XL: Calculated Measures Disappear When OLAP PivotTable Is Grouped (313611)


The information in this article applies to:

  • Microsoft Excel 2002
  • 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 "missing" 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.
Modification Type:MinorLast Reviewed:1/6/2006
Keywords:kbbug kbpending KB313611
©2004 Microsoft Corporation. All rights reserved.