Microsoft KB Archive/313611

= XL: Calculated Measures Disappear When OLAP PivotTable Is Grouped =

Article ID: 313611

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition
 * Microsoft Office Excel 2003

-



This article was previously published under Q313611





SYMPTOMS
If you group items in a PivotTable based on an OLAP cube, calculated measures may disappear from the PivotTable.



CAUSE
When you group the PivotTable, Excel creates a proxy cube, which contains a new level for the grouping. Any noncalculated measures that have their visible property set to False are not requested for the proxy cube. If you calculated the measures that are dependent upon these &quot;missing&quot; measures, those calculated measures become invalid. Any invalid measures are dropped from the proxy cube and are not available to your PivotTable.



WORKAROUND
To work around this issue, set all the precedent calculated measures to be visible. To do this, follow these steps:
 * 1) In the SQL Analysis Manager, click the plus sign (+) to expand the server, database, and cubes items.
 * 2) Right-click the cube name, and then click Edit. The Edit Cube dialog box appears.
 * 3) Scroll down the Cube Explorer (the top left pane of the dialog box) until you reach the measures.
 * 4) Click the Advanced tab on the properties pane. For each measure that has dependent calculated measures, verify that the advanced property Visible has a value of True.

When you group the PivotTable, your calculated measures should be retained.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Additional query words: XL2002 Pivot table column items rows data

Keywords: kbbug kbpending KB313611

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.