Trial Balance Detail Report is out of balance in General Ledger using Microsoft Dynamics GP

Follow the steps below to locate unbalanced journal entries:

Step 1: Print the Trial Balance report

1. Open the Trial Balance Report Options window for the Detailed Trial Balance Report by selecting the report option and click the Modify button
2. In the Include section, make sure the checkbox for Unit Accounts is not marked.
3. In the Include section, make sure the checkbox for Inactive Accounts is marked.
4. Print the report again.
5. Verify that the Detailed Trial Balance Report is still unbalanced.

Step 2: Identify unbalanced General Ledger transaction(s)

To identify unbalanced GL transaction(s), use one of the options below. To run a script that will identify the GL transaction for you, go to the Option 1: "Fix it for me" section. To run a script yourself that will identify the GL transaction, go to the Option #2: "Let me fix it myself" section.

Option #1: Fix it for me

To have us run a script that will identify (not 'fix') the GL transaction automatically, click the Fix this problem button or link. Click Run in the File Download dialog box, and then follow the steps in the Fix it wizard.

Note This wizard may be in English only; however, the automatic fix also works for other language versions of Windows.

Note If you are not on the computer that has the problem, save the Fix it solution to a flash drive or a CD and then run it on the computer that has the problem.

Option #2: Let me fix it myself

To identify (not 'fix') the GL transaction yourself, follow these steps:

Method #1: Use SQL scripts

1. Use the appropriate step below to open SQL Server Management Studio:
   • If you use SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.
   • If you use Microsoft SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
   • If you use Microsoft SQL Server 2008 or R2, start SQL Server Management Studio. To do this, click Start, point to Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. Copy the below script in to a query window and execute against the company database to identify unbalanced journal entries.

   select JRNENTRY, sum(DEBITAMT) - sum(CRDTAMNT) from GL20000, GL00100 where ACCTTYPE = 1 group by JRNENTRY having sum(DEBITAMT) - sum(CRDTAMNT) <> 0

Method #2: Use the automated solution for "General Ledger Out of Balance" to find the unbalanced journal entries. This solution can be found using the link below to KB 935384.

KB 935384 - Automated Solutions that are available for the Financial series in Microsoft Dynamics GP
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;935384

Step 3: Fix

Once you have identified the unbalanced journal entry, the course of action will vary depending on if it is in a current open year, or in a historical year as follows: 

• If the unbalanced journal entry is in a current open year, you can delete the entry directly from the GL20000 table, and reconcile the year in the front-end (to get the GL summary tables updated). Then rekey the journal entry correctly.
• If the unbalanced journal entry is in a historical year, you will need to reopen the GL year first. If you are on Microsoft Dynamics GP 2013 R2 or later versions, you can do this yourself using the 'Reverse Historical Year' checkbox in the Year-End Closing routine window. However, if you are Microsoft Dynamics GP 2013 SP2 or a prior version, you must contact your Partner to start a consulting service to have the GL year reopened for you via SQL Scripting. Once the year is reopened, you can delete the entry directly from the GL20000 table, reconcile the year in the front-end, rekey the entry correctly, and then reclose the year again.