The hidden rows of a Universal Report reveal a vast amount of text and named ranges. It can be overwhelming, but if you take the time to understand the purpose of each named range, it is quite simple and familiar.
Naming Convention
The auto-created named ranges follow the naming convention of “tm2\”_”REPORT ID”_”NAMED RANGE ID”.
The “tm2\” is constant and denotes that the range relates to the Universal Report. The “REPORT ID” is the identifier for the individual Universal Reports on the same sheet (# in my examples below). I will unpack the “NAMED RANGE ID’s” below as they form the building blocks of a Universal Report.
The Properties
The Properties range contain vital information and settings about the report, specifically:
- The Datasource URL
- The TM1 server that you are referencing
- Display Toggle (expand/collapse toggle for consolidated elements on the rows and columns)
- Indents per level (cell indents)
- Active Display (here you can specify the aliases to use for your hierarchies)
- Expand Above (can be set to be hierarchy specific)
These items form the “p” range: “tm2\_#_p”.
The Query
The Query range is an MDX SELECT statement denoted by the “q” and it is a concatenation of the Rows, Columns, and Slicer ranges discussed below.
Defining the Rows and Columns sets
The Row and Column subsets are defined using a combination of TM1SubsetToSet and the TM1SET function. The Set Expression is then referenced by the Query to create the SELECT statement.
Slicers and Tracked
Slicers are like a SUBNM and represent the Context area of the report. The Slicer range “tm2\_0_slicers” is made up of one or more TM1SET functions that are used to define the context.
A TM1SET cell that is tagged as “tm2\_tracked” (no sheet reference), will open the Set Editor.
Rows and Columns
Row and Column sets are defined with the “r” and “c” respectively.
Similarly, the formatting is defined by “rx” and “cx” ranges. Formatting is defined using Excel’s Conditional Formatting functionality.
Data
Finally, the Query needs to know where to put the data! The range that needs to be specified is the “d” parameter. The range is marked as “tm2\_#_d” in order to return values.
Get in touch
Universal Reports are a powerful new way create dynamic reports. As with Active Forms and Dynamic Reports, understanding the building blocks will make them easier to maintain and customise.
We’re exploring this new feature and you’re welcome to get in touch.
