3

Is there anyway that we can get the top 10 throught out the hierarchy instead of top level in MDX. I would like to write an MDX query which pulls the top 10 at each level in the hierarchy. Please let me know if it is possible.

Hierarchy looks something like below:

  • Regions(get Top 10)
    • Countries(get Top 10)
      • States(get top 10)
        • Cities(get top 10)
          • So on...
Benoit
  • 1,995
  • 1
  • 13
  • 18
user346514
  • 513
  • 4
  • 10
  • 33

1 Answers1

2

If you want to get the top 10 on a level you can use

TopCount([your dimension].[Regions].Members, 10, 'your measure')
TopCount([your dimension].[Countries].Members, 10, 'your measure')
...

If you want to get the top 10 regions with their top 10 countries... You can use DrilldownLevelTop:

DrilldownLevelTop(TopCount([your dimension].[Regions].Members, 10, 'your measure'), 10,, 'your measure')

You have to add other DrilldownLevelTop around the previous expression if you want to get the members on the other levels.

Benoit
  • 1,995
  • 1
  • 13
  • 18
  • The query works fine in Sql server, but if i use the same query in the performance point i am getting the error message "Exception type: InvalidOperationException Exception message: Error during serialization or deserialization using the JSON JavaScriptSerializer. The length of the string exceeds the value set on the maxJsonLength property. " Any idea on this. I changed the web.config file but no luck. – user346514 Jul 30 '12 at 17:30
  • @user346514 You should change the ``maxJsonLength`` property. (I do not know how to do this) – Benoit Aug 10 '12 at 20:57