How to create custom page numbering options in ReportWORQ

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)
    1. Income Statement (worksheet)
    2. Balance Sheet (worksheet)
    3. Cash Flow (worksheet)
  • Sales Reports (workbook)
    1. Northeast Sales (worksheet)
    2. Southeast Sales (worksheet)
    3. Midwest Sales (worksheet)
    4. 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.

Default ordering

 

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.

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

    Contact Help Desk

    This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

    I consent to QueBIT collecting my contact information.