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 targetRange for 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.

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. Reportworq recognizes and evaluates them against the workbook's TM1 connection during report generation.

Function	What it does	When to use
`DBR`, `DBRW`	Retrieve a single cube cell value for a given element tuple. `DBRW` is the "register" variant used in most live grids.	The everyday way to pull one TM1 number into a cell.
`DBRA`	Retrieve an element attribute value.	To show an element's attribute (for example a description) beside its data.
`DBS`, `DBSW`, `DBSS`	Send (write) a value back to a cube cell. `DBSS` sends a string.	Write-back grids that push values into TM1.
`SUBNM`	Return an element name from a subset by index, and drive a picklist.	Parameter and picker cells that select the element a grid slices by.
`VIEW`	Reference a named cube view for retrieval.	To bind a block of `DBRW` formulas to a defined view.
`TM1RPT*`	The Active Form family that expands a dynamic report region.	Active Form reports that grow and shrink with the underlying subset.
`DIMIX`	Return the index of an element within a dimension.	Lookups and validation against dimension order.
`DIMSIZ`	Return the number of elements in a dimension.	Sizing loops and dynamic ranges.
`DNEXT`	Return the next element in a dimension.	Walking a dimension in order.
`DNLEV`	Return the number of hierarchy levels in a dimension.	Level-aware layout logic.
`DTYPE`	Return an element's type (numeric, string, consolidated).	Branch formatting or logic on element type.
`ELCOMP`, `ELCOMPN`	Return a component (child) of a consolidated element, by name or by index.	Walk a consolidation's children.
`ELISCOMP`	Test whether one element is a component of another.	Membership checks in a hierarchy.
`ELISPAR`	Test whether one element is a parent of another.	Parentage checks in a hierarchy.
`ELLEV`	Return the level of an element.	Level-based indentation and formatting.
`ELPAR`, `ELPARN`	Return a parent of an element, by name or by index.	Roll-up navigation.
`ELSLEN`	Return the number of elements in a subset.	Sizing a subset-driven range.
`ELWEIGHT`	Return the weight of a component in its consolidation.	Weighted roll-up calculations.

Universal Report functions#

These belong to the same native library and are supported in full (dynamic) Universal Reports. They are not supported in static Universal Reports.

Function	What it does	When to use
`TM1VAL`	A hierarchy-aware retrieval, the complement to `DBR`.	Universal Reports that need hierarchy-aware cell values.
`DefineCalc`, `DEFINECALC`	Declare a user-defined calculation.	Custom calculated members inside a Universal Report.

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 flip SUBNM/DIMNM public-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.

Functions overview: which one do I use
Relational database functions for the SQL equivalents (RWSQL and 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 page

Or write to support@reportworq.com directly.