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.
![Show Sheet format area icon](images/Show_hidden_range.png)
![Hide sheet format area icon](images/Hide_hidden_range.png)
![Query parameterization displays after unhiding area in a Universal Report](images/pax_unhide_parameterization_universal_report.png)
- Properties
-
The following properties are located in the first six cells of the query parameterization area and are scoped to the p named range:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
To explore how the parameterization works in a Universal Report, use the Trace
Precedents feature in Excel
( ).
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](images/pax_universal_report_trace_precedents.png)
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.