# Dynamics GP Trial Balance SQL Query Generator

> Source: https://netray.co/tools/gp-sql-trial-balance-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Build a read-only trial balance by account for a chosen fiscal year and period from the GP summary tables.

## Category

microsoft-dynamics

## Erp

Microsoft Dynamics GP

## Audience

- Controller
- GP report writer
- Migration consultant

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Company database

**Key:** company_db

**Type:** text

**Default:** TWO

#### Fiscal year

**Key:** fiscal_year

**Type:** number

**Default:** 2026

#### Through period

**Key:** through_period

**Type:** number

**Default:** 9

**Help:** Period 0 holds the opening balance for the year.

**Blocks:**

#### Trial balance through a period

**Body:** USE [{{company_db}}];
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 = {{fiscal_year}}
  AND b.PERIODID BETWEEN 0 AND {{through_period}}
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

**Body:** USE [{{company_db}}];
GO

SELECT SUM(b.PERDBLNC) AS should_be_zero
FROM dbo.GL10110 b
WHERE b.YEAR1 = {{fiscal_year}}
  AND b.PERIODID BETWEEN 0 AND {{through_period}};

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Why start from period 0?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

dynamics gp trial balance sql, GL10110 query, gp general ledger balances sql, gp account balance by period, gp opening balance extract, dynamics gp gl sql report

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json