August 7, 2009 at 6:26 pm
Hello all.
I am trying to determine curent date in MDX.
I found this interesting solution:
http://www.miky-schreiber.com/Blog/PermaLink,guid,2efa7c5b-826e-43c4-af42-5608fdd6c188.aspx
I implemented it and created an Attribute called CurrentDateFlag and try to use this MDX to test the data:
select non empty
Measures.[SomeMeasure] on columns
from [TargetHours_currentTime]
where
{(
[Target Hours Fact].[EmpID].&[1003]
,LinkMember([Fiscal Date Dim].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key])
:
LinkMember([Fiscal Date Dim].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key]).Lag(30)
)}
query does not fail but doesn't return any data.
I know I have data because this query returns the data:
select non empty
Measures.[SomeMeasure] on columns
from [TargetHours_currentTime]
where
{(
[Target Hours Fact].[Employee Contractor ID].&[1003]
,[Fiscal Date Dim].[Date Key].&[2009-07-05]
:
[Fiscal Date Dim].[Date Key].&[2009-08-06]
)}
I appreciate your suggestions!
Thanks,
Peter.
August 7, 2009 at 7:46 pm
You have created the attribute but not the User Hierarchy You need both.
The attribute (whose values will be 1 or 0) and then a user hierarchy (again set to not visible) where *all but one* date keys will be under the '0' parent and the 'current date' will be the only child of the '1' parent.
I would recommend leaving the visibility on both the Attribute and User Hierarchy set ot Visible while you debug this. That way, you can check, yes, the 'current date' is the only child of the '1' parent in that hierarchy.
Using your example, you have an attribute named CurrentDateFlag. You now drag this (in the dimension editor) in as a User Hierarchy, remembering to put the Key attibute under it in that hierarchy. Let's assume the Hierarch is called 'CurrentDay'. Using this information, your query would now look like ->
select non empty
Measures.[SomeMeasure] on columns
from [TargetHours_currentTime]
where
{(
[Target Hours Fact].[EmpID].&[1003]
,LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key])
:
LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key]).Lag(30)
)}
If you have both of these, then the link member should work and also the range operator.
HTH,
Steve.
August 8, 2009 at 3:01 pm
Thanks a lot, Steve.
I missed hierarchy creation portion. Thanks for highlighting it and detailed explanations. It works perfect now.
Just curious - is there a way to build current date without creating user hierarchy?
August 9, 2009 at 3:35 pm
Looks like I need more help.
My MDX query now works, but I am struggling with creating this new calculated measure based on query logic in my cube. Basically I want to create a new measure based on two other base measures in my cube. The scope of this new measure will be rolling 28 days like define in my query above.
I know how to create a member but don't understand how to define its calculation to be based on time period of 28 days using my query logic.
Thanks a lot for your help!
Peter.
August 10, 2009 at 1:36 am
From my personnel experience usually current date is calculated as a date for which we have most recent data (considering we have at least daily etl load).
The mdx would be something like this:
{tail(filter([Date].[Calendar].[Day].Members, [Measures].[Order Lines] > 0),1)}
August 13, 2009 at 12:55 am
I understand that it will give me the measure (in your example Order Line) for the current day. Correct?
I need a total measure aggregated for the entire time period, like in my query.
August 13, 2009 at 10:10 am
It's hard to give you exact answers as we don't know all of your specifics, but an approach like the following should, in theory, work.
Assuming measures M1 and M2 are what we want to add, and these are naturally summable measures (ie not distinct counts or avgs or *anything* else).
In the MDX script, it could look like....
CREATE MEMBER [CurrentCube].[Measures].[New MEasure] AS
SUM(
{(
[Target Hours Fact].[EmpID].&[1003]
,LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key])
:
LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key]).Lag(30)
)}, [Measures].[M1])
+
SUM(
{(
[Target Hours Fact].[EmpID].&[1003]
,LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key])
:
LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key]).Lag(30)
)}, [Measures].[M2])
So basically summing measure 1 over the rolling 28 days (or in this case, 30 days) and doing the same for measure 2, and then adding these together.
HTH,
Steve.
August 13, 2009 at 6:40 pm
Thanks a lot, Steve! It worked out great!
Peter.
August 13, 2009 at 8:07 pm
How can I use the above hierarchy to define default values for report parameters in my report? It is a Reporting Services report is sitting on the top of Analysis Services 2008.
I have two parameters: StartDate and EndDate which basically define data range for the data on the report. They are drop down select lists.
I currently have static defualt values:
[Fiscal Date Dim].[Date Key].&[2009-08-10]
I want to use the hierarchy discussed above to define default value:
[Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0)
When I run it as an MDX query it returns the result I expect but when put in as a value for a report parameter I get nothing. Report does not fail but I don't get any value and have to select it manually.
I appreciate your help!
Peter.
August 13, 2009 at 8:44 pm
Little bit unclear as to exactly how you're implementing this, but going on assumptions again -> assuming that the paramters were prob created by SSRS for you, then they're likely to use some form of casting a string to a member. If this is the case, you can't just use the other hierarchy, you need to put a function around this 'current date' memeber to get it back to your hierarchy that you're using.
So, for the 'to' date, assuming you want that to be 'today', you could try using the same function as per the query, so ->
LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key])
this should, in theory, give you the member from the [Date Key] hierarchy of the [Fiscal Date Dim] dimension, which you'll note is the same dim and hierarchy your drop down list is populated with.
Very similar story for the default for 'from date', except you use the Lag to push it back say 30 days, so ->
LinkMember([Fiscal Date Dim].[CurrentDay].[CurrentDateFlag].&[1].Children.Item(0), [Fiscal Date Dim].[Date Key]).Lag(30)
)
HTH and hope this works! 🙂
Steve.
August 13, 2009 at 11:20 pm
Yes, my paramters were created by a RS Query Designer. Here is the entire query generated by the tool
//********************
SELECT NON EMPTY { [Measures].[Remote Hrs], [Measures].[PTO Hrs], [Measures].[Total Charged Hours], [Measures].[Target Hours], [Measures].[Total Worked Hrs], [Measures].[ChargedTargetAttainment%], [Measures].[Other Hrs], [Measures].[Training Overhead Hrs], [Measures].[DoNext] } ON COLUMNS
, NON EMPTY { ([Target Hours Fact].[Person Full Name].[Person Full Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOMEMBER(@FromFiscalDateDimDateKey, CONSTRAINED) : STRTOMEMBER(@ToFiscalDateDimDateKey, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@TargetHoursFactJobCategory, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@TargetHoursFactRegion, CONSTRAINED) ) ON COLUMNS
FROM [TargetHoursCube])))
WHERE ( IIF( STRTOSET(@TargetHoursFactRegion, CONSTRAINED).Count = 1, STRTOSET(@TargetHoursFactRegion, CONSTRAINED), [Target Hours Fact].[Region].currentmember ), IIF( STRTOSET(@TargetHoursFactJobCategory, CONSTRAINED).Count = 1, STRTOSET(@TargetHoursFactJobCategory, CONSTRAINED), [Target Hours Fact].[Job Category].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
//*******************
The solution you suggested didn't work - the report doesn't fail but no default value is picked up. I still have to manually select date ranges.
Thanks a lot for your help and time!
Peter.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply