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…

9 comments on “Creating basic Top 10 reports using MDX expression

  1. Ben Hill says:

    Good stuff Ivan, Cant wait to see how we could use this kind of thing to create a multifaceted scorecard & dashboard!

  2. Maximilian Parsons says:

    Thanks for this example.

    The combination of MDX with Active Forms is very powerful indeed. I have an Active Form that identifies potential candidates for Sales awards according to multiple criteria:

    – Salesman Gross Sales > User selected threshold
    – Salesman Commission Percent > X%
    – Salesman one of the top quatile performers in at least 2 of the last 3 years.

    The last one was tricky!

  3. Maximilian Parsons says:

    Probably worth noting that MDX statements of length > 256 characters won’t work in Excel (at least not 9.5.2 + Excel 2003).
    However very long MDX statements will work quite happily when the workbook is published to the web.

  4. TonyM says:

    Just starting with MDX. Are there any “gotchas” on doing the above with TM1 9.5.1?

    I’m getting the following problems:
    1. It’s returning numbers but they’re (definitely) not the Top 10.
    2. The returned numbers are not in sequence (though chunks of them are)
    3. The Head function doesn’t seem to suppress zero’s though I would have thought that sorting the outputs would get around that

    I’m not sure about the 256 char limit. I’m getting the same answers in Excel as in the Subset Editor (when I paste in to the expression window) which I assume means it’s ok. In any case, I can get the MDX down to 255 for testing without solving the problems.

    Thanks

  5. Hi Tony,
    As a starting point, you could use subset editor for simple expressions like Return Level 0 elements.
    Also try doing it step by step – add one function at a time, so that you can trace where the problem is.

  6. TonyM says:

    Hi Ivan, thanks, I believe I did do it step by step but there’s really only two steps – return level 0 (this bit works), then sort them by reference to a cube view (this therefore must be the bit that goes wrong!!). What would be a good way of testing the MDX for creating the cube view – surely I can’t just dump the MDX into the TM1RPT parameter?

  7. Ernest says:

    Hello, everyone!

    Would it be possible to create MDX for ActiveX colmns either?
    I would like to dynamically change them basing on parameters. E.g. display only YTD months.

  8. Ravinder says:

    Hi, Just a thought, If there is a list in the active form column and the intent is the get the top 10 elements, Why cant we use the xls Sort ( Z to A) in built functionality available?

    This will give you a sorted list and the business can use the top 10 /30 / 50 etc wahtever elements required.

    I mean the effort to sort basis Basic Salary seems much lesser then writing an MDX expression. In Any case, for each time the data will change, we have to recalc the sheet .

    Is there any practical benefit to write ‘top X’ MDX ?

    1. Hi Ravinder,
      There is a point for creating scorecard like reports as these would require many active forms in one sheet.

      Also it might be useful if you have different forecast calculations for top 10 customers (or products or cost centre) and the rest.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

/* THIS WAS THE CODE USED FOR EMBEDDING A SURVEYMONKEY POPUP */