target

SQL view for Dynamics GP open year GL trial balance with month end balances


Based on a reader’s request, I have created a variation on my SQL view for Dynamics GP open year GL trial balance. This version will show you the month end balances instead of the net change for each month.

Here is a sample of the results (please click on the image to see it bigger):
month end balances

Some additional resources:

create view view_Open_Yr_GL_TB_Month_End
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created July 9, 2014 by Victoria Yudin
-- Flexible Solutions, Inc.
-- For other code, please visit http://victoriayudin.com
-- Only returns the first open year in GP
-- Month names in columns use calendar fiscal year
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
g.YEAR1 [Year],
a.ACTNUMST Account,
g.ACTDESCR [Description],
c.ACCATDSC Category,
sum(case g.PERIODID when 0
    then g.PERDBLNC else 0 end) Beginning_Balance,
sum(case when g.PERIODID <= 1
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,
sum(case when g.PERIODID <= 2
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,
sum(case when g.PERIODID <= 3
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,
sum(case when g.PERIODID <= 4
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,
sum(case when g.PERIODID <= 5
    then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,
sum(case when g.PERIODID <= 6
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,
sum(case when g.PERIODID <= 7
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,
sum(case when g.PERIODID <= 8
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,
sum(case when g.PERIODID <= 9
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,
sum(case when g.PERIODID <= 10
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,
sum(case when g.PERIODID <= 11
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,
sum(case when g.PERIODID <= 12
    then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

from GL11110 g --GL summary data

inner join GL00102 c --categories
on g.ACCATNUM = c.ACCATNUM

inner join GL00105 a --for account number
on g.ACTINDX = a.ACTINDX

where g.ACCTTYPE = 1
and g.YEAR1 = (select min(YEAR1) from GL11110)

group by g.YEAR1, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

go
grant select on view_Open_Yr_GL_TB_Month_End to DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

9 Responses to “SQL view for Dynamics GP open year GL trial balance with month end balances”

  1. Hello Victoria,
    Love your sql views! I am having a problem with this one though.

    When I attempt to run this against my TWO company I get the following errors:
    Line 19, An expression of non-Boolean type specified in a context where a condition is expected, near ‘;’.
    Line 55, Incorrect syntax near the keyword ‘group’

    I’m using SQL Server 2012 Standard. Any ideas?
    Thanks

    Like

    • Hi Sheila,

      Looks like all my <= signs got replaced with something wacky…sorry about that, it happens once in a while with no rhyme or reason to it. I believe they are all fixed now. Please let me know if you run into any other trouble.

      -Victoria

      Like

  2. This is great. Thanks Victoria!

    Like

  3. Is there a simple overall way to have every amount formatted with 2 decimals and commas?

    Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP open year GL trial balance with month end balances - Microsoft Dynamics GP Community - July 23, 2014

    […] out SQL view for Dynamics GP open year GL trial balance with month end balances by Victoria Yudin Till […]

    Like

  2. SQL view for Dynamics GP open year GL trial balance with month end balances – 7/9, Victoria Yudin | - July 11, 2014

    […] Continue reading on Source Blog […]

    Like

  3. DynamicAccounting.net | SQL view for Dynamics GP open year GL trial balance with month end balances | Victoria YudinSQL view for Dynamics GP open year GL trial balance with month end balances | Victoria Yudin - DynamicAccounting.net - July 9, 2014

    […] Victoria offers up a SQL view for Dynamics GP open year GL trial balance with month end balances […]

    Like

  4. SQL view for Dynamics GP open year GL trial balance with month end balances : Interesting Findings & Knowledge Sharing - July 9, 2014

    […] Link: SQL view for Dynamics GP open year GL trial balance with month end balances […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,566 other followers

%d bloggers like this: