Hi,
I recently noticed in Excel and OWC that the reports that use
distinct count measures - where there is a Year hierarchy - can
return incorrect totals when there are row/column filters. If the
filters are more than 1 level deep it does not take the filter into
consideration and returns a larger Grand total - as if the filter never
took place.

I have successfully replicated this in AdventureWorks sample
easily. In the Cube browser just pull in the Date.Calendar hierarchy on
the row axis and the Order Count measure (from SalesOrders folder) .
The total is 31,455 Try to filter on Semester , Quarter or lower
levels - the Grand total will never change ! (still 31,455) Only if
the filter is on year itself will it change - nothing lower. .

I also experimented with filtering on a year (e.g. 2003) and
quarters in a year (e.g. Q2) - same issue. E.g. 2003 total count is
11,176 , if I filter on 2003 and Q2 and Q4 Grand total will not change
- only semesters filter will work - nothing lower in this scenario.

Is this a bug? Any help/ feedback would be appreciated. (If it's a
bug, how can it be reported to Microsoft ?)

Thank You,
HaroldW.

I can't reproduct the bvehavior using the Adv. works sample.
any time filter applied return the right result.
have you apply the SP1 and post sp1 patch?

To report a bug, use the connect.microsoft.com website.


<hwinzelberg (AT) gmail (DOT) com> wrote

Jeje wrote:
Thanks for your response Jeje.

I applied the post sp1 hotfixes as well tonight - same problem I
was also able to replicate this on a second machine. I notice that this
happens on any dimension when the row/column selection filters on 2 or
more levels deep (e.g. product category heirarchy if row/column filters
select categories with specific subcategories).

Jeje, when you try the filters make sure it's done only at the row or
column level not the page axis or filter pane in BI studio. Please let
me know if you have any luck replicating. I will report to Microsoft
- hope they will see problem.

thanks,
-HaroldW.

can you reproduce the issue using MDX queries?

Sometimes the OWC doesn't do a good job.

<hwinzelberg (AT) gmail (DOT) com> wrote

Jeje wrote:
Jeje, Not really sure how to replicate exactly in MDX. How do I do
grandtotal ?

My guess is that Excel may doing some work locally on its own - when
calculating the grand total. I noticed when I was profiling using sql
profiler - grand totals were not returned - only different heirarchy
levels were returned

e.g. if I ask for sales amount or other measures for Jan and Feb of
2003 the internal query returns amounts for 3 fields 1) All 2003, 2)jan
2003 amt and 3)feb 2003 amt - Then I guess excel adds jan + feb to get
grand total for regular measures. Perhaps it works different with
dist. count measures since it does not know how to add them.

Thanks,
HaroldW.

grand totals are visualtotals in the pivottable.

and your issue is related to multiple selection filters, not simple filter.
but I continue to not be able to reproduce it.
I can do any type of multi selection without any problem.

use the profiler to intercept the MDX queries generated by the pivottable


<hwinzelberg (AT) gmail (DOT) com> wrote

Jeje,
I ran the profiler for a query when I was in Excel. The query did
not use visualtotals - here is the code Row selections are on 2003
and and H1 -> Q1 and H2 -> Q3. The grand total is still 12440 the full
total for 2003.

SELECT NON EMPTY
HIERARCHIZE(Except({AddCalculatedMembers(Except({A ddCalculatedMembers({DrillDownMember({Except({AddC alculatedMembers(DrillDownMember({DrillDownLevel({ [Date].[Calendar].[All
Periods]})}, {[Date].[Calendar].[Calendar Year].&[2003]}))},
{[Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Calendar
Year].&[2002], [Date].[Calendar].[Calendar Year].&[2001]})},
{[Date].[Calendar].[Calendar Semester].&[2003]&[1],
[Date].[Calendar].[Calendar Semester].&[2003]&[2]})})},
{[Date].[Calendar].[Calendar Quarter].&[2003]&[4],
[Date].[Calendar].[Calendar Quarter].&[2003]&[2]}))},
{[Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Calendar
Year].&[2002], [Date].[Calendar].[Calendar Year].&[2001],
[Date].[Calendar].[Calendar Quarter].&[2003]&[4],
[Date].[Calendar].[Calendar Quarter].&[2003]&[2]})) DIMENSION
PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Adventure Works] WHERE
([Measures].[Order Count])

By the way, it is strange. When I just filter on quarters in 1
semester e.g. 2003 -> H1 -> Q1 it works fine, but when I filter on
quarters belonging to different semesters in 2003
e.g. 2003 , H1 -> Q1 and H2 -> Q3 I get the wrong totals.

Thanks again,
HaroldW.

Jéjé wrote: