My fact table records sales of products with reason for sale. I have a
distinct count measure of products.
Reasons dimension contains reason1, reason2, reason3 ... etc

How do I get the measure of products that can give the user products that
have sold for reason1 and reason3? Or for ALL reasons. Or for any other
reason combination?

thanks

--
Message posted via http://www.sqlmonster.com

Assuming that you're using AS 2000, Distinct Count measures can't be
dynamically aggregated. In that case, a [Product] dimension, with
individual Products as members, can be used to identify products that
have sold for a given combination of reasons. For a [Sales] cube,
something like:

'{[Reason].[Reason1], [Reason].[Reason3]}'
Member [Measures].[ProductCount] as
'Count(Except([Product].[ProductID].Members,
Generate([ReasonCombo],Except([Product].[ProductID].Members,
NonEmptyCrossJoin([Product].[ProductID].Members,
{[Reason].CurrentMember}, 1)))))'

Select {[Measures].[ProductCount]} on columns
from [Sales]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

If the user wants to dynamically change the reason selection, then is it
possible to do this on a Pivot front end?

Can you let me know, how would this MDX change for SQL2005?

thank you

--
Message posted via http://www.sqlmonster.com