SSAS FAQ MDX FAQ MDX-How do you write query that uses execution date/time (NOW()) as a parameter?
MDX-How do you write query that uses execution date/time (NOW()) as a parameter?
User Rating: / 1
PoorBest 
Written by Vidas Matelis   
Sunday, 15 April 2007 17:27

Q: How do you write MDX query that uses execution date/time as a parameter?

A: You can make MDX query that use function now() to get execution date and use it to define date dimension member. You should build a string that defines dimension member in proper format and then convert that string to member using StrToMember function.

While building member string a lot depends on how your date dimension is defined. This example is build using Adventure Works database where date member looks like [Date].[Date].[April 15, 2007]:

SELECT {[Measures].[Internet Order Count]} ON 0
, {StrToMember("[Date].[Date].["
+ Format(now(), "MMMM dd, yyyy") + "]")
} ON 1
FROM [Direct Sales];

Other date format options:

M - one or 2 digits. Example: 1, 11
MM - Month 2 digit, Example: 01, 11
MMM - Month 3 characters, Example: Jan, Nov
d - day one or 2 digits, Example: 1, 11
yy - year last 2 digits, Example: 07
yyyy - year 4 digits, Example: 2007
etc...

So if your date is format like 20070415, then you can use format:
Format(now(), "yyyyMMdd")

Note mm is different from MM, mm is ued to define minutes!

Note: After this FAQ was released, additional information about this issue was published by Mosha Pasumansky here.

< Prev   Next >
 
Strategy Companion