Hi, All.
I know that this question was asked a million times all over the internet,
but I can't find any definite answer or an example how to solve this problem.
It's an old DISTINCT_COUNT thing. As we all know, when you connect to your
cube that has a measure as a Distinct_Count through an Excel, it works fine
when you have all members shown. But when you want to select only a couple of
members (set a filter), all you see in Grand Totals is "#VALUE". Everyone is
saying that that's the logical behaviour of AS, because it can not summarize
distinct counts. But then many articles, including SQL Server BOL are saying
this: "Because distinct count measures are nonadditive, the presence of a
distinct count measure significantly restricts the ability of Microsoft® SQL
Serverâ„¢ 2000 Analysis Services to preaggregate the cube. For this reason, it
is recommended that each distinct count be placed in its own cube with no
other measures. These cubes with distinct count measures can then be joined
together with other cubes in a virtual cube that efficiently manages all of
the measures." So, I tried to create a separate cube with ONLY this one
distinct count measure and then create a virtual cube, but it still didn't
fix this "#VALUE" problem. So, is there a way to fix it ? Anyone has any
particular examples ?
Please, help. It's urgent.

Thanks,
Victor.

For the sake of performance, you should create the distinct value in a
separate cube. The Grand Totals issue has not changed. It will still
behave the same way.

Sorry.


VictorV wrote:

That's what I did. I created a separate cube with a single and only measure
for distinct count. So, you are saying that it's impossible to have Grand
Totals to work correctly in Excel or Excel PivotTable web part in Share Point
? How do you present something like that to your management ? How do you tell
them that all this investment in time and software can't produce such a
simple and very common in analysis task, as to be able to do what they call a
Basket Analysis with distinct values ?
There has to be some workaround for this kind of common task. I am sure that
everyone had to deal with similar problem at some point. There has to be an
acceptable solution.

"Gary Gibbs" wrote:

Under the PivotTable Options, you can select the "For error values,
show:" and then allow the totals to be blank.

If totals were allowed for distinct counts, you would get bad data. No
data is better than bad data.

By the way, that total can be displayed - just not with the detail you
want. It requires a second calculation.

If you need a report that shows both values (detail and totals), you
might want to look into Reporting Services.

Hi Victor,

The best option is to use AS 2005 - maybe this can help make a stronger
case to upgrade.

http://groups.google.com/group/micro...olap/msg/9d7f6
19440e0ce7b

From: Mosha Pasumansky [MS]
Date: Fri, Oct 7 2005 1:50 am
Groups: microsoft.public.sqlserver.olap

Any kind of MDX which will emulate Distinct Count, will likely suffer
from
the same problem of end users not being able to do multi-select
properly.
Therefore, I recommend upgrading to Yukon, where multi-select works fine
with Distinct Count measures.

--
==============================*=================== =
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL*og/mosha
Development Lead in the Analysis Server team
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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