Excel Functions

TM1 Function – TM1RPTROW

Available In

Excel

Definition

TM1RPTROW returns the row set in a Dynamic Report. Use MDX, a set/subset or hardcode a list of elements in Excel to return members to the row. Change how the row appears to users by setting the element’s indentation, whether to expand consolidations above or below, or allow drilling on consolidations. Used for Dynamic Reports (Active Form).

Syntax

TM1RPTROW(TM1RptView, TM1ServerName:Dimension, SetName, ElementList, AliasOverride, ExpandAbove, MDXOverride, IndentRate, IndentCharacter, ConsolidationDrilling)

TM1RptView = reference to the TM1RptView function from a Dynamic Report
TM1ServerName = Name of the TM1 server
Dimension = Name of the Dimension
SetName = Set name (OPTIONAL)
ElementList = An array of elements. For example {“4”;”40”;”400”} (OPTIONAL)
AliasOverride = Name of an alias (OPTIONAL)
ExpandAbove = A Boolean field. 1 for consolidations to expand above or 0 to expand below. As a default, it is set to 0 (OPTIONAL)
MDXOverride = An MDX expression (OPTIONAL)
IndentRate = Enter a number to indicate the indentation applied to each level. A value of 0 applies no indentation. As a default, the indentation of 1 is applied (OPTIONAL)
ConsolidationDrilling = turn on or off drilling down or up consolidations. A value of 0 prevents drilling, while 1 allows drilling. As a default, drill is applied (OPTIONAL)

Example

In a Dynamic Report, change TM1RPTROW to:

1st Demo – use a set called Components from the Product Type dimension
2nd Demo – use a hardcoded list in Excel
3rd Demo – use an MDX to obtain all children from All Products consolidation

Here is a Dynamic Report from the Product Scorecard cube.

The TM1RPTROW is used on the first element on the row set.

1st Demo – use a set called Components from the Product Type dimension

In the Product Type dimension, I’ll change the row to the Components set.

fx is used to edit the TM1RPTROW on cell B21.

SSubsetName = Components

Make sure AElementList and SMDXOverride is empty.

The Components set now used in the Dynamic Report.

2nd Demo – use a hardcoded list in Excel

Here is a static list of elements.

On the TM1RPTROW, refer to the list against the AElementList field.

The list of elements are now used in the Dynamic Report.

3rd Demo – use an MDX to obtain all children from All Products consolidation

Here is the consolidation.

In an empty cell, write out the MDX [Product Type].[All Products].children.

Refer SMDXOverride to the MDX.

The Dynamic Report is now using the MDX.

Video Demo