MDX – find a period using current date

When writing reports in SSRS, Crystal, Tableau or others I am of the opinion that the knowledge workers consuming the report shouldn’t have to choose too many parameters. Don’t get me wrong, flexible parameter choice is useful for what-ifs and for exploration & discovery, but there should be some sensible default values in those parameters.

Generally this involves using the current date because people are interesting in the now, the short term past or the short term future. Alternatively they are interested in trends over time periods up until now. Either way you slice it, right now is the pivotal moment in time. Profound.

In Oracle you might start by selecting Sysdate and from there determining it’s month or year using to_char and it’s handy freeform date formatting. In SQL Server it might be something similar starting with GetDate() and then determining the month or date using DatePart() or some of the other useful date functions. In both cases using a calendar table with fiscal periods if that’s what’s appropriate might be the better solution.

But what about the Getdate() of MDX? Devmau5 has covered this useful snippet.

StrToMember(“[Date].[Date].[” + Format(now(), “dd-MM-yyyy”) + “]”

I’ve copied it here because y’know, links die sometimes.

I recommend reading that post because Devmau5 raises the other important point. You could use currentmember to show you the current period against a measure, but the measure must be something that is never filled ahead of time. I’ve made this mistake before and it came back to bite me. Assuming that accounting figures would never end up in the cube for future dates is one of those ‘it will never happen’ moments that inevitably does. If it’s possible, not matter how unlikely, it will happen.
Variations on the date formatting in this solution will cover many implementations. I’ve used this one for example: “yyyy-MM-ddT00:00:00” , but it really depends on the cube you are using and it may not be as friendly as that

For example when you have a date member that is based on an integer starting on day 0. I recently came across a cube just like this, where day 0 was 30th December 2007.

StrToMember(“[DATE DIM].[Fiscal-Calendar].&[” + CStr(DateDiff(“d”, “30/12/07”, Now())) + “]”).Parent

The string to member function will recreate the member in something like this format [DATE DIM].[Fiscal-Calendar].&[467]
“Parent” then climbs up the hierarchy to give you the next level up which in this particular hierarchy happens to be the 4-week fiscal period.
If your date member is not a hierarchy then that won’t work.

Lucky, because Now() is not really an MDX function, it’s VBA for expression services ( note this MSFT article is old and says it applies to Analysis Services 2000 but I think it’s still valid) but it works outside of the MDX as part of the input string. This probably won’t work if your cube isn’t in SSAS, and it doesn’t really make much sense to include some kind of now/sysdate/getdate function into the MDX language itself.

Leave a Reply

Your email address will not be published. Required fields are marked *