The query language allows you to use custom expressions for calculating totals by groupings. This article covers some specifics of using custom expressions in total fields and provides usage examples.
Custom expressions in totals
The syntax and functionality of expression for calculating totals are both similar to a regular query language expression though there are some differences:
- in total calculation expressions you cannot use the IN and IN HIERARCHY operations with nested queries;
- in total calculation expressions the result of Presentation() function can be used in another expression.
If a custom expression is used to calculate totals, you have to specify the name of the field that will store the calculation result. To do this, add a field alias from the query selection list after the description of the total expression (after the optional AS keyword).
TOTALS 100 * Sum(Profit) / SUM(Turnover) AS ProfitPercentage
In this example the expression result is stored to the ProfitPercentage field. Note that the ProfitPercentage field must be present in the query selection list.
All the fields used in the total calculation expressions must be present in the query selection list. For the field names, you can use the aliases of the selection list fields.
Only those fields by which totals are calculated can be used in the expressions for calculating totals outside of aggregate functions.
TOTALS Items AS Items, // This is correct because the field is present in the list of total groupings AmountTurnover AS AmountTurnover, // This is incorrect because the field is absent from the list of total groupings SUM(QuantityTurnover) AS QuantityTurnover // This is correct because the field is used in an aggregate function BY Items, Counterparty
If a grouping field is used in a total expression and this field has not been used to get a grouping, the field value is NULL.
Calculating expressions based on aggregate functions
To calculate expressions based on aggregate functions, it is sufficient to provide an expression that uses aggregate functions.
SELECT Items, AmountTurnover AS Turnover, AmountProfit AS Profit, 100 * AmountProfit / AmountTurnover AS ProfitPercentage FROM AccumulationRegister.ProfitAccounting.Turnovers TOTALS SUM(Turnover), SUM(Profit), 100 * SUM(Profit) / SUM(Turnover) AS ProfitPercentage BY Items HIERARCHY
In this example the ProfitPercentage field of the total records will store the value of the sum by the Profit field multiplied by 100 and divided by the value of the sum by the Turnover field.
Calculating totals only for retrieved groupings
To calculate values of some total fields only for groupings whose totals are already retrieved, you can use the fact that the grouping field values are NULL for groupings whose totals are not yet retrieved.
SELECT PrimaryBalance.Account AS Account, PrimaryBalance.Currency AS Currency, PrimaryBalance.AmountBalance AS AmountBalance, PrimaryBalance.CurrencyAmountBalance AS CurrencyAmountBalance FROM AccountingRegister.Primary.Balance AS PrimaryBalance TOTALS SUM(AmountBalance), CASE WHEN Currency IS NOT NULL THEN SUM(CurrencyAmountBalance) END AS CurrencyAmountBalance BY Account, Currency
In this example the total values for the CurrencyAmountBalance field are only calculated for totals that have the Currency grouping retrieved. So, totals for the Account grouping by CurrencyAmountBalance field are only calculated when the grouping totals are retrieved only inside the Currency grouping.
Another way to calculate totals is by checking the number of different values in a grouping and calculating the totals only for the total records that are calculated for a single grouping value.
SELECT PrimaryBalance.Account AS Account, PrimaryBalance.Currency AS Currency, PrimaryBalance.AmountBalance AS AmountBalance, PrimaryBalance.CurrencySumBalance AS CurrencySumBalance FROM AccountingRegister.Primary.Balance AS PrimaryBalance TOTALS SUM(AmountBalance), CASE WHEN COUNT(DISTINCT Currency) = 1 THEN SUM(CurrencyAmountBalance) END AS CurrencyAmountBalance BY Account, Currency
In this example, the calculation of totals for the CurrencyAmountBalance field is performed for those total records that include a single value of the Currency field.
Redefining grouping point presentations
If a field is a presentation of a grouping field, redefining the expression for this field leads to displaying the total expression result as a field presentation. This feature allows you to redefine presentations for groupings.
SELECT SalesTurnovers.Items AS Items, SalesTurnovers.Items.Code AS ItemCode, PRESENTATION(SalesTurnovers.Items) AS ItemPresentation, SalesTurnovers.QuantityTurnover AS QuantityTurnover, SalesTurnovers.AmountTurnover AS AmountTurnover FROM AccumulationRegister.Sales.Turnovers AS SalesTurnovers TOTALS ItemCode + " " + ItemPresentation AS ItemPresentation, SUM(QuantityTurnover), SUM(AmountTurnover) BY Items
In this example the presentation of the Items field in the total records is an expression containing code and the default presentation.Next page: Using filters in queries with virtual tables