mechanism

SQL view for Dynamics GP open year GL trial balance


General Ledger trial balances have been a popular request lately. The view below will return a General Ledger trial balance showing the monthly net change for the first open year in your Dynamics GP. I’ve hard-coded month names to be the calendar year, if you are on a different fiscal year, you will need to update these.

It’s been suggested to me that it would be helpful to see a sample of the results when I post code for views. If you click on the picture below, you’ll see a bigger version:

GL Trial Balance results

Some additional resources:

create view view_Open_Yr_GL_Trial_Balance
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created March 17, 2014 by Victoria Yudin
--     Flexible Solutions, Inc.
-- For updates please see http://wp.me/pkuMh-1hx
-- 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 g.PERIODID when 1
	then g.DEBITAMT-g.CRDTAMNT else 0 end) January,
sum(case g.PERIODID when 2
	then g.DEBITAMT-g.CRDTAMNT else 0 end) February,
sum(case g.PERIODID when 3
	then g.DEBITAMT-g.CRDTAMNT else 0 end) March,
sum(case g.PERIODID when 4
	then g.DEBITAMT-g.CRDTAMNT else 0 end) April,
sum(case g.PERIODID when 5
	then g.DEBITAMT-g.CRDTAMNT else 0 end) May,
sum(case g.PERIODID when 6
	then g.DEBITAMT-g.CRDTAMNT else 0 end) June,
sum(case g.PERIODID when 7
	then g.DEBITAMT-g.CRDTAMNT else 0 end) July,
sum(case g.PERIODID when 8
	then g.DEBITAMT-g.CRDTAMNT else 0 end) August,
sum(case g.PERIODID when 9
	then g.DEBITAMT-g.CRDTAMNT else 0 end) September,
sum(case g.PERIODID when 10
	then g.DEBITAMT-g.CRDTAMNT else 0 end) October,
sum(case g.PERIODID when 11
	then g.DEBITAMT-g.CRDTAMNT else 0 end) November,
sum(case g.PERIODID when 12
	then g.DEBITAMT-g.CRDTAMNT else 0 end) December,
sum(g.PERDBLNC) Ending_Balance

from GL11110 g  --GL

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_Trial_Balance 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.

