Microsoft DynamicsFree generatorPDF, Excel, Word exportMicrosoft Dynamics GP

Dynamics GP Trial Balance SQL Query Generator

A trial balance is the first thing to extract and tie out in any ERP change. This generator writes the SQL for a GP trial balance straight from the account summary tables, plus a check query that proves debits and credits net to zero.

Fill in

Company database

Fiscal year

Through period

Period 0 holds the opening balance for the year.

Generated code

Download:

Trial balance through a period

USE [TWO];
GO

SELECT
    RTRIM(i.ACTNUMST) AS account,
    RTRIM(m.ACTDESCR) AS description,
    SUM(b.DEBITAMT) AS total_debits,
    SUM(b.CRDTAMNT) AS total_credits,
    SUM(b.PERDBLNC) AS ending_balance
FROM dbo.GL10110 b
JOIN dbo.GL00105 i ON i.ACTINDX = b.ACTINDX
JOIN dbo.GL00100 m ON m.ACTINDX = b.ACTINDX
WHERE b.YEAR1 = 2026
  AND b.PERIODID BETWEEN 0 AND 9
GROUP BY i.ACTNUMST, m.ACTDESCR
HAVING SUM(b.PERDBLNC) <> 0 OR SUM(b.DEBITAMT) <> 0 OR SUM(b.CRDTAMNT) <> 0
ORDER BY i.ACTNUMST;

Check: the trial balance must net to zero

USE [TWO];
GO

SELECT SUM(b.PERDBLNC) AS should_be_zero
FROM dbo.GL10110 b
WHERE b.YEAR1 = 2026
  AND b.PERIODID BETWEEN 0 AND 9;

GL10110 holds the current open year summary. Closed years are in GL10111, so change the table name for prior years. Profit and loss accounts accumulate period activity here, and balance sheet accounts carry the opening balance in period 0. Compare against the GP Trial Balance Summary report before relying on it.

Need a ledger reconciliation pack?

An optional review of how to tie GP balances to a successor ledger.

Optional. The tool and every export work without this. No spam.

How to use it

Enter the company database, the fiscal year and the last period you want. Run the first query in SSMS for the account listing and the second to confirm it nets to zero. If it does not, you have a data or period issue worth finding before any migration step.

Run for each fiscal year in scope for migration
Save results as the legal-entity opening balance source
Archive the output with the date and database backup name

How the query works

GL10110 stores one row per account, year and period with debit, credit and net period balance. Summing from period 0, the opening balance row, through your chosen period yields the balance at the end of that period. GL00105 translates the internal account index to the formatted account string, and GL00100 supplies the description.

Reconciling to GP

The check query should return zero. Next compare account totals to the GP Trial Balance report for the same period. Differences usually mean unposted batches, a different fiscal calendar, or a year that was already closed and moved to history. Resolve those before you build mapping files.

Common mistakes

Reading GL20000 detail instead of the summary and double counting is common. Forgetting period 0 gives balance sheet accounts that look wrong. Using account numbers as keys across companies fails when segments differ, so map through ACTINDX within a company and by account string between systems.

Frequently Asked Questions

Why start from period 0?

In GP, period 0 stores the opening balance brought forward for balance sheet accounts. If you start at period 1, you lose the opening balance and balance sheet accounts will not tie to the GP report.

How do I run this for a closed year?

Replace GL10110 with GL10111, which holds the summary for historical years. Column names are the same. Keep the year filter so you only get one year.

Does it include unposted transactions?

No. Unposted batches live in the GL10000 work tables and are not in the summary. Post or review them before extracting a final balance.

What if the check does not net to zero?

Look for accounts missing from GL00105, a partial year range, or an out of balance journal posted through a custom integration. Narrow by period to find the first period where the sum is not zero.

If you are moving GP financials to a new ledger, we can help set up repeatable trial balance extraction and reconciliation that you run yourself.

Talk to an Expert Explore More Free Tools

Related Tools

Microsoft Dynamics

More Microsoft Dynamics tools

See all

Back to All Tools