September 21, 2018
When running a Job, the default order of the reports within a book is determined by the order of reports in the Book Options interface, as well as the order of worksheets within each Excel source workbook. For example, we’ll look at a ReportWORQ book that’s built from the following two workbooks;
- Management Reports (workbook)
- Income Statement (worksheet)
- Balance Sheet (worksheet)
- Cash Flow (worksheet)
- Sales Reports (workbook)
- Northeast Sales (worksheet)
- Southeast Sales (worksheet)
- Midwest Sales (worksheet)
- West Sales (worksheet)
ReportWORQ book with default ordering
If we were to generate a book off these reports, our output would follow the order of the source workbooks/worksheets.
Of course, certain recipients might want their reports presented in a different order than the default one. A manager might want to see their region’s sales first, etc.
In this example, we’ll assume that we want to present these reports in the following order.
- Income Statement
- Balance Sheet
- West Sales
- Northeast Sales
- Southeast Sales
- Midwest Sales
- Cash Flow
With the ability to arbitrarily reorder reports in ReportWORQ, this reordering is simple. In order to define a custom report ordering, firstly “Enable Collation” must be enabled under Book options.
Enable Collation setting
Then, the position of each sheet in the final Job output must be set. Each sheet in a report has a corresponding setting for a “Sheet Output Order”.
Sheet Output Order setting
This setting references a cell that holds an integer value, to be used when ordering reports at Job runtime. You can define a report sequence somewhere (beginning with 1) by Recipient.
For example, if you can define a report order, by Recipient, in a Planning Analytics cube then you can reference that cube via DBRW’s in each worksheet. Any Excel formula that computes a numeric integer value (e.g., 1,2,3,4,5) or something that can be interpreted as a numeric integer value (e.g., “1”, “2”, “3”, “4”, “5”) can be used in this setting.
In the following example, within the Income Statement sheet, cell C4 evaluates to 1 for this Recipient. This sheet will then be placed first in the output.
Income Statement with Sheet Output Order in cell C4
Report ordering must be defined based on a number that is “1” or higher and less than 2,147,483,647. Negative numbers and zeros are ignored, and these numbers do not have to be consecutive in order, meaning skipping a number in a sequence is supported.
If the “Sheet Output Order” setting for a particular worksheet returns anything other than a number or something that can be interpreted as a number at Job runtime, ReportWORQ will apply default ordering for that worksheet, meaning it will come *after* any explicitly ordered reports (reports that *do* have a valid report order specified), in the order created during Job execution.
Additionally, ReportWORQ will truncate any decimal places in specified report ordering values, meaning 3.7 and 3.1 will both be interpreted as “3”, for ordering purposes.
Finally, if more than one reports has the same ordinal value applied to it, the reports will be ordered based on that number, followed by the order in which they were generated.