I'm new to MDX but want to keep my code as clean as possible.
I have a query that looks at todays sales and compares them against LY and LY-1 using the ParallelPeriod function. It looks something like this..
With Member [Date].[SalesCalendar].[DateToday] as [Date].[SalesCalendar].[Date].&[2012-10-26T00:00:00]
SELECT
{[Date].[SalesCalendar].[DateToday],
ParallelPeriod([Date].[SalesCalendar].[Year],1,[Date].[SalesCalendar].[Date].&[2012-10-26T00:00:00]),
ParallelPeriod([Date].[SalesCalendar].[Year],2,[Date].[SalesCalendar].[DateToday]}
*
{[Measures].[Total Sales],[Measures].[Units],[Measures].[Sales Target]}
ON Columns,
[Locations].[Location PK].[Location PK]
on Rows
From MyCube
I start by defining a member that points to today's date. I want to define it once and use it throughout this query (and other queries I write), the theory being I can change it in once place and the underlying query reacts.
The problem I have is that if I try and use this calculated member within the ParallelPeriod function I get no results. With the query above I get results for the first column and the first call to ParallelPeriod (for LY) works but the second call for LY-1, which uses the declared member, fails.
I'm guessing this is down to my lack of knowledge with MDX and so I guess I am missing something fundamental. However, banging my head against the wall isn't working so I need some help!
Any ideas what I am doing wrong?
Thanks
This cannot work because when your query is evaluated [Date].[SalesCalendar].[DateToday]
is not replaced with [Date].[SalesCalendar].[Date].&[2012-10-26T00:00:00]
.
You created a member that will give the same numeric values than [Date].[SalesCalendar].[Date].&[2012-10-26T00:00:00]
in the pivot table cells.
You can try this query:
With Member [Date].[SalesCalendar].[DateToday] as [Date].[SalesCalendar].[Date].&[2012-10-26T00:00:00]
Member [Measures].[name] as [Date].[SalesCalendar].CurrentMember.Name
SELECT
{[Measures].[name], [Measures].[Total Sales]} ON Columns,
{[Date].[SalesCalendar].[DateToday], [Date].[SalesCalendar].[Date].&[2012-10-26T00:00:00]} on Rows
From MyCube
The Total Sales
will be the same but not [Measures].[name]
.