Normalising date ranges in MDX query

Hey,

I've got a requirement for reporting on multiple user provided date ranges from my cube. So the user will provide two date ranges, a "current period" and a "previous period" (for want of a better name for them). I then want to return two types of result set.

The first is to return multiple measures, with a value for each period. Resultset either in the format

	currentPeriodMeasure1	previousPeriodMeasure1	currentPeriodMeasure2	previousPeriodMeasure2
Dimension Member 1
Dimension Member 2
Dimension Member 3
Dimension Member 4
Dimension Member ...

Or ideally

		Measure1	Measure2
Period1	Dimension Member 1
Period1	Dimension Member 2
Period1	Dimension Member 3
Period1	Dimension Member 4
Period1	Dimension Member ...
Period2	Dimension Member 1
Period2	Dimension Member 2
Period2	Dimension Member 3
Period2	Dimension Member 4
Period2	Dimension Member ...

I can do the first of these using this mdx:

 

with set currentPeriod as ([Date].[Date].&[2011-10-01T00:00:00]:[Date].[Date].&[2011-10-15T00:00:00])
	set previousPeriod as ([Date].[Date].&[2011-09-01T00:00:00]:[Date].[Date].&[2011-09-15T00:00:00])
	member previousPeriodRevenue as AGGREGATE(previousPeriod, [Measures].[Revenue])
	member currentPeriodRevenue as AGGREGATE(currentPeriod, [Measures].[Revenue])
	member previousPeriodSales as AGGREGATE(previousPeriod, [Measures].[Sales])
	member currentPeriodSales as AGGREGATE(currentPeriod, [Measures].[Sales])
select {
		currentPeriodRevenue
		, previousPeriodRevenue
		,currentPeriodSales
		, previousPeriodSales
		} on 0
 from [tracking dw2]

 

 

So the first thing i'm wondering is if it'd be possible to return something with the structure more of the second set (or if there's just a better way of doing what i'm doing).

And secondly...

I also want to plot the values by day for both periods, side by side. So I think what I want to do is to normalise the dates in each period into something like Day 1, Day 2, Day 3 etc (or just 1, 2, 3...)

 

Any thoughts / help hugely appreciated.

I found a solution to this. It's not perfect, it doesn't work for periods that overlap, but other than that I think it's ok (there may well be a better way...).

WITH SET currentPeriod AS ([Date].[Date].&[2011-09-01T00:00:00]:[Date].[Date].&[2011-09-15T00:00:00])
	SET previousPeriod AS ([Date].[Date].&[2011-10-01T00:00:00]:[Date].[Date].&[2011-10-15T00:00:00])
	MEMBER currentDateRank AS RANK([Date].[Date].CURRENTMEMBER, currentPeriod)
	MEMBER previousDateRank AS RANK([Date].[Date].CURRENTMEMBER, previousPeriod)
	MEMBER period as IIF(currentDateRank = 0, 2, 1)
	MEMBER dateRank as IIF(currentDateRank = 0, previousDateRank, currentDateRank)
SELECT {period, dateRank, [Measures].[Sales]} ON 0
	, {currentPeriod, previousPeriod} ON 1
FROM [tracking dw2]

So identify the two periods as sets, then rank the days in those sets. The two members with IIF statements effectively do a transform of the ranks when used in conjunction with the {currentPeriod, previousPeriod} set in the SELECT clause.