Classic multi-select problem. I am trying to display the delta between visits to our clinic for the current timeframe versus the same timeframe last year. Pretty straightforward application of ParallelPeriod.
The following query works just fine:
WITH
MEMBER [Measures].[Test] AS
'[Measures].[Visits] - (ParallelPeriod([Time].[Visit Date].[Year], 1), [Measures].[Visits])'
select
{[Test], [Visits]} on columns
from [Clinic]
where ([February 2006])
However, change the where clause to include a set rather than a member...
WITH
MEMBER [Measures].[Test] AS
'[Measures].[Visits] - (ParallelPeriod([Time].[Visit Date].[Year], 1), [Measures].[Visits])'
select
{[Test], [Visits]} on columns
from [Clinic]
where ({[February 2006], [March 2006]})
...and it breaks with the following error: #Error The MDX function PARALLELPERIOD failed because the coordinate for the 'Quarter' attribute contains a set.
Thanks to Moshe's very useful article on multi-select (http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx), I understand what is going on. Since I am selecting a set rather than a single member from the visit date hierarchy, parallelperiod chokes.
WHERE I NEED HELP:
What I would like to be able to do is apply parallelperiod to each member of the set and aggregate the result. As I understand it, that would fix my problem and, I believe, give me the correct result. However, I'm not yet good enough at MDX to figure out how to do this. Help, please?
Here is an example using Adventure Works and the "Existing" operator to sum the total for whatever months are contained in your WHERE clause set:
Original Query
WITH
MEMBER [Measures].[Test] AS
[Measures].[Order Count] - (ParallelPeriod([Date].[Calendar].[Calendar Year], 1), [Measures].[Order Count])
SELECT
{[Measures].[Test], [Measures].[Order Count]} on columns
FROM
[Adventure Works]
WHERE
([Date].[Calendar].[Month].&[2004]&[1])
Revised Query using Existing
WITH
MEMBER [Measures].[Test] AS
SUM(Existing [Date].[Calendar].[Month].Members,
([Measures].[Order Count] - (ParallelPeriod([Date].[Calendar].[Calendar Year], 1), [Measures].[Order Count])))
SELECT
{[Measures].[Test], [Measures].[Order Count]} on columns
FROM
[Adventure Works]
WHERE
({[Date].[Calendar].[Month].&[2004]&[1],
[Date].[Calendar].[Month].&[2004]&[2]})
HTH,
Steve
No comments:
Post a Comment