MDX - Only return chosen hierarchy level

Question

I have an MDX Query in which I ask to get the 3rd level within a given hierarchy. When I am querying in MS SQL Server Managemnt Studio, I only get the selected hierarchy. However, when I run the query in an Excel table, I get all three levels.

What I ask for:

• 1st level
  • 2nd level
    • 3rd level <--- I only ask to recieve this guy.

What I get in Excel table:

• 1st level <--- returned
  • 2nd level <--- returned
    • 3rd level <--- returned

What I get in SQL Management studio table:

• 1st level
  • 2nd level
    • 3rd level <--- returned

So my question is:

1) Can I specify exactly what level I want to retrieve in the Excel table?

2) Can I retrieve all three levels in MS SQL Server Management Studio if I wanted to?

Overall I am interested in being able to control what is retrieved, of course :)

Thanks guys!!

Answer 1

See here for how to hide level 1-2 in Excel: use ssas date hierarchy in excel pivottable

How are you running your query in SSMS. Cube browser? MDX query? If you wrote the query then what query did you try?