MS SQL Server OLAP - AS2000-OLAP Cube (Distinct Count) & OWC11

Author

AS2000-OLAP Cube (Distinct Count) & OWC11 - Pivot Tables

Graham Marko

2005-08-31, 1:24 pm

I have a problem with Office Web Components (OWC11) - Pivot Tables involving
an AS2000-OLAP Cube Distinct count problem. All the totals work fine if none
of the rows or columns are filtered.

Once I try to filter any of the dimensions in the cube, I get a Formula
Error.

If I filtered any of the rows or columns when using the distinct count I
would get a "#VALUE!" in the Grand Total column or row.

If I mouse over the entire error message read: "Formula error - aggregations
are not supported for the DISTINCT COUNT measure "DistinctUsers" - in the
Aggregate function.

If I do not choose a DistinctCount for Totals and I choose <a simple measure
count> everything works fine. Including all the filtering.

Is anyone aware of this issue. Do you know of any solutions? If this is a
legitimate Microsoft bug, how can we go about getting this resolved. Do you
have Microsoft connections to speed up the process.

Any help on this issue or related issues would be greatly appreciated.

I have heard from some web posting that this was not an issue with OWC9 but
it is with OWC10 &11. Using OWC 9 would be an acceptable solution for the
customer.

I have also seen a lot of references that this issue is not a problem in
SQL2005.

Here are some related links I have found and tracked etc

http://www.databasejournal.com/feat...10894_3482536_1

http://www.databasejournal.com/feat...10894_3454931_2

http://msdn.microsoft.com/library/d...l/distinct2.asp

http://www.awprofessional.com/artic...sp?p=29580&rl=1

Thanks

Graham Marko

Jéjé

2005-08-31, 8:24 pm

simply disable the visual total option of your OWC.
in this case the "total" use the "all member" value instead-of trying to
apply a visual total
by default OWC11 enable the visual total option to make sure that the user
see the total of only selected or filtered members. But this doesn't works
with DCount measures.
without visual total, the user will see the "all members" value, so if the
user select 2 products in the filters, the dcount will return the number of
customers of all products and not only the customers of the selected
products only.

another option is to convert your DCount measure by a calculated dcount
measure. in this case the visual total works fine, but the performance is
impacted.

"Graham Marko" <GrahamMarko@rogers.com> wrote in message
news:OH%23$OSlrFHA.4040@TK2MSFTNGP14.phx.gbl...
>I have a problem with Office Web Components (OWC11) - Pivot Tables
>involving an AS2000-OLAP Cube Distinct count problem. All the totals work
>fine if none of the rows or columns are filtered.
>
> Once I try to filter any of the dimensions in the cube, I get a Formula
> Error.
>
> If I filtered any of the rows or columns when using the distinct count I
> would get a "#VALUE!" in the Grand Total column or row.
>
> If I mouse over the entire error message read: "Formula error -
> aggregations are not supported for the DISTINCT COUNT measure
> "DistinctUsers" - in the Aggregate function.
>
> If I do not choose a DistinctCount for Totals and I choose <a simple
> measure count> everything works fine. Including all the filtering.
>
> Is anyone aware of this issue. Do you know of any solutions? If this is a
> legitimate Microsoft bug, how can we go about getting this resolved. Do
> you have Microsoft connections to speed up the process.
>
>
> Any help on this issue or related issues would be greatly appreciated.
>
> I have heard from some web posting that this was not an issue with OWC9
> but it is with OWC10 &11. Using OWC 9 would be an acceptable solution for
> the customer.
>
>
> I have also seen a lot of references that this issue is not a problem in
> SQL2005.
>
>
> Here are some related links I have found and tracked etc
>
> http://www.databasejournal.com/feat...10894_3482536_1
>
> http://www.databasejournal.com/feat...10894_3454931_2
>
> http://msdn.microsoft.com/library/d...l/distinct2.asp
>
> http://www.awprofessional.com/artic...sp?p=29580&rl=1
>
>
>
> Thanks
>
> Graham Marko
>
>

Sponsored Links