How to create top X count in active form using MDX expression

Check list of all posts

There are two ways to create top X count in active form: 1) using dynamic subset with architect and 2) using worksheet functions with prospective. One of major advantages using architect is to record MDX expression without manually writing MDX expression. This document is use a sample to get top 3 countries for different measures.

Using dynamic subset with architect

Step 1: Create Top 3 countries subset

The MDX expression is automatically created.

Step 2: Create a view based on dynamic subset from prospective as below

 Step 3: open prospective with active form and get sheet as below

 Step 4: Connect this sheet with application

 Step 5:  Open TM web

Using worksheet function with prospective

However, the solution above is not flexible enough, such as you cannot change the measure from Sales to budget as top 3 countries. But we can use the pre-generated MDX expression as start point.

Step 1: Create a simple view in prospective

Step 2: Get active form and select the first row, where you can put MDX expression in a excel cell, and TM1RPTROW can refer to it.

Step 3: Create MDX expression in spreadsheet using dynamic subset as start point

C11:

="{ TOPCOUNT( TM1FilterByLevel(TM1SUBSETALL([Region]),0), 3.000000, [SalesCube].([actvsbud].["&C14&"],[model].[Total],[account1].[Sales],[month].[Year])) }"

Notes:

1. We need to replace TM1SubsetBasics() with TM1SUBSETALL([Region]), otherwise it doesn’t work in active form when published in TM1 web
2. The MDX expression becomes dynamic, as we can replace any strings from selection. This functionality will open a door for active to handle very complicated logic as requested.