Hello!

When I connect to my cube with Excel, I can evalute its data quite nice, yet
I came across two (maybe it is the same, so only one) problem:

I do a DISTINCT COUNT as BIG INTEGER to a foreign key in the Fact Table to
get the number of entries, DISTINCT since I do count over a foreign key, and
I need to know how many unique values there are (how many matches in the
parent table to the one I have as fact).

I use dimensions, one is a simple date field in this "parent" table, the
other is a self joined relation in another table.

1)
What I see is that if I use 1 month as selection for the date dimension, it
all works out. Same if I select the whole year (Excel 2003 is my client, SQL
2k and Analysis Server 2k both SP4). If I do select 2 months, Excel returns
only #VALUE where the numeric number should be. Both months by themselves to
work fine, just combined it seems not to work.

2)
The second issue is that the other dimension, said self-join, does generate
a row containing "sum of the last section", by default carrying "[label]
data". These values do get flagged #Value too (for any date choice)

Any ideas what this is related to, and how it can be fixed?

It IS related to DISTINCT COUNT, since when I replace it with normal COUNT,
I do get results for both cases.
However, I cannot use COUNT here, I need the number of _distinct_ foreign
keys only to know who many rows are affected in the master table carrying
this key as primary key.

Any help is appreciated (if someone has a good link on how to do row
counting in parent-child-table situations, please let me know!)
Ralf

In article <O4lKHRUPGHA.2668 (AT) tk2msftngp13 (DOT) phx.gbl>, Ralf.Hermanns (AT) gmx (DOT) de
says...
This is by design, in AS2k what happens is that when you select more
than one member in a filter, it generates a calculated member on the fly
to use in the filter which sums the multiple members together. AS2k is
smart enough to know that you should not add distinct count measures,
hence it displays "#val".

Do you need the data members in the parent-child dimension, because you
can just turn them off if you are not using them?

I cannot think of a situation where I have had to combine parent-child
and distinct counts, the documentation in BOL states that distinct
counts only add up values from the lowest level of the dimension. I
would have thought that the data members should be treated as leaf level
members, but it sounds like they aren't.

I don't know if anyone else can chime in here with more information?

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell