AddCalculatedTotal Method

Microsoft Office Web Components Visual Basic

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