November 9, 2004 at 7:27 pm
Hi there,
I am using the following MDX to default to the current month.
tail(filter([FiscalYear].[Month].members, not
isempty([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
November 9, 2004 at 9:15 pm
One of the ways we do it is to create a separate dimension that contains only the current period, hide it from users (visible = false) then use this value to obtain the 'correct' current month from the primary time dimension. see MDX below
SUM
({StrToMember("[All Dates].[Fiscal Month Name].["+OpeningPeriod([Current Week].[Fiscal Month Name]).Name+"]")},[Measures].CurrentMember)
The other way we have done this is to use the Now() function, also using the Month(), Year() functions (or alternatively use string manipulation) to get the pieces to form the string for use in string to member. Further to this one, if you have a nice date key, you could just format the Now() function value (say to [yyyymmdd] -> 20041110 ) and then, assuming your time goes to day level, use the ancestor function to select the month that is an ancestor of the current day.
Of the two approaches, we most frequently use the first one now, mainly because it is a shorter and cleaner piece of MDX to get to the current period. Additionally, it doesn't rely on the systemdatetime of the server and/or client, which could cause issues if you were providing access across time boundaries.
HTH,
steve
Steve.
November 9, 2004 at 9:17 pm
Apologies jared, this is what we use for a calc member (located in time dim) that allows users to see current month value for any given measure(watch out for distinct count rollups tho!).
You should be able to use the same approach, just don't put the sum function around the lot.
Cheers,
Steve.
November 10, 2004 at 7:19 pm
Hi steve,
I am not sure I understand exactly what you are suggesting...
I have created a table in SQL which holds the current period. I have then create an additional dimension which is based on this period...
If i have a time dimension called SalesDate, which consists of Year, Quarter, Month, Day, how do i create a calculated measure which is based on this current date....wouldn;t i need to ensure somewhere that it equals the SalesDate, otherwise no data will come through???
Sorry, but my MDX is far from good.
November 10, 2004 at 8:15 pm
Hi Jared,
Forget the first way I said to do it - this works great for calculated members but not for setting the default member of a dimension.
Below is a script for setting the default member for the Time dim within the FoodMart 2000 - Sales cube. Taking this, you should be able to modify the script so that you specify a member either using the vbs Now() function (which will be based on the server current datetime, or alternatively you could add some more objects to the script and query a sql table, text file, whatever, to get the current member.
Dim dsoServer 'As New DSO.Server
Dim dsoDbDimTime 'As DSO.Dimension
Dim dsoDB 'as DSO.MDStore
Dim RightNow
Dim strNewDefaultMember
' Initialize server.
Set dsoServer = CreateObject("DSO.Server") 'New DSO.Server
' Connect to local Analysis server.
dsoServer.Connect "LocalHost"
msgbox("connected to server")
' Connect to the right DB
set dsoDB = dsoServer.MDStores("FoodMart 2000")
msgbox("connected to DB")
' Connect to the right database dimension
set dsoDbDimTime = dsoDB.Dimensions("Time")
msgbox("connected to Dim")
' Set the current date as epr server
RightNow = Date()
' Make the member string
strNewDefaultMember = "[Time].[1997].[Q" & GetQtr(RightNow) & "].[" & GetMonth(RightNow) & "]"
' Set the default member
dsoDbDimTime.DefaultMember = strNewDefaultMember
' Remember to update the dim
dsoDbDimTime.Update
' Just checking 🙂
msgbox(dsoDbDimTime.DefaultMember)
msgbox("changed time default member")
' Close off the conxn and release the objects that vbs should have released already
dsoServer.CloseServer
set dsoDbDimTime = Nothing
set dsoDB = Nothing
set dsoServer = Nothing
' ** useful little functions that stop me from writing really long lines of code....
Function GetYear(TheDate)
GetYear = DatePart("yyyy", TheDate)
End Function
Function GetQtr(TheDate)
GetQtr = DatePart("q", TheDate)
End Function
Function GetMonth(TheDate)
GetMonth = DatePart("m", TheDate)
End Function
HTH,
Steve.
November 10, 2004 at 8:29 pm
Thank you for your reply...but I am sure there must be an easier way to achieve this.
On another forum, I was provided with the script below, which returned the correct current year:
members("[salesdate].[year].[" + cstr(year(now()-1)) + "]")
What I want to be able to do is the same for Quarter, Month and Day, so I can have four time members i.e.
CurrentYear, CurrentQuarter, CurrentMonth and CurrentDay (my time dimension is a 4 level dim)...
If the above works, then surely it can be modified to work for Quarter, month and Day....I am just not sure how to..
Here is an example of my time dim.
2004, Quarter 1, January 2004, 1
November 10, 2004 at 10:08 pm
Ok, so you are looking for members to add to your time dimension, rather than setting the default member.
To do the quarter, month and day, either look up the VBScript help (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctnow.asp) to see if there are quarter, month and day functions, or alternatively use the datepart function (like in the script posted earlier). You'll still have to create the member name string though, as per the example you posted.
Steve.
November 11, 2004 at 5:09 am
Have a look at a previous post - http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=17&messageid=139979#bm140985 and see if that helps in any way
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply