SQL Server Performance Forum – Threads Archive
Obtaining current month from system date using MDX
Hi there, I am using the following MDX to default to the current month. tail(filter([FiscalYear].[Month].members, notisempty([FiscalYear].currentmember)),1).item(0)
The issue i have however, is if there is an incorrect transaction in the system for a date in the future, or of there is budget data, this MDX will not work. How can I use MDX to somehow point to the Current month from the System date? Is there a way to do this? e.g. Where system month = dimension time period
MDX allows you to incorporate VBA functions into your code, so for your example you can use Month(Date()) that will generate 11 for November.
Yeah but there will no difference form year to year. I found this MDX to work well. Tail(Filter([MyDate].[Day].members, [MyDate].Properties("Is_Current_Date_Flag") ="yes" ),1).item(0)
Alternativly I was thinking you could use something like this. You can set the Default Member property for the Time dimension to an MDX formula, which uses the VB Now() function to pick up the server date, e.g. StrToMember("[Time].[Month].[" + Format(Now(), "mmmm") + "]") This will let the time dimension default to the current month based on the server date, but you might want to consider adding a flag to the Time dimension table to denote the current period. This is because you probably want to default based on the load date, not the server date. If you have a flag you can use the Filter function to pick up the default member, or see the SQL Server Accelerator for BI for good example of time dimensions. http://blogs.msdn.com/bi_systems/articles/162831.aspx Setting up the flags is also a very good approach. Raulie
hp
]]>