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
