Using custom expressions in query language totals

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).

Example:

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.

Example:

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.

Usage examples

Calculating expressions based on aggregate functions

To calculate expressions based on aggregate functions, it is sufficient to provide an expression that uses aggregate functions.

Example:

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.

Example:

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.

Example:

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.

Example:

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.