I’ve been working with the other components of BI (SSIS, SSAS, SSRS, PPS) for the past 6 weeks and haven’t had much chance to work on the MOSS component.

Here is something I had to struggle with and hope you guys find it useful:
In the cube, one of the most common tasks is to do data analysis/comparison for different time periods and almost all the time, the reference point is NOW.
How did our numbers compare to a period in the past w.r.t. today/this month/this quarter, etc

Here are some MDX examples to solve 90% of those worries. Might come in handy if you want to create a named set based on such:

1. Current Month MDX
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”))

2. Current Quarter MDX
The VBA function format() does not have a provision for returning the current quarter, so here is what you do – as above, you get to the current month and lookup its parent to get to the quarter. Now, the caveat is that your date hierarchy should be defined as such (Month to be a child of Quarter). In my case, it is Year -> Quarter -> Month -> Date
strtomember (“[Date Hierarchy].[Month].&[” + format(now(), “yyyyMM”) + “]”).Parent)

It saved my day and hope it helps you.
In case you know of another (better or worse) way, let me know.

Advertisements