One common issue occurs when setting a dimension hierarchy as a parameter, as in screenshot 1 (for this example, I will be using Customer Geography hierarchy in the AdventureWorks Cube).
|Screenshot 1 - Parameter|
|Screenshot 2 - Large dropdown list|
Solution 1 (if you only need the top level in the hierarchy):
- In the Report Data, right click the Datasets folder and select "Show Hidden Datasets".
|Screenshot 3 - Show Hidden Datasets|
- Right click the new dataset that appeared, and select "Query...". You'll be shown some scary, unformatted code (called MDX). Don't fret!
|Screenshot 4 - MDX with ALLMEMBERS|
- In the code, there is one and only one place with the text "ALLMEMBERS", as seen in screenshot 4. This can be safely replaced with "CHILDREN" as in screenshot 5. If you execute the query, you will see that you only get the members without all the lower levels.
|Screenshot 5 - MDX with CHILDREN|
- Click OK to save the query and preview the report. The dropdown list only shows the countries now, and is much cleaner than before.
|Screenshot 6 - Clean dropdown list|
Solution 2 (to get a specific level in the hierarchy):
- The first solution works only if you require the members at the highest level (in the example, countries). But what if we want cities only for example? The first thing is to repeat the steps as above until screenshot 4.
- This time, we will leave ALLMEMBERS as it is, and instead replace the statement preceding it. In this case, it is [Customer].[Customer Geography]. In the left column under the Metadata tab, find the level that you require and hover the mouse over it to get the statement you need, as in screenshot 7.
|Screenshot 7 - Getting level MDX statement|