I have a cube to which report builders connect to make reports with report builder 2.0, since they don’t want to write MDX for complex functions and just use drag and drop to create the reports i decided to create named sets with some default time functions like YTD, YTD previous year, year difference, current month, previous month and moth difference. This blog post tells the story of that achievement :).
To start with i have a pretty simple time dimension time with a simple hierarchy:
As first step i tried the most simple: current month. My initial thought was to use the LASTCHILD function with the following expression:
[Time].[Month].LASTCHILD
this looked ok, but on closer inspection it seems to return the last Month level, so when your in april (month 4) of 2009 but you have december (12) in your dimension as well it would return 12. After some googling i came up with a new idea, to create a new attribute called yearmonth (combine the 2 in the DSV) and use the LASTCHILD of that field, that worked but i didn’t feel good with me, I left it a while and continued with the rest and found the function ClosingPeriod some time later, this function returns the last member of a time level, perfect :). I rewrote the function accordingly:
ClosingPeriod([Time].[Calendar].[Month])
Watch me use the complete hierarchy expression here, one thing that helped me alot understanding all these expression was the debug function of Mosha’s MDX Studio.
Next up was previous month, the obvious function was to use ParallelPeriod, which eventually formed:
ParallelPeriod ([Time].[Calendar].[Month],1,ClosingPeriod([Time].[Calendar].[Month])).
Which worked great.
now to join these 2 (and use them on the x-axis on a report) i found the function Union:
Union ([Current Month] ,[Previous month])
I tried using crossjoin but the sets use the same dimension and thats not usable in crossjoin)
Now for the year level, Current year to date:
Ah this one is easy using the YTD function, first i used YTD([Time].[year].LASTCHILD) this returned the year in the set but i decided to use the ClosingPeriod again which returns the months of the current year in the set:
YTD(ClosingPeriod([Time].[Calendar].[Month]))
The last on my todo list is YTD from the previous year, this one took the longest to finish and let me to eventually find the ClosingPeriod function which made my life a lot easier 🙂
YTD(ParallelPeriod([Time].[Calendar].[Year]
, 1
, ClosingPeriod([Time].[Calendar].[Month]))
Get the current month and go back one year from that, determine the year to date to that month.
Join these tow named sets together to get a year difference, to do that i needed 2 identical sets with the same level, in my first YTD statement i used [Time].[year].LASTCHILD) this function uses the year level while the ytd from previous year uses the month level, these two aren’t joinable. This is the reason i used the ClosingPeriod on the YTD, it now returns a month and will lead the ytd function to return the months of the year.
So now i could use UNION:
Union ([Current year to date],[Previous year to date])
Eventually leading to the following script:
