|MDX-How do you write query that uses execution date/time (NOW()) as a parameter?|
|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]:
Other date format options:
So if your date is format like 20070415, then you can use format:
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.
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you calculate monthly average of one year, optionally including empty months?
- MDX-How do I calculate sales for 12 Month to date?