You are currently browsing the monthly archive for March 2012.

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

Timeline

March 2012
M T W T F S S
« Feb    
 1234
567891011
12131415161718
19202122232425
262728293031  

Blog Stats

  • 42,939 hits