Use the SubTotal method as a function in data transformation formula expressions.SubTotal(Type: PivotEvaluatorElementType;
[Relation: Variant = Null;]
[Position: PivotTotalPosition = 0]): Variant;
Type. Subtotals calculation method.
Relation. The dimension element, by which the calculated subtotal must be calculated.
Position. Totals position (by rows or by columns).
The SubTotal method returns subtotal value, that is, value of dimension element total by above dimension element.
The method is used in transformation formulas of analytical data area calculated elements. Only name of the SubTotal method without the PivotClass class is specified in formula equations. Calling of the method from macros in Fore is not supported.
Use the SubTotal method as a function in data transformation formula expressions.
The mandatory requirement for method working is enabling calculation of required totals for table rows or columns. If totals are not calculated, the method returns empty values.
The Relation parameter is optional. If the parameter is not specified, the method returns subtotal of the element, after which a calculated element is 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 method 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.
The Position parameter is optional. If the parameter is not specified, or the value matches the location of created element, the method returns total of the element, for which transformation formula is set. If the value is specified that is opposite to calculated element location (calculated element is located in columns, and the PivotTotalPosition.Rows value is specified, and vice versa), the method returns subtotal value by rows or columns respectively.
To create formulas and expressions, see the Creating Formulas and Expressions article.
The example of calculation element setup is given in IEaxDataAreaSlice.CalcTransformations.
Expression:
SubTotal(PivotEvaluatorElementType.Sum, Null, PivotTotalPosition.Rows);
Result: the subtotal located by rows as a sum of corresponding dimensions located above the calculated element.
Before:
After:
Application: it can be used in calculated element formulas.
See also: