In an effort to escape 'Excel-anarchy' many organizations transition away from error-prone spreadsheet-driven reporting toward governed enterprise data warehouses which are expected to provide a 'single-version-of-the-truth' for all reporting and analytics endeavours. To that end, during the implementation process, bespoke business rules and constraints are applied to the data warehouse to enforce the consistency and validity of reported data.
Although data warehouse applications (like Jet Report's Jet Data Manager) simplifies the process of applying business rules to data sets, the majority of implementations Onyx Reporting encounters do not report & monitor outlier records that fail data validation constraints. Translation: you may be truncating data and not know it. Double Translation: You may be making decisions based on incomplete data! That's not to say that supporting tools within the product ecosystem don't exist! They just frequently go under utilized.
Every Data Warehouse is built on (un)declared Business Rules
The expectation that business keys (like Customer No or G/L Account No) will uniquely identify one member of a dimension is virtually universal. Though a semi-obvious requirement, particularly when organizations are integrating data from disparate sources (multiple companies, a legacy system, web-based or unstructured data sources), conflicts between the expected versus actual data reality can arise. Additionally, there can be a disconnect between expected values (as laid out by standard operating procedures) and the actual values recorded in the ERP systems (ex. every sales event should be attributed to a Salesperson or Geographic Region).
To close the loop, and prevent your organization from making strategic decisions based on incomplete information, system implementors must add measures and controls for monitoring and correcting records that fail data validation.
Join our mailing list to be informed when we publish new articles.
Author Jae Wilson, lead data strategist at Onyx Reporting, partners with co-author Joel Conarton, director at executive and management consultancy Catalystis, to provide strategic solutions for data-driven organizations.
Are you ready to partner with us?
In this webinar we use Jet Essentials' Scheduler combined with Excel's named ranges to create a Parameterized Report.
We then use the same input file to create two scheduled tasks which output a semi-monthly 8 month rolling report and a weekly 4 week rolling report.
NOTE: In order to properly schedule reports to run while the user is offline, there are settings and permissions which must be applied to the User Account used to schedule the task. If you need support on this aspect of setting up the scheduler, please reach out to me at Jae@OnyxReporting.com, I'd be happy to work with you on that!
I was corresponding with a former colleague at Jet Reports US, Steve Little, and he shared this fantastic report. The general gist of the report is to Print Mailing labels on Avery 5160 paper for Christmas cards!
If you ask any Jet Reports consultant, they'll tell you, writing the report is never the problem. Formatting it for printing especially multipage printing is a pain!
Steve uses a classic technique described in summary below:
Count the number of qualifying records do display and identify how many records to fit per page.
Identify how many Pages to print, then replicate the report with NL("sheets")
On each page, use 'Excel Number Magic' to calculate which record to start on.
Then use a series of NL("nth") functions to show the 1st, 2nd, 5th, nth Record.
It's really quite clever!
If you're interested in honing your skills for the Jet Certification exams take a look at the use of:
NL("Filter") to create an Array of customers
note the use of the Filters= keyword to quickly select an array of Filter criteria
NP("Integers") to replicate numbers in the NL("Sheets") function.
note the use of named cell references
Steve was so excited about the report, he encouraged me to share it with my customers and fellow bloggers, so feel free to distribute it!
But, be advised, the report would be suitable for printing on one specific type of paper and label size.
I personally am too lazy to make a version for A4 and A5, so I thought, why not find a dynamic way where the user can identify how many rows they want in their report (as opposed to a fixed 30 records per page).
At the end of it all, it's really just the same report with some fancy Excel math and expanded replication regions which we learned from grouping reports -- NL("rows=6").
DISCLAIMER: I am not the originator of this report. Steve Little and his team at Jet Reports US originally developed this report. I'm blogging about it to provide a learning opportunity for up and coming consultants and report writers.
If you have any questions about the techniques used, please don't be a stranger. Email me at Jae@OnyxReporting.com