Posted: 1/17/2012
I have a dimension called location having a hierarchy Country, State, City.......
I written an mdx query which will return top 3 customers having high sales amount.
Find the mdx query below: Created the below mdx as named set in cube.
TOPCOUNT([Customer].[Customer Name].[Customer Name],3,[Measures].[Sales Amount])
SELECT
NON EMPTY TOPCOUNT([Customer].[Customer Name].[Customer Name],3,[Measures].[Sales Amount])
ON COLUMNS,
NON EMPTY { [Measures].[Sales Amount] } ON ROWS
FROM [Cube]
I created a chart in PPS (performance point server) for Top 3 customers by Sales Amount,
having Sales Amount on series, Named set on bottom axis.
It works fine when i slice it through country level but if i drill down to state and city
level it is not working fine.
The same top 3 customers whose has high sales at country level is divided and given at
state and city level when i do a drill down. But if i do a query in database the list of top
3 customers at state level is slight different from the result i get from drill down from
country to state.
Please help me on this..... Thanks in Advance.