It is pretty straightforward to get measure values for members in a hierarchy. In Adventure Works, say you wanted to get [Internet Sales Amount] for all products by color, you can use this query

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Color].[Color].MEMBERS ON 1
FROM [Adventure Works]

Sales By Color

and, to get [Internet Sales Amount] for all products by class, you can use this query

SELECT [Measures].[Internet Sales Amount] ON 0,
NON EMPTY [Product].[Class].[Class].MEMBERS ON 1
FROM [Adventure Works]

 Sales By Class

It gets trickier if you want to get the output to show both results in the same query

Sales By Color And Class

You can UNION the two result sets by creating a set of members from the 2 hierarchies, using their [All] members respectively. Something like

WITH MEMBER [Measures].[Attribute] AS
IIF([Product].[Color].CurrentMember IS [Product].[Color].[All]
, [Product].[Class].CurrentMember.NAME
, [Product].[Color].CurrentMember.NAME)

SELECT {[Measures].[Attribute], [Measures].[Internet Sales Amount]} ON 0,
NON EMPTY {
([Product].[Class].[Class].MEMBERS, [Product].[Color].[All]),
([Product].[Class].[All], [Product].[Color].[Color].MEMBERS )} ON 1
FROM [Adventure Works]

Union Sales By Color And Class

Advertisements