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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You are asking for the descendants of both members, hence 6. Try this:

WITH
SET [Time_Selection] AS '{[Time].[1997].[Q1],[Time].[1997].[Q2]}'
MEMBER [Measures].[Months_In_Quarter] as
'Count( Descendants(Time.CurrentMember,[Time].[Month],SELF ))'
SELECT
{[Measures].[unit sales],[Measures].[Months_In_Quarter]} on
columns,
{[Time_Selection]} on rows
FROM SALES

public @ the domain below
www.tomchester.net

"Shabnam Naghshineh" <snaghshineh (AT) supplychain (DOT) com> wrote

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

** The contents in this message are provided as is and is not guaranteed
to be correct **

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!