Current Member in a calculated(named) set

[Shab]

Hi all,

Using FoodMart 2000, I have a calculated set formed of two quarters in 1997 and I put that on rows. For each row I want to calculate how months are in that row. (In this example I know the answer is 3, because all quarters have all months, but my other cube does not have all months in each quarter)
What I get back is 6 instead of 3. When I change the MEMBER definiton to be like [Time_Selection].CurrentMember or if I use Current I get #ERR as the result.

Any ideas for a workaround?

Shabnam



WITH

SET [Time_Selection] AS '{[Time].[1997].[Q1],[Time].[1997].[Q2]}'
MEMBER [Measures].[Months_In_Quarter] as 'Count( Descendants( [Time_Selection],[Time].[Month],SELF ))'

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

FROM SALES

 

[MDXer]

your above query will return 6 as the month count because your doing a count on your set which has 6 months in it. There really is no need for the set in your query as placing the members on rows accomplishes the same thing. then you can modify your Months_in_Quarter calculation so that is counts the months that are descendants of the current member. The following code works against foodmart.

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

SELECT 
{[Measures].[unit sales],[Measures].[Months_In_Quarter]} on columns,
{[Time].[1997].[Q1],[Time].[1997].[Q2]} on rows

FROM SALES

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

 

[Shab]

Ok. What you suggested worked. Here is my real problem. 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

 

[MDXer]

You need to implement som iif logic to test for the level and if it isnot quarter or higher then you need to reference the parent. I can provide some sample code tonight

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