Creating basic Top 10 reports using MDX expression

One of the most common requests for reports is to generate a list of top 10 for a dimension based on TM1 data. This makes sense considering level of data TM1 works best with; this could cover top 10 consumers, customers or top 30 SKUs at one particular outlet or within a brand.

There are several ways to achieve that within TM1, the simplest way to do this within a Excel report is to write a MDX query inside an active form.

For this example, lets use a standard Payroll model to rank staff based on their salary.

First step – generate the view in TM1 Perspectives.

TM1 view for Excel Reporting

TM1 view for Excel Reporting

Then export it to Excel as an Active Form:

Basic Excel Report

Basic Excel Report

 

Now, let the fun begin! The next step is to write a MDX statement inside this view which might not be an easy task for those who have never interacted with MDX.

For this particular view open formula for B18 cell (the cell that holds TM1RPTROW function that generates rows for the active form).

Open formula dialog and scroll down a fraction, there is a MDX parameter:

MDX parameter for TM1RPTROW

 

Let`s start with simple all level 0 elements MDX subset that selects all elements of one dimension and then filters it to the lowest level only.

{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)}

There are two MDX functions in the expression:

  1. {TM1SUBSETALL([Dimension])}
  2. {TM1FILTERBYLEVEL(Subset, Level)}
TM1 Excel Report with Level 0 elements (MDX subset)

TM1 Excel Report with Level 0 elements (MDX subset)

 

Next step is to sort this subset. This kind of sort does not work all by itself, it requires data, in this case the sort criterion is “Base Salary” column, in this case this is an easy task since the view is constructed in excel Using dropdowns on top.

MDX ORDER function:

ORDER({ElementsToBeSorted,DataSet,DESC)}

ElementsToBeSorted – use aforementioned expression for getting level 0 elements.

DataSet – this is a cubeview.

[CubeName].([Dimension1].[Element],…,[DimensionN].[Element])

Use DESC/ASC as per requirements (top or bottom elements) for numeric values, or use BDESC/BASC for text values.

In this particular case DataSet should look like this:

[Salary].([Year].[2012],[Month].[All Months],[Scenario].[Actual],[Department].[All Departments],[Measure Salary].[Base Salary])

Full MDX expression:

ORDER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},

[Salary].([Year].[2012],[Month].[All Months],[Scenario].[Actual],

[Department].[All Departments],[Measure Salary].[Base Salary]),DESC)}

 

Now there is only one step left – limit the output of the MDX. In order to achieve this, apply HEAD function around current MDX espression:

HEAD(SUBSET,NumberOfElements)

MDX Expression for limited subset (10 elements):

HEAD({ORDER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [Employee] )}, 0)},[Salary].([Year].[2012],[Month].[All Months],[Scenario].[Actual],[Department].[All Departments],[Measure Salary].[Base Salary]),DESC)},10)

 

Attach this MDX expression to the report, rebuild it, enjoy.

This xlsx file is attached to the post, please use it as a reference.

Sample Top 10 Report

TM1 Excel report based on Top 10 MDX list

TM1 Excel report based on Top 10 MDX list

Stay tuned for more posts covering Top 10 topic soon!

 

If you like this post, please spread the love…
This entry was posted in IBM Cognos TM1, Training Material and tagged Excel, IBM Cognos TM1, MDX Use, Reporting. Bookmark the permalink.

9 Responses to "Creating basic Top 10 reports using MDX expression"

Log in to post a comment.