AX 2012 R2 Trial Balance Bug and Fix

This question is answered
Dynamics AX 2012 R2 - Class DimensionFocusUpdateBalance method createBalance Fix.txt

There is a bug in Dynamics AX 2012 R2 regarding the Trial Balance where under the right conditions the total will drift out of balance with the underlying transactions.  Typically this will involve debits and credits the same ledger account within the same journal.  Doing a "Rebuild balances" on the affected dimension sets will clear the problem, but it can reoccur quickly.  In our case, the issue occurred multiple times daily until we implemented a fix ourselves.

My personal fix for this is attached.  It is provided for academic purposes only and no warranty is provided.  Use at your own risk.

The code responsible is in Classes\DimensionFocusUpdateBalance\createBalance where recently posted transactions waiting in the DimensionFocusBalanceTmp table are inserted or updated into the DimensionFocusBalance table.

The root cause of the problem is very subtle and revolves around a little known issue with doing a UPDATE in SQL against a 1:n JOIN.  The fix splits the update statement into two statements where debits and credits are applied separately.

The following SQL demonstrates the underlying root cause.  The results are different from what one would intuit at first glance.

 

create table #balances (   ACCOUNT nvarchar(10),   TOTAL NUMERIC(10,2) )

create table #balancestemp (   ACCOUNT nvarchar(10),   DEBIT NUMERIC(10,2),   CREDIT NUMERIC(10,2) )  

insert into #balances values (N'5000', 200.0)

insert into #balancestemp values (N'5000', 300.0, 0)

insert into #balancestemp values (N'5000', 0, -100.0)  

select * from #balances

select * from #balancestemp  

update b set b.total = b.total + t.debit + t.credit

  from #balances b

  join #balancestemp t on t.account = b.account  

select * from #balances

 

Verified Answer
  • This bug has been fixed in AX 2012 R2 CU 6.

    2820691

    Dimension set balance updates do not handle debits and credits in a single update

    GFM\General Ledger

All Replies
  • Hello Sir Brandon,

    Have you tried to run the hotfixes (KB2742864) to solved that issue.  That hotfixes can be downloaded in partnersource.  Here the lisst applicable for trial balance issue.  Apply only what is applicable in your environment

    .

    KB2738056  (To update Kernel version)

    KB2742864 - Poor performance experienced while running “Rebuild balances” in Financial Dimensions set.

    KB2754985 (Trial Balance Detail & Summary mis match error)

    KB2810622 (The Summary Trial balance report is unbalanced by allocated amount)

    KB2828929 – CU5 KB

    KB2846993 (Detailed trial balance report performance needs improvement and accumulated totals are not including opening balances)

  • Bamboogypsie,

    Unless I'm mistaken, the hotfixes you list appear to be at least in part related to the RTM rather than R2 version of AX 2012 which Brandon is reporting about.

  • This bug has been fixed in AX 2012 R2 CU 6.

    2820691

    Dimension set balance updates do not handle debits and credits in a single update

    GFM\General Ledger