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 the 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 an Excel report is to write an MDX query inside an active form.
For this example, lets use a standard Payroll model to rank staff based on their salary.
Create Dynamic Report
First step – generate the view in TM1 Perspectives.
Then export it to Excel as a Dynamic Report (Active Form):
Write the MDX for the Top 10
Now, let the fun begin! The next step is to write an 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 an MDX parameter:
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:
- {TM1SUBSETALL([Dimension])}
- {TM1FILTERBYLEVEL(Subset, Level)}
Sort the Subset to Derive the Top 10
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. 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 cube view.
[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 the current MDX expression:
HEAD(SUBSET,NumberOfElements)
MDX Expression for a 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.
Stay tuned for more posts covering Top 10 topic soon!
