expression.AddCalculatedTotal(Name, Caption, Expression, SolveOrder)
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