After experimenting for some time with MDX parallel period function I could finally write a query which returns two parallel periods as separate rows and not as another column in the same row.
The final query looks like this
But interestingly along the way I could figure out another not very idealistic method of achieving the same results, mentioning here just in case it can be used by someone someday
The final query looks like this
SELECT { [Measures].[Internet
Sales Amount] } ON COLUMNS,
NON EMPTY{[Date].[Calendar Weeks].[Calendar Week].ALLMEMBERS} on rows
FROM (
SELECT { ParallelPeriod ([Date].[Calendar Weeks].[Calendar Year]
, 1
, [Date].[Calendar
Weeks].[Calendar Week].&[3]&[2012]) , [Date].[Calendar Weeks].[Calendar
Week].&[3]&[2012] }
ON 0
FROM [Adventure
Works] )
But interestingly along the way I could figure out another not very idealistic method of achieving the same results, mentioning here just in case it can be used by someone someday
SELECT { [Measures].[Internet
Sales Amount] } ON COLUMNS,
NON EMPTY{[Date].[Calendar Weeks].[Calendar Week].ALLMEMBERS} on rows
FROM (
SELECT ({ Filter( [Date].[Calendar Weeks].ALLMEMBERS - [Date].[Calendar Weeks].[(All)].ALLMEMBERS, Instr(
[Date].[Calendar Weeks].currentmember.Properties( 'Member_Caption' ), 'Week 3 ' ) = 1 ) } ,{
[Date].[Calendar Year].&[2011], [Date].[Calendar Year].&[2012] } )
ON 0
FROM [Adventure
Works] )
No comments:
Post a Comment