MDX - Get MTD between two hierarchies (Month & Week) based on a week value

stelth240
stelth240 used Ask the Experts™
on
Hello Experts,

I'm writing an MDX query in SQL Server 2005 SSRS.

I'm trying to get a MTD value but I have to go through two hierarchies to get it.  We have two time hierarchies in our cube: [Year - Week - Date] and [Year - Month - Date].  The data that's coming in to the query (this is built for Reporting Services) is a week member.  I'd like to get the last day of that week (using LastChild) and somehow translate that over to the Month hierarchy for the same date.  Then I'd like to get the MTD value from that date.  Is that possible?

Here's an example:

Let's say the current week is W2010-13, from the week hierarchy.  In our cube, this date spans over March and April, but the last day of that week is in April (2010-04-03).  I'd like to get the MTD associated with that date based on the Month hierarchy (April 1 - April 3).

Below is an example of what I'm trying to accomplish, though I know it's not correct.  I'm fairly new to MDX so I don't understand most of the functions within the language.  I believe part of my issue is the WHERE clause is filtering down to one day so the MTD won't ever give me the correct data regardless of the hierarchies.  How do I specify a date as a "current member" without causing it to filter the data?

Thank you in advance for any help!
WITH
MEMBER [Measures].[MTD Cases Shipped] AS
	SUM(MTD([Yr - Month - Date].CurrentMember), [Measures].[Cases Shipped])
MEMBER [Measures].[MTD Cases Cut] AS
	SUM(MTD([Yr - Month - Date].CurrentMember), [Measures].[Cases Cut])
SELECT
{
	[Measures].[MTD Cases Shipped],
	[Measures].[MTD Cases Cut]
} ON COLUMNS,
NON EMPTY
{
	[SC Category Desc].[All].Children
} ON ROWS
FROM
	[Cube]
WHERE
    StrToMember(@ToWeek).LastChild //@ToWeek = [Yr - Week - Date].[W2010-13]

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

You can use STRTOMEMBER to convert a string expression based on the member from one hierarchy and return a member from another hierarchy. Here is an example use Adventure Works that translates from the Calendar hierarchy to the Fiscal hierarchy. Not great as the last two levels in each are Month and Date, but the principal is the same:

WITH
MEMBER [Measures].[Fiscal MTD] As SUM(
      MTD(STRTOMEMBER("[Date].[Fiscal].[Date].[" + [Calendar].CurrentMember.LastChild.Member_Caption + "]").parent), [Measures].[Internet Tax Amount])

select {[Measures].[Internet Tax Amount], [Measures].[Fiscal MTD]} ON COLUMNS,
[Date].[Calendar].[Month].MEMBERS ON ROWS

FROM [Adventure Works]

The key is that the lowest level member (Date) is a common member to both hierarchies and therefore the caption values can be directly used. If they were not (say you were moving between two separate dimensions - one formatted dmy and one ymd) you can perform string manipulation functions on the one member caption so it relates to a member in the other dimension.

Tim
TIm,

Thank you for your reply.  You solution looks like what I need, but it looks like my situation requires more information.  The MTD calculation is done on the cube itself, so I assume it does something like [Year - Month - Date].CurrentMember.  Since I can't change the MTD calculation directly, is there a way to use the same idea of StrToMember but do it to somehow "set" the Current Member for the [Year - Month - Date] hierarchy?

I'm not too well versed in cube navigation so I'm not sure if this makes sense or not.  I hope it does.  Both hierarchies are in the same dimension, so the Date members are common to both of them.
I'm not sure what you mean by 'more information'. MTD is simply a shorthand function for PeriodsToDate(Level, Member) - not sure what you mean by 'on the cube itself'.
The above can be used to "get" the current member in the [Year - Month - Date] hierarchy as this is used as the parameter to the MTD function:

WITH
MEMBER [Current YMD] As
STRTOMEMBER("[Date].[Fiscal].[Date].[" + [Calendar].CurrentMember.LastChild.Member_Caption + "]").parent
MEMBER [Meaures].[Cases Shipped YMD] As SUM([Current YMD], [Measures].[Cases Shipped])
etc.

If you need properties of  the [Current YMD] you can get these to as [Current YMD] is a member of the [Year - Month - Date] hierarchy, i.e. [Current YMD].Properties("...")

Does that help?
Tim,

Sorry, I wasn't clear enough before in describing my situation.  What I meant by "on the cube itself" is that the calculated measure is already created in the cube definition rather than the MDX query.  I just put it in my MDX query to show what it's doing.  Because it's in the cube definition, I don't have access to change it.

I think what I meant by more information was, how does my query know what [Calendar].CurrentMember is going to be when it runs?  So [Calendar].CurrentMember.LastChild.Member_Caption should be based on the week from STRTOMEMBER(@ToWeek).  Is this what I'm looking for:

STRTOMEMBER("[Yr - Month - Date].[Date].[" + STRTOMEMBER(@ToWeek).LastChild.Member_Caption + "]").Parent

That looks like it would work (I haven't tested it yet) but that still brings up the problem that I can't change the MTD calculation that's already in the cube definition.  It looks like if this works, I might have to just create my own member directly in the MDX.
MTD is an MDX function - shorthand for PeriodsToDate(....) see http://msdn.microsoft.com/en-us/library/ms144753.aspx
Are you saying that [MTD Cases Shipped] is already defined in the cube?
CurrentMember is not a fixed thing - there isn't a value of 'Currentmember' when the report runs, it is the member over which the query is iterating at a point during the query execution.
However, if your @ToWeek parameter = [Yr - Week - Date].[W2010-13] (i.e. a member of the [Yr - Week - Date] hierarchy) then your conversion of the parameter to get a corresponding member of the month hierarchy should work.
If you include [Yr - Month - Date] in your crossjoin then a CurrentMemebr should be evaluated during query execution and the existing calculation should work.

So try having [SC Category Desc].[All].Children * [Yr - Month - Date].AllMembers in the crossjoin and the where clause as STRTOMEMBER("[Yr - Month - Date].[Date].[" + STRTOMEMBER(@ToWeek).LastChild.Member_Caption + "]").Parent




I got it!

Yes, I did mean that the "MTD calculation" was defined in the cube.  I was referring actually to the calculated member [MTD Cases Shipped] so I shouldn't have kept saying MTD calculation, which I do understand is a built-in function based on PeriodsToDate.  I just wasn't using the right words.  Calculated member is better. :)

I set up the MTD calculation like you mentioned, but I took out the .Parent function so it stayed down at the date level.  When I had .Parent in there, it rolled up to the complete month instead of to the last day of the week.  I wasn't able to combine weekly and MTD data in one query but I don't need to anyway, so this works for what I need to do.

I'm just going to leave the calculated measure in the MDX query and not use the calculation defined in the cube.

Thank you so much for your help.  I have the code below so you can see what I did.
WITH
MEMBER [Measures].[MTD Cases Shipped] AS
	SUM(MTD(STRTOMEMBER("[Yr - Month - Date].[Date].[" + STRTOMEMBER(@ToWeek).LastChild.Member_Caption + "]")), [Measures].[Cases Shipped])
MEMBER [Measures].[MTD Cases Cut] AS
	SUM(MTD(STRTOMEMBER("[Yr - Month - Date].[Date].[" + STRTOMEMBER(@ToWeek).LastChild.Member_Caption + "]")), [Measures].[Cases Cut])
SELECT
{
	[Measures].[MTD Cases Shipped],
	[Measures].[MTD Cases Cut]
} ON COLUMNS,
NON EMPTY
{
	[Category Desc].[All].Children
} ON ROWS
FROM
	[Cube]

Open in new window

Thanks again for your help.  The final query seems so simple but I was missing several key pieces.  Thanks for figuring it out for me.
No problem - glad you got there! I've used this approach to translate members between dimensions a fair bit - it's fairly simple and quite flexible.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial