SSAS MDX : Appending columns from different result sets

Question

I've started working with SSAS cubes recently. I have a requirement where I need results from different date ranges to be shown side-by-side, for the required measures. Something like :


                         Last 7 Days     Last 6 Months   Last Full Year
Internet Sales Amount        X                Y               Z
Reseller Sales Amount        X                Y               Z

Any pointers towards a solution would be greatly appreciated.

Thanks,
Venu

AustinDahl AustinDahl · Accepted Answer · 2012-01-31T08:03:51

I'm only guessing (and maybe my syntax is wrong), but perhaps something like this:

WITH
  MEMBER [MYTIME][Last 7 Days] AS 
    'Aggregate(LastPeriods(7, StrToMember(Format(CDate(),"YYYY-MM-DD"))))'
  [MYTIME][Last 6 Months] AS
    'Aggregate(LastPeriods(6, StrToMember(Format(CDate(), "YYYY-MM"))))'
  [MYTIME][Last Full Year] AS
    'Aggregate(LastPeriods(12, StrToMember(Format(CDate(), "YYYY-MM"))))'
SELECT
  {[MYTIME].[Last 7 Days], [MYTIME].[Last 6 Months], [MYTIME][.Last Full Year]} ON COLUMNS
  {[Internet Sales Amount], [Reseller Sales Amount]} on ROWS
FROM MYCUBE

That one gets 12 months for the the full year, but if you mean calendar year, you'll want to do something different.

SSAS MDX : Appending columns from different result sets

1 Answers