Tuesday, December 13, 2016

MDX parallel period query

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

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

   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

   FROM [Adventure Works]  )


No comments:

SSL Error - The connection for this site is not secure

 After cloning a git repo of dot net framework website and trying to run it all I could see was this error Turns out the fix was to simply e...