March 22, 2007 at 6:48 pm
I'm writing a report in Reporting Services which requires an MDX query as the foundation of its data. The report is pretty simple:
__________Dec LY | Dec TY | % | Jan LY | Jan TY | % ... | Total LY | Total TY | %
Customer 3000 4000 33
Item A 300 350 16
...
I want the last 12 full months on columns (from the Time dimension), with their prior twelve months alongside them, and a percentage change along side both of them. It is to be grouped on rows by customer (Customer dimension) then item (Product dimension). The measure is litres of product sold, but it could be dollars etc.
I could make 36 calculated members and include these in a named set, but I'd rather not. What is the simplest MDX query I can write?
March 22, 2007 at 8:17 pm
I'm not sure on how you'd ensure it gets ordered correctly, maybe you could re-org it in Reporting Services. But I would try using a combination of the GENERATE function and then possibly LAG or COUSIN. Something like (pseudo code, so can't copy and paste this and have it work)...
WITH
SET [MySet] AS 'GENERATE ({[Time].[2007].MEMBERS, [Time].&[2007]}, if ([time].currentmember.level = 0, [Time].Currentmember.lag(1), [Time].CURRENTMEMBER.LAG(12)), ALL)'
SELECT
[MySet] on 0,
[Customer].[Customer].MEMBERS * [Product].[Item].MEMBERS ON 1
FROM
[Some_cube]
Another thing that might make it a bit more elegant would be to try using the descendants function to create the initial set of current years months and current year (e.g. something like descendants([time].[2007], SELF_AND_BELOW)). Not sure what the valid second parameter values are, best look these up in BOL.
Steve.
March 29, 2007 at 1:21 am
Hi Steve,
For some reason I had issues getting the IF statement to work inside the GENERATE statement. However, I found a workaround using the following:
'GENERATE({[TimeNJ].[Fiscal].[Month].MEMBERS}, {[TimeNJ].[Fiscal].CURRENTMEMBER.LAG(1)}, ALL) - GENERATE({[TimeNJ].[Fiscal].[Month].MEMBERS}, {[TimeNJ].[Fiscal].CURRENTMEMBER.LAG(25)}, ALL'
I'm pretty new to this stuff and only stumbled upon that through trial and error. It's still probably quite inefficient. Now all I need to do is give each field a dynamic alias in Reporting Services, so I don't need to to change the columns every month. Hmm...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply