Bursting Reports

Bursting

This step provides the ability to specify a finite list of output reports to be created where a parameter value is supplied for each burst set. When bursting is enabled for a parameter, it is not necessary to provide a parameter value in the Parameters step unless a default value is desired. For example, assume we have three parameters: Period which will be the same for all reports, Currency which will be USD for all reports aside from a few exceptions, and Business Unit which will be different for each report. In this example, Period will not be enabled for bursting because it is the same for all reports. Currency will be enabled for bursting with a default value of USD, it will then be overridden as needed. Finally, the Business Unit will be enabled for bursting and will not have a default value (parameter type none) since each burst set will include its parameter value. 

The options for One report per item or One page per item also apply to burst sets. For example, if each burst set also provides a list of Departments, the user may choose for that burst set to produce a single report with a page per department or a report per department with a single page each.

Static Burst Sets

The Bursting step will display a column for each parameter that was enabled for bursting. If the parameter had a value supplied on the Parameters step then that value will appear in this step as a default value indicated by the prefix default: and in italics font style. To override the value, select the hyperlink and provide one. Each value provided on the burst step will appear in Bold font style. Each row of the burst sets table represents a burst set or typically a specific report to be generated using the selected parameter values. Optionally a contact from the address book can be associated with each burst set. By associating a contact, the fields and metadata for this contact specified in the address book become available in the distribution step. For example, the contact may contain the Email Address and Slack Channel to be used when distributing this report.

Dynamic Bursts Sets

Bursts can alternatively be created dynamically using an Excel or SQL source.

In the following example, 2 parameters added to a worksheet appear in the Parameter view (Business Unit, and Departments).  A third parameter (EmailAddress) only to be used for distribution purposes, has been added to the Parameter configuration page. All three have been edited to "Enable this parameter to be used in burst sets", but the parameter type default has been left as the default (None).

On the bursting screen, the mode is set to an Excel or SQL source.

Excel Burst Sets

After selecting the Excel Source mode and selecting the Excel source file, the user can then select the Refresh Preview button to display the content imported from the source file. The Excel file format should have a header in row 1 to specify the column labels which will be used for mapping the Excel file to the ReportWORQ parameters. Each column name must match the name of the Parameter in ReportWORQ. Rows 2 through n will be imported as a Burst Set into ReportWORQ mapping the cell values to the appropriate Burst Set Parameters. If the preview shows blank for a column of parameters, then check to make sure that the Parameter Name and the Column Name in the Excel file match. 

Distribution information, such as Email Address can be supplied as a parameter and then used in the distribution screen by choosing the Variable Parameter. However, if you’re already utilizing the Address Book feature in ReportWORQ and prefer to associate a Contact from the address book with the burst set row then this can be done by creating a column named “Contact” in the Excel file and providing the name of the contact in the Excel file. When this reserved column exists, ReportWORQ will look up the contact from the address book and associate that contact with the burst set. Using this technique, you can use the Contact Variable in the distribution screen to select contact information. If the lookup fails and the contact is not found, then the Preview or Job Execution will fail and indicate which contact could not be found. The lookup is case-insensitive but must match a name in the address book.

In cases where you wish to provide a list of values to a parameter, such as a list of cost centers or departments, this can be done by providing each value in the Excel cell separated by a delimiter. To provide maximum flexibility and avoid any data source limitations you may use any character delimiter that you prefer, and then inform ReportWORQ what character to use as the delimiter for list types. The default delimiter is Pipe |.

ReportWORQ also supports using a list of values dynamically generated by MDX queries or Planning Analytics Subsets. This can done by entering the “MDX|“MDX query” or SUBSET|”PA Subset model” entry in an Excel workbook to be imported:

MDX Query example:

PA Subset example:

After importing the burst set file, ReportWORQ evaluates these expressions when bursting to generate the list of elements that correlate to these expressions:

MDX Query:

PA Subset:

SQL Burst Sets

After selecting the SQL Source mode and entering a valid query, the user can then select the Refresh Preview button to display the query results. Similar to the Excel Burst sets technique, the column names of the SQL query will be used to map row fields to Parameters in ReportWORQ and if a field by the reserved name “Contact” is found then that will be used to lookup a contact from the Address Book. Adjusting database columns to match ReportWORQ parameter names may not be desirable and is unnecessary, if the database columns do not match the names of the parameters simply use the AS syntax in the SQL query to line up those names. For example, if the database column is named FYPERIOD and the ReportWORQ parameter is named Fiscal Year then SQL query SELECT FYPERIOD AS ‘Fiscal Year’ FROM table would be sufficient. Please review the Excel Burst Sets documentation above for information regarding Value Delimiter and specifying a Contact from the Address Book.