14 Responses to “SQL view for Dynamics GP open year GL trial balance”

  1. Hi Victoria,
    This is a great report, but my boss wants the actual period balance by month instead of the net change. I tried using the perdblnc field but it really isn’t the period balance, it is actually the net change field. Then I tried using the perdblance (net change) and add that back to the previous month in my own tweaked version and it created the view, but when I try and select from it I get ‘error converting data type varchar to numeric’. I can’t seem to figure out why that’s happening. Here’s how I changed what you wrote. I’m obviously doing something wrong. I’d appreciate it if you could help me figure out how to get period balances by month.

    Thanks,Laura

    CREATE view [law_Open_Yr_GL_nc_pb]
    as

    — ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
    — Created March 17, 2014 by Victoria Yudin
    — Flexible Solutions, Inc.
    — For updates please see http://wp.me/pkuMh-1hx
    — Only returns the first open year in GP
    — Month names in columns use calendar fiscal year
    — modified to try and get real period balance – LAW 7/7/2014
    — ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

    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_nc,
    sum(case g.PERIODID when 1
    then g.PERDBLNC else 0 end)January_nc,
    sum(case g.PERIODID when 2
    then g.PERDBLNc else 0 end) February_nc,
    sum(case g.PERIODID when 3
    then g.PERDBLNC else 0 end) March_nc,
    sum(case g.PERIODID when 4
    then g.PERDBLNC else 0 end) April_nc,
    sum(case g.PERIODID when 5
    then g.PERDBLNC else 0 end) May_nc,
    sum(case g.PERIODID when 6
    then g.PERDBLNC else 0 end) June_nc,
    sum(case g.PERIODID when 7
    then g.PERDBLNC else 0 end) July_nc,
    sum(case g.PERIODID when 8
    then g.PERDBLNC else 0 end) August_nc,
    sum(case g.PERIODID when 9
    then g.PERDBLNC else 0 end) September_nc,
    sum(case g.PERIODID when 10
    then g.PERDBLNC else 0 end) October_nc,
    sum(case g.PERIODID when 11
    then g.PERDBLNC else 0 end) November_nc,
    sum(case g.PERIODID when 12
    then g.PERDBLNC else 0 end) December_nc,
    sum(g.PERDBLNC) Ending_nc,

    sum(case g.PERIODID when 1
    then (g.PERDBLNC + ‘Beginning_nc’) else 0 end)January,
    sum(case g.PERIODID when 2
    then (g.PERDBLNC + ‘January_nc’) else 0 end) February,
    sum(case g.PERIODID when 3
    then (g.PERDBLNC + ‘February_nc’) else 0 end) March,
    sum(case g.PERIODID when 4
    then (g.PERDBLNC + ‘March_nc’) else 0 end) April,
    sum(case g.PERIODID when 5
    then (g.PERDBLNC + ‘April_nc’) else 0 end) May,
    sum(case g.PERIODID when 6
    then (g.PERDBLNC + ‘May_nc’) else 0 end) June,
    sum(case g.PERIODID when 7
    then (g.PERDBLNC + ‘June_nc’) else 0 end) July,
    sum(case g.PERIODID when 8
    then (g.PERDBLNC + ‘July_nc’) else 0 end) August,
    sum(case g.PERIODID when 9
    then (g.PERDBLNC + ‘August_nc’) else 0 end) September,
    sum(case g.PERIODID when 10
    then (g.PERDBLNC + ‘September_nc’) else 0 end) October,
    sum(case g.PERIODID when 11
    then (g.PERDBLNC + ‘October_nc’) else 0 end) November,
    sum(case g.PERIODID when 12
    then (g.PERDBLNC + ‘Nobember_nc’) else 0 end) December
    from GL11110 g –GL

    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


    It creates the view, then when I say
    select * from [law_Open_Yr_GL_nc_pb]

    I get:

    Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to numeric.

    Like

  2. I tried running this in Smartlist builder to see if would return the results I want and when I preview it says Invalid script for both options, I’m not a sql person, so I don’t know how to fix.

    Like

  3. As usual, nice stuff, thanks! After looking at it, I thought, “This sure looks like something for a PIVOT query”. I haven’t done many of them so I thought it would be fun to give it a whirl. Here ya go! It produces the same output as your, just differently. IMHO, I think the monthly totals are calculated easier by using the PIVOT, but the ending balances are worse in my version.

    Oh yeah, our fiscal year starts on July 1, so my months are labeled differently than yours.

    Enjoy

    John

    SELECT Year,
    Account,
    Description,
    Category,
    [0] AS Previous,
    [1] AS Jul,
    [2] AS Aug,
    [3] AS Sep,
    [4] AS Oct,
    [5] AS Nov,
    [6] AS Dec,
    [7] AS Jan,
    [8] AS Feb,
    [9] AS Mar,
    [10] AS Apr,
    [11] AS May,
    [12] AS Jun,
    Ending.Ending_Balance
    FROM (
    SELECT GL11110.YEAR1 AS Year,
    GL11110.PERIODID,
    GL11110.ACTINDX,
    GL00105.ACTNUMST AS Account,
    GL11110.ACTDESCR AS Description,
    GL00102.ACCATDSC AS Category,
    DEBITAMT – CRDTAMNT AS Diff
    FROM GL11110 –GL
    JOIN GL00102 ON GL11110.ACCATNUM = GL00102.ACCATNUM –categories
    JOIN GL00105 ON GL11110.ACTINDX = GL00105.ACTINDX –for account number
    WHERE GL11110.ACCTTYPE = 1
    AND GL11110.YEAR1 = (SELECT MIN(YEAR1) FROM GL11110)
    ) AS p
    PIVOT
    (
    SUM(Diff)
    FOR PERIODID IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
    ) AS PivotCreditsAndDebits
    LEFT JOIN (
    SELECT YEAR1,
    ACTINDX,
    ISNULL(SUM(PERDBLNC),0) AS Ending_Balance
    FROM GL11110
    GROUP BY YEAR1,
    ACTINDX
    ) AS Ending ON Ending.YEAR1 = PivotCreditsAndDebits.Year
    AND Ending.ACTINDX = PivotCreditsAndDebits.ACTINDX
    ORDER BY Year,
    Description,
    Account,
    Category

    Liked by 1 person

  4. lonnie.nelson@watchguard.com Reply March 19, 2014 at 12:44 pm

    I like this view but think I may have some bad data in my GL10110 table. Should there be anything from closed years in there? I have records from year 0, year 2008 – 2012 and then the ones I expected which are years 2014 – 2018. Should there be any records for historical years in this table? Of the 9046 rows in the table, 496 are historical years. I changed the script to hardcode 2014 as if I run the script as is it tries to use the records from year 0 in my data.

    Thanks!

    Like

    • Hi Lonnie,

      There should not be anything from closed years or with year 0 in the GL10110 table. Have you tried running check links and reconcile to see if that clears it up? If not, you may need to talk to your GP Partner or Dynamics GP Support for some more specific help with this.

      In the meantime, it sounds like you already implemented what my suggestion would be, which is to hardcode the year you want. The only issue with doing that will be that your beginning and ending balances for Balance Sheet accounts will not be correct – but all the monthly net change columns should be fine.

      -Victoria

      Like

  5. Works like a charm! Thanks for sharing!

    Like

Trackbacks/Pingbacks

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

    […] 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 […]

    Like

  2. Some things that I ran across today - Microsoft Dynamics GP DBA - Microsoft Dynamics GP - Microsoft Dynamics Community - April 21, 2014

    […] SQL view for Dynamics GP open year GL trial balance […]

    Like

  3. SQL view for Dynamics GP open year GL trial balance | Victoria Yudin | DynamicAccounting.net - March 19, 2014

    […] Victoria shows off her SQL view for Dynamics GP open year GL trial balance. […]

    Like

  4. SQL view for Dynamics GP open year GL trial balance – 3/17, Victoria Yudin | - March 17, 2014

    […] Continue reading on Source Blog […]

    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: