Drive a rowset with a dynamic MDX statement

The introduction of Active Forms with TM1 v9.5 was a big step forward in the TM1 world. It allowed for the first time TM1 users to build reports with dynamic rows without having to write a single lines of VBA.

With Active Forms, you can now show different data-set in the same report depending on a user selection. The row-set in an Active Form can be driven either by a subset or a MDX statement. The MDX statement gives you more options such as filtering rows based on an attribute or showing only the descendants of a specific parent.

Active forms are great to build advanced TM1 applications but you can easily reach the limitations of Excel. That is why Cubewise decided to build Canvas. Canvas adds to the same cell-based logic as TM1, the modern web technology which allows you to build a sophisticated web planning and reporting TM1 application. 

To build a dynamic data table with Canvas and TM1, you’ll need to follow the same steps:

1. Define the user selection (SUBNM / tm1-ui-subnm)
2. Define the row set (MDX / tm1-ui-element-list)
3. Define the table (TM1RPTROW / ng-repeat)

In this article, I’m going to show you how easy it is when you’re familiar with TM1 to build in Canvas a table with dynamic rows driven by a MDX statement.

The objective is to build a report which will show different account hierarchies depending on a user selection. 

For example, as you can see below if users pick Balance Sheet it will show the Balance Sheet hierarchy, if Net income is selected, it will show the Net income accounts:

In TM1, you firstly need to use a SUBNM to create the drop-down list for the accounts, the list of accounts comes from the subset “All Accounts” of the Account dimension, the default element is Balance Sheet and the alias Description:

• SUBNM(“Canvas_Sample:Account“,”All Accounts“,”Balance Sheet“,”Description“)

Let’s name the cell where the SUBNM is pAccount.

Then we need to define the MDX statement which queries all descendants of the Account (pAccount) selected, to do this we use the TM1DRILLDOWNMEMBER function:

• “{TM1DRILLDOWNMEMBER( {[Account].[“&pAccount&”]}, ALL, RECURSIVE )}”

The last step is to add the MDX we just defined to the TM1RPTROW function:

• TM1RPTROW($C$9,”Canvas_Sample:Account”,,,”Description”,,pMDX).

The TM1RPTROW function will loop through the list of elements and duplicate the first row of the table for each accounts in the list.

That’s it, just rebuild the Active Form and you can now update the Account selection and see different data sets in the same report:

Similar as we did in TM1, we need to define the drop-down list. Instead of using the Excel function SUBNM, in Canvas the equivalent of the SUBNM function is the tm1-ui-subnm directive:

<tm1-ui-subnm    tm1-instance="dev"    tm1-dimension="Account"    tm1-subset="All Accounts"    tm1-default-element="Balance Sheet"   ng-model="page.account"></tm1-ui-subnm>

tm1-ui-subnm will to create a list of accounts based on the subset “All Accounts” and the value selected will be stored in the variable page.account.

The second step is to create the list of elements which will drive the row-set. Instead of using the TM1RPTROW function, we’re going to use the tm1-ui-element-list directive:

<tm1-ui-element-list   tm1-instance="dev"   tm1-dimension="Account"   tm1-mdx="{TM1DRILLDOWNMEMBER( {[Account].[{{page.account}}]}, ALL, RECURSIVE )}"  tm1-attributes="Description"   ng-model="page.accounts"></tm1-ui-element-list>

The tm1-ui-element-list will store the list of accounts retrieved by the MDX statement:

• {TM1DRILLDOWNMEMBER( {[Account].[{{page.account}}]}, ALL, RECURSIVE )}”

inside the variable page.accounts (ng-model=”page.accounts”).

To duplicate the first row, we use the angular directive ng-repeat, which will create one row (tr) for each element in the array page.accounts:

<tr ng-repeat="account in page.accounts"></tr>

Similar as we did with TM1, in 3 steps we were able to replicate the same behaviour as the TM1 Active forms in Canvas: