How to get curret member in a calculated set of calculated members? 1

[Shab]

I have a cube similar to Sales in FoodMart_2000 and I have
an interface that I let users make selections on Time, for example select a few months and then they
go to another tab, where they can look at the results from the cube for their selection.
My problem began when I let them select at a lower level and then go and make a query with a higher level.
For example they can select only September and October, and then they can decide they want to see their
results by Qaurter. Now September is in Quarter 3 and October in in Quarter 4, but in my query I cannot
use Quarter 3 and Quarter 4 since I only need a subset of them. So I make my calculated members in time dimension
and I call them [Time].[1997].[Q3 ] and [Time].[1997].[Q4 ]. Notice the space at the end, it lets me create
a member with simillar (almost) name and the user will never see the space at the end in GUI.
Look at the folowing example in FOodMart_2000. When I use Time.CurrentMember I get 0 as number of months.
If I specify Solve orders I get 1.j# or #ERR back.
I'll appreciate your help if you can think of someway to do this.

Thanks,

Shabnam



WITH

MEMBER [Measures].[Months_In_Quarter] as 'Count( Descendants(Time.CurrentMember,[Time].[Month],SELF ))'

SET [Month_Selection] AS '{[Time].[1997].[Q3].[9],[Time].[1997].[Q4].[10]}'

MEMBER [Time].[1997].[Q3 ] AS 'AGGREGATE(INTERSECT(Descendants([Time].[1997].[Q3],[Time].[Month],SELF),{[Time].[1997].[Q3].[9],[Time].[1997].[Q4].[10]}))'

MEMBER [Time].[1997].[Q4 ] AS 'AGGREGATE(INTERSECT(Descendants([Time].[1997].[Q4],[Time].[Month],SELF),{[Time].[1997].[Q3].[9],[Time].[1997].[Q4].[10]}))'

SET [Time_Selection] AS '{ [Time].[1997].[Q3 ], [Time].[1997].[Q4 ]}'

SELECT
{[Measures].[unit sales],[Measures].[Months_In_Quarter]} on columns,
{[Time_Selection]} on rows

FROM SALES

 

[justins57]

Here's your solution:

WITH

MEMBER [Measures].[Months_In_Quarter] as 'Count(Intersect(Time.CurrentMember.Siblings,Month_Selection))'

SET [Month_Selection] AS '{[Time].[1997].[Q3].[9],[Time].[1997].[Q4].[10]}'

MEMBER [Time].[1997].[Q3].[Q3] AS 'Aggregate(INTERSECT(Descendants([Time].[1997].[Q3],[Time].[Month]),{[Time].[1997].[Q3].[9],[Time].[1997].[Q4].[10]}))'

MEMBER [Time].[1997].[Q4].[Q4] AS 'Aggregate(INTERSECT(Descendants([Time].[1997].[Q4],[Time].[Month]),{[Time].[1997].[Q3].[9],[Time].[1997].[Q4].[10]}))'

SET [Time_Selection] AS '{ [Time].[1997].[Q3].[Q3], [Time].[1997].[Q4].[Q4]}'

SELECT
{[Measures].[Unit Sales],[Measures].[Months_In_Quarter]} on columns,
{[Time_Selection]} on rows

FROM SALES

 

[Shab]

Thanks Justin57,

I thought I never get an answer to this. I posted this 9 months ago! I changed the GUI so that the user is not able to make a selection on a higher level when they make selections at a lower level and that seemed to be fine with users.
Thanks so much for the solution, I tested it and it works.

Shab

 

[justins57]

Shab,

I joined the site this week looking for a solution to another MDX puzzle (MDX - Per Store Avg). It is a little like your query, but I may have to work it in the business logic code, too.

I've been working with MDX for a few years now, so I hope to be able to help out when I can. This forum looks like it is getting used regularly. I plan on using it as needed.

Justin

 

[MDXer]

justin I saw your mdx issue and was going to take a stab at it but haven't had a chance, (I'm getting some demos ready for a tradeshow). I had some calcs on the order of what your doing in the past but need to remember how I did it and apply it to yours.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck