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
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)
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.
Dimension set balance updates do not handle debits and credits in a single update