IBM Planning Analytics functions#
Reportworq works with IBM Planning Analytics (TM1) two ways in a source workbook: the RWMDX importer, which runs an MDX query and writes the result into a range, and the native TM1 worksheet functions (DBRW, DBS, SUBNM, DIMIX, VIEW, TM1RPT*, and the element/dimension helpers) that Planning Analytics authors already know from Perspectives and Planning Analytics for Excel. Reportworq ships and evaluates this native library, so an existing TM1-authored workbook keeps working as a Reportworq report.
Before you use these functions#
- A configured IBM Planning Analytics / TM1 connection must exist for the workbook. An admin sets this up in the integrations hub; see Connect IBM Planning Analytics.
- The functions are evaluated by the Reportworq engine at report-generation time; they do not compute in desktop Excel unless the corresponding IBM add-in is installed there.
RW.PlanningAnalytics.MDX#
Runs a TM1 MDX query and writes the result set into an Excel range. Alias: RWMDX.
Signature
=RW.PlanningAnalytics.MDX(targetRange, connectionName, query)
| # | Argument | Type | Meaning |
|---|---|---|---|
| 1 | targetRange |
Range | Top-left output range for the MDX result. |
| 2 | connectionName |
String | The TM1 connection to use. |
| 3 | query |
String | The MDX query text. |
All three are required.
Behavior
- Reportworq calls the configured TM1 REST provider and requests a cellset.
- Two or more axes writes a crosstab with row and column labels. One axis writes a list-style output.
- On success the formula cell is set to the query text that produced the data, so the delivered workbook shows exactly what was run.
- Reportworq attempts to destroy the TM1 cellset after writing the output.
- Leave enough space below and to the right of
targetRangefor the headers and the data values. - Formula parsing succeeds before the MDX is ever sent, so a bad query surfaces at run time rather than at author time. Validate your MDX in a query tool first.
Planning Analytics v12 enforces stricter MDX. The v12 engine (the containerized PA-Engine, including PA-as-a-Service) rejects loosely-qualified MDX that older v11 servers accepted - it wants hierarchy-qualified member references. Any Reportworq v6 release sends v12-compatible MDX; on the v5 line you must be on 5.0.0.92 or greater. If a query works against a v11 server but fails against v12, fully qualify each member with its hierarchy. See Planning Analytics v11 vs v12.
Examples
=RW.PlanningAnalytics.MDX(A1, "TM1Server1", "SELECT NON EMPTY [Time].Children ON ROWS, {[Measures].[Revenue]} ON COLUMNS FROM [Sales]")
=RW.PlanningAnalytics.MDX(B5, "FinanceTM1", "{[Period].[2026]} ON COLUMNS FROM [Plan]")
When an argument is wrong
| Situation | Result |
|---|---|
Invalid targetRange |
Formula error before the query runs. |
| Empty query text | Formula error. |
| The MDX returns an unexpected cell count for its axes | Report generation fails for that formula. |
Available since around v5.0.0.80. Version availability is approximate.
Splitting a long MDX query across cells#
An MDX (or SQL) query that is too long for one Excel cell can exceed Excel's roughly 8,192-character formula limit. Break it across several cells and concatenate them, for example =$A$1&$A$2&$A$3. Two traps to avoid:
- Every fragment cell must start with
=. A fragment cell that does not begin with=embeds its leading quote character literally into the query string, and the query then fails silently. Make each fragment its own formula. - Escape quotes around attribute values in MDX, for example
&""""&$A$1&""""&. Validate the concatenated output in a query tool before you deploy the workbook.
You can use IF(...) across the fragments to switch between queries.
Native TM1 worksheet functions#
These are the classic TM1 / Planning Analytics functions that Planning Analytics authors already know from Perspectives and Planning Analytics for Excel. Reportworq recognizes and evaluates them against the workbook's TM1 connection during report generation. Each function has its own reference page; use the index below.
Cube data#
| Function | What it does |
|---|---|
DBRW (DBR, DBRW) |
Retrieve a single cube cell value for an element tuple. |
| DBRA | Retrieve an element attribute value. |
DBS (DBS, DBSW, DBSS) |
Send (write) a value back to a cube cell. |
| VIEW | Reference a named cube view for retrieval. |
Element and consolidation#
| Function | What it does |
|---|---|
| ELPAR | A parent of an element, by index. |
| ELPARN | The number of parents of an element. |
| ELLEV | The level of an element. |
| ELWEIGHT | The weight of a child in its consolidation. |
| ELCOMP | A child (component) of a consolidation, by index. |
| ELCOMPN | The number of children of a consolidation. |
| ELISCOMP | Test whether one element is a component of another. |
| ELISPAR | Test whether one element is a parent of another. |
| ELSLEN | The length of an element's name. |
Dimension and subset#
| Function | What it does |
|---|---|
| DIMIX | The index of an element within a dimension. |
| DIMSIZ | The number of elements in a dimension. |
| DNEXT | The next element in a dimension. |
| DNLEV | The number of hierarchy levels in a dimension. |
| DTYPE | An element's or attribute's type. |
| DFRST | The first element in a dimension. |
| TABDIM | The dimension name at a cube position. |
| SUBSIZ | The number of elements in a subset. |
SUBNM (SUBNM, DIMNM) |
An element name from a subset, by index or name; drives a picklist. |
Active Form and Universal Report#
These are normally generated by Planning Analytics for Excel or the Reportworq Universal Report rather than typed by hand.
| Function | What it does |
|---|---|
| TM1RPTVIEW | Anchors an Active Form region. |
| TM1RPTROW | The dynamic row axis of an Active Form. |
| TM1RPTTITLE | A title member reference. |
| TM1RPTFILTER | The sort and filter for a region. |
| TM1RPTELISCONSOLIDATED | Whether a row is a consolidation. |
| TM1RPTELLEV | The display level of a row. |
| TM1RPTELISEXPANDED | Whether a row is expanded (always FALSE). |
| TM2RPTELISCONSOLIDATED | Whether a Universal Report axis member is a consolidation. |
| TM2RPTELLEV | The display level of a Universal Report axis member. |
| TM2RPTELISEXPANDED | Whether a Universal Report axis member is expanded (always FALSE). |
Spill, calculation, and session#
| Function | What it does |
|---|---|
| TM1ELLIST | Spill the element names of a subset or MDX set down a column, indented by level. |
| TM1VAL | A hierarchy-aware retrieval, the complement to DBRW. |
| DefineCalc | Declare a user-defined calculated member. |
| TM1USER | The current user's display name on a server. |
| TM1SET | A hierarchy-aware member selector with extra output modes. |
| TM1PRIMARYDATASOURCE | The workbook's last-saved data source name. |
| TM1PRIMARYDBNAME | The workbook's last-saved server name. |
TM1ELLIST#
The full TM1ELLIST reference has moved to its own page: TM1ELLIST function.
How Reportworq handles errors#
Every native function reports a bad argument in one of three ways, and the per-function pages use these terms:
- Hard error - the formula fails and the cell shows
#VALUE!(or#NAME?for a function that is recognized but not supported). Argument-count mistakes, Excel error values passed as arguments, and a server that is not connected are hard errors. - Warning - a message is written to the run report and the cell falls back to its default value (blank,
0, orFALSE, depending on the function). A not-found dimension, element, or subset is usually a warning. - Silent drop - the bad input is filtered, clamped, or ignored with no message. For example, TM1ELLIST drops invalid element names from an element list without warning.
Calc Mode changes the outcome for the cube-data and picker functions. The connection's Calc Mode setting decides whether an invalid or missing dimension, element, or server in DBRW, DBS, SUBNM, or DIMNM is a hard error or a warning. With Calc Mode on, these are hard errors (#VALUE!); with Calc Mode off, the same input is a warning and the cell returns blank. This is why the same workbook can show #VALUE! on one connection and blanks on another. Calc Mode is a connection setting; see Connect IBM Planning Analytics.
Planning Analytics v11 vs v12#
The v12 engine (the containerized PA-Engine, including Planning Analytics as a Service) enforces stricter, hierarchy-qualified MDX than older v11 servers, and a hierarchy's default display levels can differ between the two versions. This affects generated MDX (see the note under RW.PlanningAnalytics.MDX above), the member references TM1RPTTITLE emits, and the level-based indenting TM1ELLIST produces. For the connection-level detail, see Planning Analytics v11 vs v12.
Universal Report functions#
TM1VAL and DefineCalc belong to the same native library and, as documented here previously, are supported in full (dynamic) Universal Reports and not in static Universal Reports. That scope is carried from the earlier documentation and still needs confirmation against the current build, so treat it as a documented limitation rather than a guarantee.
Notes and limits#
- The native functions are the standard TM1 syntax; refer to your IBM Planning Analytics documentation for exact argument lists.
- If a cell shows
#VALUE!from a Planning Analytics for Excel workbook, the connection's Calc Mode can render invalid elements that way and can flipSUBNM/DIMNMpublic-versus-private subset resolution. See Connect IBM Planning Analytics. - Disable other datasources pointed at the same TM1/PA database before certain operations, concurrent connections to one database can conflict. This is a connection-level concern, covered on the connector page.
Related pages#
- Functions overview: which one do I use
- Relational database functions for the SQL equivalents (
RWSQLand the split-long-query rule). - General report and layout functions for shaping the imported grid.
- Connect IBM Planning Analytics for the connection and authentication setup.
Feedback on this page
Comments, questions, requests, or something missing or unclear? Email us - the page you are on is filled in for you.
Email feedback on this pageOr write to support@reportworq.com directly.