Handling Filters in JavaScript

Handling Filters in JavaScript

Scenario:
If there are 2 KPI, One shows Top most product sales and other shows that Top product’s monthly sales trend. Here monthly sales trend KPI depends on the Top most product sales KPI to get the Top product. Below are the steps to handle that dependency and apply filter based on the product.

MDX Query:
KPI 1: Top most product sales 

Result will be only one Product which has high sales by Amount.

select NON EMPTY(TopCount({ Descendants([Product].[All Products] ,[Product].[Product])}, 1, [Measures].[Sales Amount])) on ROWS,
NON EMPTY({[Measures].[Sales Amount]}) on Columns
from [Sales Overview]

KPI 2: Top product’s monthly sales trend 

Result will be monthly sales by Top Product only.

select NON EMPTY({ Descendants([Invoice Financial Month].[All Financial Periods] ,[Invoice Financial Month].[Months])}) on ROWS,
NON EMPTY({[Measures].[Sales Amount] , [Measures].[Sales Qty]}) on Columns
from [Sales Overview]
where (${filter}) // In this filter parameter, that top product will be placed from post execution

Post Execution: 

KPI 1: Top most product sales

Add this below line within the function in the post execution section,
myFilter [KPIID] = ‘[Product].[‘+res[0][0]+’]’;

Explanation:
myFilter [KPIID] // It should have the KPI ID of the 2nd KPI
‘[Product].[‘+res[0][0]+’]’; // This will extract the Top Product name from the result of MDX query to pass it on to 2nd KPI

Now 2nd KPI will show the monthly sales trend of the top product being displayed in the 1st KPI.