Great set of questions John.
Q - Are these just spacer columns/rows or is it supposed to be possible to use any Excel formulae in there as well?
User columns and rows in Universal Reports offer flexible usage options:
Spacers - Leave them empty to create visual separation
Custom Headers - Add values in the header cells only
Excel Formulas - Insert custom headers and Excel formulas for both Universal Reports and Static URs
Q - The equivalent functionality works in static URs but in non-static URs the content disappears on refresh. Is this to be expected?
In non-static Universal Reports, formulas require an additional step to persist through refreshes:
1. Define your calculation in the cell
2. Right-click on the formula cell
3. Select the option to apply the formula to the entire row or column
This ensures the formula persists after data refreshes.
You can see an example in this video
https://youtu.be/1KcEJ10eLyA?si=CIB1Ui1j9-HCKd7I
Q - I have found that if the report has 2 column dimensions, then the option to insert a column is not available. What are the constraints on the availability of these user columns? I found that I could create a spacer in the cube viewer and it would carry through to a converted UR, but then it didn't behave well.
Universal Reports currently support user rows and columns only for simple stacked columns or rows. The insert option is unavailable for reports with complex axis expressions.
As stated in the official documentation:
"User rows and columns and complex axis expressions:
Currently, you cannot add user rows and columns on axis with complex use case expressions. This option to add a user row or column is unavailable in the Excel context menu."
As per an AI question - What Constitutes a Complex TM1 Axis?
A TM1 complex axis expression includes MDX expressions on query axes (ROWS/COLUMNS) that go beyond simple member selections:
1. Set Functions
- `TOPCOUNT()`, `BOTTOMCOUNT()` - Top/bottom N members by measure
- `FILTER()` - Conditional member selection
- `DESCENDANTS()`, `ANCESTORS()` - Hierarchical navigation
2. Set Operations
- `UNION (+)` - Combine sets
- `INTERSECT (*)` - Common members
- `EXCEPT (-)` - Exclude members
3. Crossjoins
- `CROSSJOIN()` - Multiple dimensions on one axis
4. Hierarchical Functions
- `.CHILDREN`, `.PARENT`, `.MEMBERS`
- `DRILLDOWNLEVEL()`, `DRILLUPLEVEL()`
5. Nested Functions**
- Multiple function layers (e.g., `TOPCOUNT(FILTER(...))`)
Future Enhancement:
We are actively working on supporting complex axis expressions. In the next few months, we will release a new UI that will allow you to:
- Insert spacers in complex axis scenarios
- Access the set-editor for complex expressions
Stay tuned for updates!
Q - The PAfE API doesn't appear to have been updated to include Universal Report functionality.
Universal Report functionality for the PAfE API is currently planned for development. We will announce availability once this feature is released.
Q -If `RefreshBook` (or `RefreshAllData`) is used, will it refresh URs (Static & non-static)? Similarly, would `UnlinkAllData` apply to URs?
Basic book and sheet refreshes will work. The more granular refresh api's like RefreshSelection are not supported.
------------------------------
Xavier Osorio
Planning Analytics
Senior Product Manager
IBM
------------------------------