Hi,

I have the following cube with Date and Product dimension shown:

Date/ | 1-jan-05 1-feb-05 1-mar-05 1-apr-05 1-may-05
Product |
---------------------------------------------------------
1 | 100 2000 5000 9000
2 | 2000 5000 9000
3 | 100 2000 5000

the figures shown are some sales amount.

I need to extract non-empty cells (i.e. amounts) for each member of the
product dimension into a set, say we call it 'AmountSet'. This set
should contain non-empty cells for each product member i.e. Jan-05 to
Apr-05 for Product 1; Feb-05 to Apr-05 for Product 2, and Jan-05 to
Mar-05 for Product 3.

I have created a measure that takes a set of values and fetched into
Excel for compution (say Excel!Sum), like the following:
Excel!Sum(SetToArray(AmountSet, measures.amount))

AmountSet was defined as 'Filter(time.members, not
isempty(measures.amount))' but obviously this does not take into
account of the respective product, and returns Jan-05 to Apr-05
instead.

How can I define the AmountSet to take into account of the current
member of the product dimension?

Any help is very much appreciated!

Thanks,
Philip

With Analysis Services 2000, unlike Calculated Members, Named Sets are
not dynamically re-computed, so the sets for each Time member would have
to be saved:

'Generate({[Product].[1]:[Product].[3]},
CrossJoin({[Rpoduct].CurrentMember},
Filter(time.members, not isempty(measures.amount))))'

http://groups-beta.google.com/group/...rver.olap/msg/
f7d3a7429b6b2bc1?hl=en
From: "Mosha Pasumansky [MS]" <mos... (AT) microsoft (DOT) com>
Date: Tue, 10 Jun 2003 16:18:53 -0700

Subject: Re: Help with Named Sets

Named sets are evaluated when the session is open and stay static
afterwards. If you refresh your session, then the named set will be
updated.

==============================*=================== =
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================*=================== =

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***