Microsoft KB Archive/318695

= Calculated measures disappear from OLAP PivotTable when you group them =

Article ID: 318695

Article Last Modified on 2/1/2007

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition
 * Microsoft Office Excel 2003

-



This article was previously published under Q318695



SYMPTOMS
If you group calculated measures in a PivotTable for Online Analytical Processing (OLAP) data, data may disappear, and the calculated measures no longer appear in the field list.



CAUSE
This problem occurs if the calculated measures depend on measures that are hidden in the Microsoft SQL Server Analysis Manager. When you group measures in an OLAP PivotTable, Microsoft Excel creates new levels in the hierarchy. To do this, Excel creates a proxy cube, queries the database again for the data to include, and then builds the new levels. If there are calculated measures that depend on hidden measures, the problem occurs.



WORKAROUND
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To work around this problem, set all measures to be visible in the Microsoft SQL Server Analysis Manager. If you have to hide cube fields in Excel, you can use the client-side properties to do this programmatically. To do so, follow these steps:   It is important that the fields appear in the correct format. Run the following code to create a list of all cube fields in the PivotTable: Sub CreateList_Click Dim CurCell as Range Set CurCell = Range(&quot;J1&quot;) 'Change this to put the list outside 'the PivotTable range. For Each cf In ActiveSheet.PivotTables(1).CubeFields CurCell.Value = cf.Name Set CurCell = curcell.Offset(1, 0) Next End Sub  Remove any list entries for fields that you want to keep in the PivotTable.  Run the following code to delete the remaining fields from the PivotTable field list: Dim cf As CubeField

Function FWantToHide(Curfield As String) As Boolean

For Each c In ActiveSheet.Range(Cells(1, 10), Cells(65536, 10).End(xlUp)).Cells 'Change this to match the range used in the CreateList macro.

If c.Value = Curfield Then FWantToHide = True Next End Function

Private Sub cmdHide_Click For Each cf In ActiveSheet.PivotTables(1).CubeFields If (FWantToHide(cf.Name)) Then cf.ShowInFieldList = False Next End Sub 



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Additional query words: XL2002 XL

Keywords: kbbug kbdtacode kbpending KB318695

-

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

© Microsoft Corporation. All rights reserved.