Configuring Parameters

Parameters

After reports are added to a job, we can use parameters to change the data filters that are used in a report and drive the dynamic loading of data (e.g. changing a period or business unit). Parameters are configured in the Reports step and added to one or more worksheets. Each parameter is assigned a name and optionally a cell location to receive the parameter value. While parameter names are unique within a worksheet, they may be reused across worksheets, in which case the parameter value will flow to all of the worksheets using that parameter name. Adding a parameter to a worksheet in the Reports step will make it available for configuration in the Parameters step and for bursting specific contacts. Each parameter will require a name and optionally the named range or cell location that should be adjusted. Parameter values will be selected in the Parameters step and/or the Bursting step if applicable.

Creating Parameters

To create a parameter, navigate to the Reports section of a job and select any report worksheet. Worksheet options will then be displayed. Parameters can be added by clicking the '+' button to the right of the Parameters section title.

Parameter names should be unique within the worksheet. If a parameter by the same name is used in multiple worksheets, then the value for that parameter will flow to all worksheets which use it. Parameter names added to other worksheets can be accessed using the dropdown in the name field. Parameters can be linked to a cell reference or named range using the fields to the right of the parameter name. Named ranges are pre-populated in the drop-down whereas cell references can be manually typed into the field. Parameters can be reordered and deleted using the controls to the right of the cell reference field. 

Cell References

A common use case for a ReportWORQ parameter is to adjust a cell value on a worksheet which will in turn impact downstream formulas. For example, changing the name of a cost center may adjust data loaded by a provider formula or Excel VLOOKUP formula. Another example would be when using the RWSQL formula, which is used to load data into a worksheet based on a SQL Query, the cell being adjusted by the parameter may manipulate the SQL query passed to this formula. A cell reference should be provided if the parameter value is required to adjust the data for the worksheet (e.g. a provider-specific formula or a VLOOKUP). A cell reference is not required for data providers with hidden report specifications, such as IBM Planning Analytics Quick Reports, Oracle SmartView POV reports, Anaplan reports, or Workday Adaptive OfficeConnect reports. 

Dynamic Worksheets

When the value for the parameter is chosen, it may be a single item or a list of items. If the value is a list of items and the option to create multiple copies of the worksheet is selected, then this worksheet will be copied for each item in the list. Each worksheet will receive its respective value in the configured cell location. If multiple parameters have lists for values then a worksheet will be created for all combinations of the parameter lists. In this scenario, the parameters can be ordered to in turn control the order of the sheet creation. For example, if a worksheet were to be created for 3 business units and 4 departments, then 12 unique worksheets will be created. Each worksheet will receive its respective Business Unit and Department values in the respective configured cell locations. The order of these sheets may be Business Unit by Department or Department by Business Unit. When creating worksheets using this technique it's often desired to use the Sheet Name and optionally PDF Parent Bookmark Name features to dynamically name the worksheets. The Sheet Suppression feature can also be utilized to remove any created worksheets that do not have any data or don't meet the criteria for remaining in the output file.

Parameter Sets

Creating Distinct Sets of Worksheets

The previous topic covered an example where a worksheet could be created for every combination of 2 lists. However, there are some cases where a distinct and finite set of combinations should be produced instead of every possible combination. To support this scenario ReportWORQ allows for a spread operation whereby a single parameter can provide a list of values to be spread across multiple cells. For example, the Business Unit is located in cell C5 and Department is located in cell C6. Our use case requires us to create 4 copies of a worksheet for different combinations of Business Unit and Department, where a cross-join (every possible combination) would result in more worksheets than required. The desired combinations can be achieved by creating a single parameter and entering a pipe-delimited list of cell locations, such as C5|C6. Then in the Parameters or Bursting step choose a List type value for this parameter. Each line of text in the list value will represent another worksheet to be created and each entry will contain a pipe-delimited list of values to be spread to the respective cells.

 Example configuration for a Parameter Set including Business Unit in cell C5 and Department in cell C6

An example value for the above parameter set will produce 4 worksheets with the specified combinations of Business Unit and Department.

*Note that the Parameter Sets are currently only supported for cell-based reports and not specification-based reports.

Parameter Values

The Parameters step will display the distinct set of parameter names used across all worksheets in the job. In this step, we will decide if and how a value should be provided for the parameter, if the parameter should be used in bursting, and the behavior of the parameter if it has multiple values. A parameter value will be sourced by one of the available parameter types dependent on which data sources have been configured.

Parameter Types

The following Parameter Types are supported:

None

No parameter value is supplied. This option may be chosen if the intention is to override the parameter in the Bursting step or through a schedule or API call.

Excel Range

Parameter values are retrieved from a specified range of cells in an Excel file. You specify the file, worksheet, and cell range. The Excel file must be stored within a Report Provider (Network Folder, Google Drive, or ReportWORQ Drive).

Text List

The Text List parameter type is a static new line delimited list of values. This is the only parameter option that supports the Parameter Sets feature discussed earlier in this article.

IBM Planning Analytics Subset, MDX Query, or Dimension

These parameter types require a configured and enabled IBM Planning Analytics On-Premise or IBM Cloud connection. These parameter types allow the user to dynamically source the list of values from an IBM Planning Analytics subset, dynamic subset, or by executing an MDX query.

IBM Planning Analytics MDX Repeater

This parameter type allows you to execute an MDX query for each item of another parameter list. This supports a highly dynamic use case where the number of reports is calculated by a query and then the number of pages within each report is also calculated. For example, assume the user wanted to create a report for every Business Unit, and each report should have a page for each department in the business unit. To achieve this an MDX query could be crafted to list the divisions for any given business unit, where the term %ELEMENT% will be used in the MDX query in place of the business unit. Then this parameter references another parameter that will provide the list of Business Units to cycle through.

Workday Adaptive Dimension

This parameter type requires a configured and enabled Workday Adaptive Planning connection. The user may select a dimension and list of elements, or source the list of elements dynamically using an attribute.

Oracle SmartView Dimension

This parameter type requires a configured and enabled Oracle connection. The user may select a dimension and a list of elements.

Anaplan Dimension

This parameter type requires a configured and enabled Anaplan connection. The user may select a dimension and a list of elements.

Pivot Table

This parameter’s values are retrieved from a specified Pivot Table in an Excel file. Users can set a parameter location to update a pivot table filter by using the syntax PivotTableName.FieldName.

Parameter Options

For each parameter, the user must select the desired behavior if the parameter results in a list of values, and optionally enable this parameter to be used in burst sets.

One report per item 

The One report per item option will create a separate output file for each parameter value provided. When using this option it's often desired to also use this parameter value as a variable in the name of the file that is created. 

One page per item

The One page per item option will make a copy of the Microsoft Excel Worksheet for each parameter value provided. When using this option it's often desired to also use this parameter value as a variable in the sheet name option. Additionally, the option to Group & collate pages that use this parameter can be enabled. When a single worksheet uses a parameter that contains a list of values, a worksheet will be created for each item in the list. When multiple worksheets use the same parameter then each of those worksheets will be replicated for each element. In this situation, it's often desired to keep each worksheet with the parameter, grouped by the parameter. For example, assume we have an Income Statement and a Balance Sheet to be replicated for regions: US, UK & Canada. With the group & collate option disabled the output would result in an Income Statement for US, UK & Canada, followed by a Balance Sheet for US, UK & Canada. The preferred order with the group & collate option enabled would result in an Income Statement and Balance Sheet for the US, then a pair for the UK, and finally a pair for Canada.

Enable this parameter to be used in burst sets

Enabling this option on at least one parameter will enable the burst step to become available for the job. See the next section for an explanation of burst sets.