Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Analysis Server and Reporting Services (2008)  MDX - How do I get a known subset of a hierarchy?

Author  Topic  AgileDBA Starting Member 1 Post Posted - 2011-05-10 : 05:52:42 How can I get a known subset of hierarchy members when slicing a cube. The code below (against Adventure Works) will work. It will return all reseller sales in Arlington, Texas between 7/1/2005 and 7/1/2006. ALMOST what I want. If I change &[Arlington]&[TX] to allmembers - a get a result set for all Cities in the hierarchy. But I want just Arlington and Atlanta. If I uncomment Atlanta, I get "The City hierarchy is used more than once in the Crossjoin function." How can I limit my result set to just two cities?? select { [Measures].[Reseller Order Count] } on columns, non empty ( [Geography].[City].[City] ) on rows from ( select ( { [Date].[Date].&[20050701] : [Date].[Date].&[20060701] * [Geography].[City].&[Arlington]&[TX] // * [Geography].[City].&[Atlanta]&[GA] } ) on 0 from [Adventure Works] ) AgileDBA