Calculated measures disappear from OLAP PivotTable when you group them (318695)

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:

1. 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("J1") '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
   					

2. Remove any list entries for fields that you want to keep in the PivotTable.
3. 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