Query parameterization in Universal Reports

A Universal Report is a hierarchy aware and parameterized report type that achieves its parameterization by using named ranges.

When you create a Universal Report from an existing view or an Exploration View, Planning Analytics for Microsoft Excel, IBM® Planning Analytics Workspace websheets, and IBM Planning Analytics TM1® Web automatically hide the query parameterization cells that drive the report.

To see this hidden area, highlight the rows and columns of the sheet. Then, right-click and select Unhide. Now, you can see the cells that are included in the report properties and named ranges.
Note: In IBM Planning Analytics Workspace, and IBM Planning Analytics TM1 Web 2.0.96 or later, you can use the Show Sheet format area Show Sheet format area icon or Hide sheet format area Hide sheet format area icon buttons on the toolbar to reveal and hide the format range.
Query parameterization displays after unhiding area in a Universal Report


The query parameterization area includes properties that determine the look and feel of the report and the Query cell which contains the MDX expression.
Properties

The following properties are located in the first six cells of the query parameterization area and are scoped to the p named range:

  1. Datasource - This is the URL of the first data source that you log into and is calculated by the TM1PRIMARYDATASOURCE() function. You can change the URL manually if the view you are using is from a different datasource.
  2. Server - This is the name of the first database that you are authenticated through and is calculated by the TM1PRIMARYDBNAME() function. You can change the database manually if the view you are using is from a different database.
  3. Display Toggle - When Display Toggle property is set to TRUE, toggles on rows and columns display, making it easy to identify and expand or collapse consolidated cells. To remove toggles from rows and columns, set this property to FALSE and rebuild the sheet. You cannot expand or collapse consolidated cells if Display Toggle is set to FALSE.
  4. Indents Per Level - This property refers to the number of spaces between the cell margin and member name in consolidated cells. The Indents Per Level property accepts integer values.
  5. Active Display - The Active Display property displays the alias attribute of a hierarchy. The Active Display property is defined by string that includes each hierarchy in the query and its display alias attribute. You can modify the alias for any hierarchy in this string and rebuild the sheet to change the output of the report. This property only affects the row and column aliases of your report, and cannot affect slicer aliases.
  6. Expand Aboves - The Expand Aboves property determines whether a toggle expands upwards or downwards. This property is defined by a string that includes each hierarchy in the query and how each one expands. If Expand Aboves is set to TRUE, the hierarchy in the row or column expands upwards.
Query
The Query cell is a valid MDX expression and is scoped to the named range q. The Query cell contains an accessory function called MakeQuery() which builds an MDX query from its arguments that includes the Row Axis Sets, Column Axis Sets, Slicers, and Calculations.
When the MDX query string exceeds the maximum cell character count, Planning Analytics for Microsoft Excel replaces the MDX query string with TM1BigString-id. For example, TM1BigString:1234567890. TM1BigString-id maps to the Planning Analytics for Microsoft Excel backend, and is replaced with the full MDX query string when run against TM1. You can manipulate the query on the sheet by making changes to the row, column, and slicer axis expressions via the hidden parameterization area.

To explore how the parameterization works in a Universal Report, use the Trace Precedents feature in Excel (Formulas > Trace Precedents). This feature adds a blue line from the Query cell to all its arguments and their dependencies.

Tracing precedents to see parameterization in a Universal Report


If you continue to click Trace Precedents a few times, you can see the full parameterization graph of the query and how the underlying TM1SET formulas drive the expressions of each hierarchy in the report.

Each hierarchy in the RowAxisSets and ColumnAxisSets has its own TM1SET formula that uses the SessionSet output mode. These SessionSet IDs are then used to create a Set Expression. The Set Expression uses the TM1SubsetToSet([dim].[hier], <sessionset_id>) notation, which is then concatenated by using the MakeAxis() accessory function.

This parameterization of the MDX query allows for hierarchy-level control of the report, and can be enhanced with further sophistication so that the TM1SET formulas of one Universal Report can drive the row, column, or slicer expressions of other Universal Reports in the workbook.

When done correctly, this technique of sharing TM1SET outputs among multiple Universal Reports can create a synchronization effect and reduce the number of TM1SETs needed to drive the Universal Report content in your workbook.