What is an MDX Form and How Does it Work?
What is an MDX Form and How Does it Work?
An MDX Form is a report type in Slice that drives an entire Excel report from a single MDX query. Unlike traditional Active Forms — which generate one TM1 formula per cell relatively rigid in structure, and in some cases are difficult to replicate or troubleshoot — an MDX Form uses a single MDXForm Excel function as its engine. This makes reports significantly lighter, more flexible, and easier to maintain.
How MDX Forms Work
The report is controlled by the MDXForm function, which takes an MDX statement as its core parameter. By changing the MDX query — or making it dynamic with Excel formulas — you can reshape the entire report without rebuilding it.
Key capabilities enabled by this approach:
Swap which dimension or hierarchy appears on rows or columns at runtime
Expand and collapse row and column consolidations
Stack multiple dimensions on rows or columns
Define calculated members directly in the MDX query, without modifying the TM1 model
MDX Form Hidden Components (Rows 1–10)
When a MDX Form is exported to Excel, rows 1–10 are hidden by default. Unhiding them reveals the three structural components:
Component | Description |
|---|---|
MDX Query | Auto-generated by Slice from the cube view. This is the statement that drives the report. |
Format Range | Always 7 rows × 2 columns. Defines the cell formatting styles, similar to Active Forms. Can be customised directly in Excel. |
MDXForm Function | The single Excel function that renders the report (see below). |
The MDXForm Function – Arguments Reference
Argument | Description |
|---|---|
| A unique identifier for this MDX Form. Can be any value, text, or combination of both. |
| The TM1 server/instance name. |
| The MDX query that drives the report. Can reference a cell containing the query string. |
| Whether data write-back is enabled. |
| The element attribute to display, using the syntax |
| A reference to the format range. Must be exactly 7 rows × 2 columns. |
MDX Form vs Active Form – Key Differences
Feature | Active Form | MDX Form |
|---|---|---|
Formula per cell | Yes — one TM1RptRow/Col per row or column | No — one MDXForm function drives the whole report |
Hierarchy support | Yes (via TM1RptRow syntax) | Yes (natively via MDX) |
On-the-fly calculations | Not supported | Supported via MDX WITH MEMBER |
Dynamic dimension switching | Not supported | Supported by making the MDX query dynamic |
Expand / collapse | Rows only | Rows and columns |
Maintenance | Multiple formulas to update | Single MDX query to update |