Saturday, January 11, 2014

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

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
="{ TOPCOUNT( TM1FilterByLevel(TM1SUBSETALL([Region]),0), 3.000000, [SalesCube].([actvsbud].["&C14&"],[model].[Total],[account1].[Sales],[month].[Year])) }"
  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.

No comments:

Post a Comment