Reporting Services on AS2005 cube

Hi all,

I'm trying to create a RS report that will use an AS2005 cube as a datasource. I did try to use the query wizard from reporting services to build the mdx statement but I'm having a hard time with it... How could I use the following MDX statement in a reporting services report? Do you have to use the query builder?

Code Snippet

WITH SET [Last 5 Months] AS

{TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(2),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(3),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(4),

TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(5)}

MEMBER [Date].[Calendar].[AVG 5 months] AS

AVG([Last 5 Months])

MEMBER [Date].[Calendar].[Last Mth Var.] AS

(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1) - AVG([Last 5 Months]))

MEMBER [Date].[Calendar].[YTD] AS

AGGREGATE(YTD(TAIL( NONEMPTY( {[Date].[Calendar].[Month].MEMBERS}, {[Measures].[Internet Sales Amount]} ) ,1).ITEM(0).LAG(1)))

SELECT

{[Last 5 Months],[AVG 5 Months],[Last Mth Var.],[YTD]} ON COLUMNS,

NONEMPTY([Product].[Category].Members) ON ROWS

FROM [Adventure Works]

WHERE [Measures].[Internet Sales Amount]

It's probably very simple to acheive but for some reason I can't get this to work properly...

I did a lot of RS against relational DB but reporting out of a cube is new to me.

TIA,

Eric

In the query builder, there's an option at the top for design mode, clicking this will bring you to the mdx.

If you've already been there, sorry for the explanation. Are you receiving any errors?

cheers,

Andrew

|||The SSAS 2005 provider, which the MDX Query Designer uses, requires measures on columns while you request an MDX set. The long story short -- you cannot use this query as it is with the MDX Query Designer. Instead, consider using the Microsoft OLE DB Provider for Analysis Services 9.0. It allows you to send whatever query you want but it doesn't support parameterized queries.|||

Thanks Teo,

I looks like RS is a good reporting tool against Relational DB but it's not so good with complex MDX that you need to parameterized...

|||Please post this to the wish list on connect.microsoft.com.