You receive a "A balance on the Transaction Detail Report may be incorrect" error message when you generate a report in Microsoft FRx

Article ID: 862192 - View products that this article applies to.
Microsoft Business Solutions-Axapta 3.0 is now part of Microsoft Dynamics AX 3.0. All references to Microsoft Business Solutions-Axapta and Microsoft Axapta pertain to Microsoft Dynamics AX.
Expand all | Collapse all

TechKnowledge Content

SYMPTOMS

You receive the following error message when you generate a report in Microsoft FRx: FRx Reporting Engine Error:
A balance on the Transaction Detail Report may be incorrect. The Column Layout used with a transaction detail report must contain a Current column and an YTD column. This error could also indicate a problem with the transaction details not matching the Period Balance records in the General Ledger. Account [####-####-###] Transaction Detail Amount [ #####] Account Balance [ #####] Continue printing Out-of-Balance Messages



The following warning message is added to the report directly below the transaction totals referenced in the error message:

"*** CAUTION: THE ABOVE TOTAL DOES NOT MATCH THE ACCOUNT BALANCE RECORD"

You may experience this issue in any one of the following situations:

> You generate a report that includes transaction detail, when the output type is something other than the DrillDown Viewer, such as Printer, Excel (XLS), Excel 5 or above (via OLE).
> You drill to a one page transaction detail level report in the DrillDown Viewer.
> You scroll to the last page of a transaction detail level report in the DrillDown Viewer.

CAUSE

This issue occurs if any one of the following conditions are true:

>The sum of the transactions from the General Ledger does not match the period balance records in the General Ledger.
>The report includes transaction detail and whole dollar rounding is selected in the FRx catalog.
>The report includes transaction detail and the report has more than one YTD column in the column layout.
>The report includes transaction detail and the report spans multiple fiscal years.
>The report includes transaction detail and the report includes more than one book code in the column layout.

RESOLUTION

To resolve this issue, use the method that corresponds with the cause of the issue.

General Ledger period balance records do not match sum of General Ledger transactions

Verify the period balance records and transactions in the General Ledger program or the General Ledger database. If the sum of the transactions does not equal the period balance records in the program or the database, you must contact your General Ledger vendor to correct the issue. After the issue is corrected in the General Ledger, the error message will not occur in FRx.

Report includes whole dollar rounding

Sometimes using whole dollar rounding for reports that include transaction detail can cause the error message because of the way that FRx currently rounds and calculates transactions and totals.

Here is an example of a sample transaction report that does not use whole dollar rounding.

CUR YTD
Opening Balance 12/01/03 1000.13
Sales Journal-GLTRX0044 12/31/03 (100.51)
Payroll Journal-GLTRX0044 12/31/03 (200.51)
Disbursement Journal-GLTRX0044 12/31/03 (300.51)
----------- -----------
Total 1100-1000-000 Cash-Checking (601.53) 398.60
----------- -----------
----------- -----------
Total Cash-Checking (601.53) 398.60
======= ======
Here is an example of the same sample transaction report when whole dollar rounding is used.

CUR YTD
Opening Balance 12/01/03 1000
Sales Journal-GLTRX0044 12/31/03 (101)
Payroll Journal-GLTRX0044 12/31/03 (201)
Disbursement Journal-GLTRX0044 12/31/03 (301)
------- -----
Total 1100-1000-000 Cash-Checking (602) 397
------- -----
*** CAUTION: THE ABOVE TOTAL DOES NOT MATCH THE ACCOUNT BALANCE RECORD
------- ------
Total Cash-Checking (602) 399
==== ====

FRx uses the following process to calculate the amounts to whole dollars.

Note This example uses the amounts from the first sample transaction report to produce the results shown in the second sample transaction report.

1. FRx retrieves the balance for each account for use in the Account Level Detail report.

Current Cash-Checking Balance - (601.53)
Year-To-Date (YTD) Cash-Checking Balance - 398.60

2. FRx rounds the balance for each account for use in the Account Level Detail report

Current Cash-Checking Balance - (601.53) rounds up to (602)
Year-To-Date (YTD) Cash-Checking Balance - 398.60 rounds up to 399

3. The rounded values also appear when you drill to the Transaction Level Detail report and is eventually used for a comparison.

Total Cash-Checking - Current (602), YTD 399

4. At the transaction level, FRx rounds the opening balance, each transaction, and the transaction total.

Opening Balance - 1000.13 rounds down to 1000,
Sales Journal-GLTRX0044 - (100.51) rounds up to (101)
Payroll Journal-GLTRX0044 - (200.51) rounds up to (201)
Disbursement Journal-GLTRX0044 - (300.51) rounds up to (301),
Total Cash-Checking - (601.53) rounds up to (602)

5. FRx adds the rounded opening balance to the rounded transactions to calculate the YTD balance

Year-to-Date Balance -- 1000+ (101) + (201) + (301) = 397

6. FRx compares the totals from the Account Level Detail to the totals from the Transaction Level Detail

Account Level - Total 1100-1000-000 Cash-Checking - Current (602), YTD 397
Transaction Level Detail - Total Cash-Checking - Current (602), YTD 399

7. FRx returns the error message because the Account Level YTD balance totals do not match the Transaction Level totals.

To prevent the out of balance message, use any one of the following methods.

1. Change the detail level of the report to Financial Report, Account Only, or Financial & Account.
2. Do not use whole dollar rounding in the catalog.

Note - FRx is exploring alternative methods of rounding transactions to whole dollar amounts and is considering adding an option that allows the user to turn off this message.

Report includes multiple YTD columns

Transaction reports using multiple YTD columns are not supported. Although the out of balance message may not appear on all transaction reports including multiple YTD columns, this configuration is not guaranteed to produce reliable transaction detail reports, and should not be used.

To prevent the out of balance message, use any one of the following methods.

1. Change the detail level of the report to Financial Report, Account Only, or Financial & Account.

2. Change the additional YTD columns to CUR columns with the period code of 1 TO 12.

3. Change the column layout to include only one YTD column.

Report spans multiple fiscal years

Transaction reports spanning multiple fiscal years are not supported. Although the out of balance message may not appear on all transaction reports spanning multiple fiscal years, this configuration is not guaranteed to produce reliable transaction detail reports, and should not be used.

To prevent the out of balance message, change the detail level of the report to Financial Report, Account Only, or Financial & Account.

This article was TechKnowledge Document ID: 34168

Properties

Article ID: 862192 - Last Review: July 19, 2011 - Revision: 2.0
APPLIES TO
  • Microsoft Business Solutions for Analytics–FRx 6.7 Desktop
  • Microsoft Dynamics GP 9.0
  • Microsoft Business Solutions–Great Plains 8.0
  • Microsoft Business Solutions–Great Plains 7.5
  • Microsoft Dynamics SL 6.5
  • Microsoft Business Solutions–Solomon 6.0
  • Microsoft Dynamics AX 4.0
  • Microsoft Dynamics AX 3.0
  • Microsoft Dynamics NAV 4.0
  • Microsoft Business Solutions–Navision 3.70
  • Microsoft Small Business Financials 9.0
  • Microsoft Business Solutions Small Business Financials North America Edition 8.0
  • Microsoft Business Solutions Small Business Manager 7.5, when used with:
    • Microsoft Business Solutions for Analytics–FRx 6.7 Desktop
    • FRx Financial Reporter 6.5
  • FRx Financial Reporter 6.5
Keywords: 
kbnavisionfrx kbsolomonfrx kbsmallbusinessfinancialsfrx kbgreatplainsfrx kberrmsg kbaxaptafrx kbmbsmigrate KB862192

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com