GL Trial balance via sql query

[Michaelny...@discussions.microsoft.com]

Hi everybody -
I need to be able to create a trial balance via a sql query. I know GP
already has this report built in, but I need to make it via a query. Your
help is appreciated.

[Dencio]

Hi Michael,

You can start creating your query with the following company tables, which
will provide you the following:

GL00100 - Account Master
GL20000 - Open Year Transaction Details
GL30000 - Historical Year transaction Details
GL00105 - Account number string, an alternative from conbining account
segments in GL00100

The following script is a very simple SQL query that is functionally TB
Summary as at run date (one open fiscal year):

select GL20000.actindx as [ACCOUNT INDEX],GL00105.actnumst as [ACCOUNT],
sum(GL20000.DEBITAMT) as [DEBIT AMOUNT], sum(GL20000.CRDTAMNT) as [CREDIT
AMOUNT]
from GL20000
inner join GL00105
on GL20000.actindx = GL00105.actindx
group by GL20000.ACTINDX, GL00105.actnumst
ORDER BY GL20000.ACTINDX

You can put more fields and apply additional restrictions to meet your
specific requirements.

Hope this helps!

Cheers,

Dennis Araullo, MACS
Microsoft Certified Technology Specialist GP,CRM,AX
MCITP Installation and Configuration AX 2009
MCITP Database Administrator SQL 2008
Citrix Certified Enterprise Administrator

[Michaelny...@discussions.microsoft.com]

Thanks for the excellent help. How can I write that same query with a date
range [between 12/1/2009 to 12/31/2009 ?

[Michaelny...@discussions.microsoft.com]

What I really need is the ability to specify a 'posted' date range

[Frank Hamelly, MCP-GP, MCT, MVP]

Have you thought about using FRx for your trial balance? Much easier
than using a SQL query.

Frank Hamelly
MCP-GP, MCT, MVP
East Coast Dynamics
www.eastcoast-dynamics.com

get your gptip42today at www.gp2themax.blogspot.com

[Jim Hummer]

On Jan 4, 1:37 pm, Mich...@nyresume.com

> > "Mich...@nyresume.com" wrote:
>
> > > Hi everybody -
> > > I need to be able to create a trial balance via a sql query. I know GP
> > > already has this report built in, but I need to make it via a query. Your

> > > help is appreciated.- Hide quoted text -
>
> - Show quoted text -

Those dates wouldn't give you a trail balance as they exclude the
BBF. I would use FRx for this function.

Jim

[Leslie Vail]

So are you looking for the detail of the transactions posted in the date
range? What you would be getting is a net change rather than a balace. Does
this better describe what you need?

Do you need the report in Excel? What requires a SQL statement. There are
easier ways to export the trial balance numbers to Excel.

Let us know, we can help.

Kind regards,

Leslie

[Jim Hummer]

On Jan 4, 1:37 pm, Mich...@nyresume.com
<Michaelnyresume...@discussions.microsoft.com> wrote:

> > "Mich...@nyresume.com" wrote:
>
> > > Hi everybody -
> > > I need to be able to create a trial balance via a sql query. I know GP
> > > already has this report built in, but I need to make it via a query. Your

> > > help is appreciated.- Hide quoted text -
>
> - Show quoted text -

Here is a summary level query

Here is another one. Replace Year and Month

select b.ACTNUMST,SUM(a.PERDBLNC) from GL10110 a
LEFT OUTER JOIN GL00105 b on a.ACTINDX = b.ACTINDX
where YEAR1 = 2014 and PERIODID < 8
GROUP BY b.ACTNUMST

Jim