SubTotal(Type: PivotEvaluatorElementType,
Relation: Variant,
Position: PivotTotalPosition)
Type. Method of subtotals calculation.
Relation. The dimension element, by which the calculated subtotal must be calculated. Optional parameter. By default parameter is set to Null.
Position. Total position (by rows/by columns). Optional parameter. The PivotTotalPosition.Default_ default value is position is not specified.
It returns the value of subtotal, that is value of dimension element total by above dimension element.
Mandatory condition for method working is enabling of required totals calculation for rows/columns of table. If totals are not calculated, the method returns empty values.
If the Relation parameter is not specified, then function returns the subtotal of the element after which the calculated element was created. As a value of the Relation parameter, it is required to specify element of the dimension, for which a calculated element is created. On specifying the Relation parameter, the function returns a subtotal calculated by the specified element. If there are several dimensions in the heading or sidehead, the subtotal is taken by the last dimension of heading or sidehead.
If the Position parameter is not specified, or the value matches the location of created calculated element, the function returns total of the element, for which transformation formula is set. If the value is specified that is opposite to calculated element location (the calculated element is located by columns, and thePivotTotalPosition.Rowsvalue is specified, and vice versa), the function returns subtotal value by rows/columns respectively.
On using the SubTotal method consider the following:
If the calculated element is located in columns and the function is set with the Column Totals parameter, or the calculated element is located in rows and the function is set with the Row Totals parameter:
If the SubTotal function is set for the current dimension item without specifying the particular item in the formula, and the appropriate total method is calculated for the table, the subtotal is calculated by this item
If a new calculated element was added, and a formula is calculated for it where the total could be taken, value of the SubTotal function by this calculated element will be empty
If the data table heading or sidehead contain several dimensions, the subtotal is calculated by the last dimension in the heading or sidehead
If another element is specified for the calculated element in the formula, the subtotal is calculated by the specified element
If the calculated element is located in columns and the function is set with the Row Totals parameter, or the calculated element is located in rows and the function is set with the Column Totals parameter, value of the calculated element will correspond to value of the subtotal by rows or columns, respectively.
If the subtotal by rows or columns is not calculated, the corresponding function set in the formulas returns empty value. For example, if a formula is set as SubTotal, but the Sum total by columns for the table is not calculated, this function returns empty value.
| Formula | Result | Application |
| = SubTotal(PivotEvaluatorElementType.Sum, Null, PivotTotalPosition.Rows) | The subtotal located by rows as a sum of corresponding dimensions located above the calculated element. | It can be used in formulas of calculated elements. |
See also:
Functions Available in Expression Editor │ Totals │ IPivotClass.SubTotal