I have defined the below calc measure to distinct count the number of
products in a product dim with the levels
Company, product, brandname, ....

Count(CrossJoin({measures.[sales]},
descendants([Product].currentmember, [Product Name])), ExcludeEmpty)

However, the count is not rolling up to the Company level.

how do I set this right?

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

To give some more details....

This calculated member is being used to replace a distinct count aggregate
as that does not work with multi select of other dimensions.
(http://msdn.microsoft.com/library/de...l=/library/en-
us/dnsql2k/html/distinct2.asp)

1. If I don't have any other measure except the one i am trying to
calculate, with what measure do we crossjoin? Do we need a dummy measure -
say a count - specially for this?

2. How do we have this calc measure rolling up the product and other
dimensions in the cube?

tia

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

For a single physical cube, the actual base measure used shouldn't
matter, but it will for a virtual cube with multiple underlying cubes.

Can you provide more details or examples of where the count is not
rolling up correctly?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I have a single cube and a distinct count measure alone- so what should i
use for crossjoin?

Is there any chance that the multiselect and distinct count issue will be
resolved in SQL2005? If that is the case I can try using that instead.

thanks very much

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

2005 provide a better DCount with multi-select support.
today, to do the same job, you have to create a calculated DCount measure
like:

count(crossjoin(descendants(Customers.currentmembe r, customers.NameLevel),
{measures.Sales}), excludeempty)


"leela via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote

The problem is I don't have any measure other than the distinct count.
So what do i use in place of the measure.sale? Do i need to introduce a
dummy measure so that I can cross join?

What are the other changes that need to go into the cube for this?
does such a calc member work ok when viewed against the other dimensions
too?

thank you

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

a dummy measure like a simple count of anything

this type of measure works with any dimension in your cube.

"leela via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote