Hello, All!

We are using Excel XP PivotTable as OLAP client and we encounter a lot of
problems. The most serious problem is with calculated cells : in some cases
Excel XP shows incorrect results ! All other clients that we have tested
including Excel 2000 show correct results. Here is a simplified description
of the problem

Let's say a cube has a 'Products' dimension and a measure 'Quantity'.
Now, we want to show the actual quantity for a product category and
percentage of the category among all products, so we introduce one more
dimension ValueType with two members: Actual and Mix
'Actual' is the value specified in the fact table, and 'Mix' is defined as
'[ValueType].[Actual] / ([Products].[All Products], [ValueType].[Actual])'
(Normally 'Mix' should be a calculated member, but Excel does not support
calculated members in dimensions other than Measures, so we introduce an
artificial regular member and then define calculated cells for it.
Let's say we've got the following fact table
ID, ValueType, Product, Quantity
1, Actual, Product1, 70
2, Actual, Product2, 30

In Excel XP PivotTable, when we browse the whole cube everything is fine:

Products, Actual, Mix
Product1, 70, 0.7
Product2, 30, 0.3

Now we hide Product2 using member selector and get unexpected results:

Products, Actual, Mix
Product1, 70, 1

0.7 magically changed to 1 !!!

All other clients including Excel 2000 display correct results
Is there a solution for this problem?

With best regards, Andrew Surinov. E-mail: sav (AT) tut (DOT) by

Andrew,
opposed to excel 2000, excel xp leaves an option for
partial sums.

If one shows the following products
Product Amount
table 100$
chair 150$
monitor 150$

the sum is 400$

If one hides the chair as in
Product Amount
table 100$
monitor 150$
.... the sum is up for discussion
the total sum is still 400$
the sum of all visible elements is 250$

In XP you can choose which sum you will get by clicking
on an icon in the pivot table toolbar. The icon is a
table with a green star. A sum marked with a star is
always the total sum. It is probably called someting
like "sow hidden elements in grouping functions" (I have
the german excel edition). It does actually make more
sense than in previous excel editions.

HTH
Lutz