AddCalculatedTotal Method

Adds a calculated total to a PivotTable. Use the AddCalculatedTotal method to create a custom total based on totals already defined in the PivotTable. The calculated total is returned as a PivotTotal object.

expression   Required. An expression that returns a PivotView object.

Name  Required String.  Used to identify the new calculated total in the PivotTotals collection. This parameter must be unique within the PivotTotals collection. Must be between 1 and 50 characters in length.

Caption  Required String.  Used to identify the new calculated total in the PivotTable user interface.

Expression  Required String.  The expression used to calculate the new calculated total. Must be a valid multidimensional expression (MDX) statement for the OLE DB provider that is being used to access the data.

SolveOrder  Optional Long.  Indicates the solve order of the new calculated total when the PivotTable is refreshed. The SolveOrder parameter is useful if you create a calculated total that is dependent on calculated totals that were created earlier.

Example

The following example adds a calculated total named "Sales in Last Period" to a PivotTable named "PivotTable1."  The new calculated total displays the sales in the previous time period. The example utilizes the FoodMart OLAP cube that is installed with Microsoft SQL Server 7.0 OLAP Services.

Sub TestAddCalculatedTotal()

   Dim strExp
   Dim totCalcTotal
   Dim vwView

   Set vwView = PivotTable1.ActiveView

   ' The MDX expression used for the new calculated total.
   strExp = "([Measures].[Store Sales], Time.PrevMember)"

   ' Create the new calculated total.
   Set totCalcTotal = vwView.AddCalculatedTotal  _
                   ("Sales in Last Period", "Sales in Last Period", strExp)

   ' Insert the calculated total in the data area of the PivotTable.
   vwView.DataAxis.InsertTotal totCalcTotal

End Sub