Reporting Services, pivot function or union on MDX

I have to build a rather simple report with only one chart. The chart has to show a 100% stacked bar chart, with the VBS members as series.

I have 2 measures, subscriber count and total revenue. Now I want 2 bars: a first one showing the vbs stacks on subscriber count and a second one showing the vbs stacks on total revenue.

Because subscriber count and total revenue are measures, I cannot add them to the categorie fields in reporting services.

This means that I have to somehow perform a sort of pivot function on my mdx query, or a kind of union to create a record per measure.

This is the basic mdx query I have now:

SELECT NON EMPTY { [Measures].[Total Net Rev], [Measures].[Subscriber Count] } ON COLUMNS, NON EMPTY { ([Dim Vbs].[Vbs].[Vbs].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DimMcoMco, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimTimeTimeHierarchy, CONSTRAINED) ) ON COLUMNS FROM [RaceSegments])) WHERE ( IIF( STRTOSET(@.DimTimeTimeHierarchy, CONSTRAINED).Count = 1, STRTOSET(@.DimTimeTimeHierarchy, CONSTRAINED), [Dim Time].[Time Hierarchy].currentmember ), IIF( STRTOSET(@.DimMcoMco, CONSTRAINED).Count = 1, STRTOSET(@.DimMcoMco, CONSTRAINED), [Dim Mco].[Mco].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks

Joos

Anybody?

I need a 100% stacked chart with 2 bars. One for subscriber count and one for Total Net Revenue. I would like to visualize the distribution of the VBS dimension members within each bar.

To make it easier...this is the mdx query without any parameters:

SELECT NON EMPTY { [Measures].[Total Net Rev], [Measures].[Subscriber Count] } ON COLUMNS, NON EMPTY { ([Dim Vbs].[Vbs].[Vbs].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [RaceSegments] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS