Dynamic Reports with MDX

Dynamic Reports in Planning Analytics for Excel (PAfE) takes “complex reporting” to a whole new level! This report is fully embedded within Excel and utilizes TM1 formulas. One of the greatest strengths of this report type is that it allows for expandable / collapsible rows, dictated by the TM1RPTROW formula.

There are various levers to pull in customizing the TM1RPTROW formula: today we will focus on utilizing the MDX Override option and walk through an example report.

This revenue report expands on the Product dimension and is using the Level0 subset.

TIP: Click on the fx button to review the arguments required for the TM1RPTROW function

The <Subset Name> argument must reference an existing subset in the dimension; it can be either a static or an MDX driven subset.  The <MDX Override> argument will take precedence over the <Subset Name> argument.
 

When should you use the <Subset Name> vs the <MDX Override?

If the list of elements displayed on the rows does not need to change based on any of the context selectors, the <Subset Name> is sufficient to drive the row expansion.

If the rows displayed need to update in relation to any other data points in the report, an <MDX Override> is required.

Let’s return to our example report:

Each product has a Primary Channel; Channel is a context selector on the report. The objective is to only display products when its Primary Channel is selected.

Step 1.  Write an MDX expression to filter on the Product’s Primary Channel

{ FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Primary Channel] = “Retail”)}

Step 2.  Place the MDX in the PAfE report and update the reference to point to the Channel Selector.

Step 3. Update the TM1RPTROW so that the <MDX Override> argument is pointing to the MDX formula in the PAfE report

Results:  The product list is dynamically updated based on the selected Channel.

Ex. When the Channel “Internet” is selected, only products whose Primary Channel is “Internet” is displayed in the rows.

How will you use MDX in your reports?  We would love to hear from you!