Typical balance calculation issues

This article explains why a report generated using the data composition system might show incorrect balances.

No parent period fields in the query

The issue might occur in the following scenario: a query retrieves the Recorder field but does not retrieve the SecondPeriod field.

Incorrect:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance,
 ItemAccountingBalanceAndTurnovers.QuantityTurnover
FROM
 AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers

For such queries, the system cannot calculate balances correctly.

To fix the issue, add the Period field to the query.

Correct:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.SecondPeriod,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance,
 ItemAccountingBalanceAndTurnovers.QuantityTurnover
FROM
  AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers

No paired balance field in the query

The issue occurs if the query retrieves the opening balance field only or the closing balance field only.

Incorrect:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.SecondPeriod,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance
FROM
  AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers

To fix the issue, retrieve both opening and closing balances in the query.

Correct:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.SecondPeriod,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance
FROM
  AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers

Field roles are not filled

The issue occurs if field roles are filled incorrectly. Fill the roles correctly for period, dimension, and balance fields. Period fields must have continuous numbering, starting from 1. The periods must be sorted by accuracy: shorter periods must have lesser numbers. For example, the LineNumber field adds accuracy to the Recorder field, therefore the number of the LineNumber field must be less than the number of the Recorder field. The number of the DayPeriod field must be less than the number of the YearPeriod field.

Incorrect period role:

In this example the recorder does not have the Period role.

Dimension fields (fields whose balances are calculated) must be marked as dimensions. Fields that are not marked as dimensions are not included in the calculation of totals by balance fields, which can lead to incorrect calculation of balance values. Nondimension fields must not be marked as dimensions because this can also lead to incorrect calculation of balance values.

Incorrect:

In this example the Comment field is marked as a dimension while it is not a dimension. And the Warehouse field is not marked as a dimension while it is a dimension.

The balance fields must have correct balance roles. Paired balance fields must have identical balance field group names. Balance fields that are not paired must have different balance field group names.

Example:

In this example the balance fields lack the balance role and balance group name.

Incorrect processing of dimension attributes

The issues described in this section occur when a query retrieves a dimension attribute.

For example, a register has the Agreement dimension with the Counterparty attribute and a query includes the Agreement.Counterparty field.

In this scenario, two issues might occur:

First, the query retrieves the dimension attribute but does not retrшeve the dimension itself, therefore totals by balance fields can be calculated incorrectly.

Incorrect:

SELECT
   BalanceAndTurnovers.Agreement.Counterparty AS Counterparty,
   BalanceAndTurnovers.AmountOpeningBalance,
   BalanceAndTurnovers.AmountClosingBalance,
   BalanceAndTurnovers.AmountTurnover,
   BalanceAndTurnovers.Recorder,
   BalanceAndTurnovers.SecondPeriod
FROM
   AccumulationRegister.Accounting.BalanceAndTurnovers(, , Auto, , ) AS BalanceAndTurnovers

If query data is retrieved from virtual tables, dimension fields whose attributes are retrieved in the query must be also retrieved in the query.

Correct:

SELECT
   BalanceAndTurnovers.Agreement,
   BalanceAndTurnovers.Agreement.Counterparty AS Counterparty,
   BalanceAndTurnovers.AmountOpeningBalance,
   BalanceAndTurnovers.AmountClosingBalance,
   BalanceAndTurnovers.AmountTurnover,
   BalanceAndTurnovers.Recorder,
   BalanceAndTurnovers.SecondPeriod
FROM
   AccumulationRegister.Accounting.BalanceAndTurnovers(, , Auto, , ) AS BalanceAndTurnovers

Second, a dimension attribute field might not have the parent dimension specified. To fix the issue, specify the parent field in the attribute field role. In the example above, specify the Dimension role for the Counterparty field and specify Agreement as a parent field.

Using virtual table dimensions that are not in the query selection list

The issue occurs if a query uses a virtual table dimension but this field is not in the selection list.

Incorrect:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.SecondPeriod,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance
FROM
 AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers
WHERE ItemAccountingBalanceAndTurnovers.Warehouse IN (&Warehouses)

In this example the virtual table provides balances based on the Warehouse dimension but the data composition system is not aware of that.

To fix the issue, ensure that all virtual table dimensions used outside table parameters are present in the selection list.

Correct:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.SecondPeriod,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance
FROM
 AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers
WHERE ItemAccountingBalanceAndTurnovers.Warehouse IN (&Warehouses)

In this example the Warehouse field is added to the selection list and the data composition system will calculate totals based on this dimension.

Also correct:

SELECT ItemAccountingBalanceAndTurnovers.Recorder, ItemAccountingBalanceAndTurnovers.SecondPeriod, ItemAccountingBalanceAndTurnovers.Item, ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance, ItemAccountingBalanceAndTurnovers.QuantityClosingBalance FROM AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , Warehouse IN (&Warehouses)) AS ItemAccountingBalanceAndTurnovers

In this example the Warehouse field is not used outside virtual table parameters.

Using periodicity other than Auto

The issue occurs when data is retrieved from the BalanceAndTurnovers virtual table with periodicity other than Auto if some of the period fields are not displayed in the report. This is similar to the "No parent period fields in the query" issue described at the beginning of the article.

Example of a query that might provide incorrect balances:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.Period,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance,
 ItemAccountingBalanceAndTurnovers.QuantityTurnover
FROM
 AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Recorder, , ) AS ItemAccountingBalanceAndTurnovers

To fix the issue, use the Auto periodicity in BalanceAndTurnovers virtual tables of customizable data composition system reports. Alternatively, ensure that all of the period fields are displayed in the report.

Query example:

SELECT
 ItemAccountingBalanceAndTurnovers.Recorder,
 ItemAccountingBalanceAndTurnovers.SecondPeriod,
 ItemAccountingBalanceAndTurnovers.Item,
 ItemAccountingBalanceAndTurnovers.Warehouse,
 ItemAccountingBalanceAndTurnovers.QuantityOpeningBalance,
 ItemAccountingBalanceAndTurnovers.QuantityClosingBalance,
 ItemAccountingBalanceAndTurnovers.QuantityTurnover
FROM
 AccumulationRegister.ItemAccounting.BalanceAndTurnovers(, , Auto, , ) AS ItemAccountingBalanceAndTurnovers

Using the Week periodicity together with longer periodicities

The issue occurs not at the data composition schema level but at the composition settings level. For example, it occurs if groupings by both month and week are displayed in the report. Solution: do not use periodicities longer than Week together with the Week periodicity.

Next page: Avoiding deadlocks


Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.