MS SQL Server OLAP - Bug in OWC 11 / MSOLAP2?

Author

Bug in OWC 11 / MSOLAP2?

Timmy

2006-03-23, 3:31 am

Calculated measure defined as
[Measures].[Quantity (All Category)] = ([Measures].[Qty], [Category].[All
Category])
does not show the total correctly in the owc 11 pivottable when i filtered
out one of the [Category] member on the column.

[Measures].[Quantity (All Category)] value was filtered instead of showing
the total value!

Analysis Services 2000 was used.

I did not encounter this problem using Crystal Analysis.

Deepak Puri

2006-03-23, 3:31 am

This may be related to the "Visual Totals Mode" used by Excel and OWC,
as explained in this past post:

http://groups.google.com/group/micr....olap/msg/fe7f6
45622c760b0[color=da
rkred]
From: Chris Webb
Date: Tues, Apr 19 2005 3:41 am

Well, I think the best place to start is probably the section on
VisualTotals
in this white paper:
http://www.microsoft.com/technet/pr...ntain/anserd...
What the paper calls 'Visual Totals Mode' is switched on by using the
'Default MDX Visual Mode' connection string property, and this is what
Excel
uses to calculate its subtotals. So once you know that the subtotals are
really just parent members with their values overwritten, it's clear
that
calculated measures are going to be evaluated after this has taken place
and
so aren't going to be affected - and that's why, to get the behaviour
needed
in this case, it was necessary to create a real measure and get the
desired
values into it somehow.
...[color=darkred]

To see the VisualTotals() effect in AS 2000, try this Foodmart query in
the MDX Sample app:
[color=darkred]
With Member [Measures].[Sales (All Category)] as
'([Measures].[Unit Sales], [Product].[All Products])'

select {[Measures].[Unit Sales], [Measures].[Sales (All Category)]} on
0,
VisualTotals(Except(
DrillDownLevel({[Product].[All Products]}),
{[Product].[All Products].[Drink]})) on 1
from Sales[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Timmy

2006-03-27, 3:29 am

I found the solution:
- right-click the pivot-table and bring up the commands and options dialog.
- under the 'report' tab, select 'all items' instead of 'visible items only'.

Tim

Deepak Puri

2006-03-27, 8:27 pm

Tim,

That's right - selecting all items switches off the Visual Totals()
mode. But then you can't see the sum of just the selected items, even
for the original [Measures].[Qty].

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Sponsored Links