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.

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

  1. This is very helpful! Though I’ve used SQL Server for many years, I’m new to GP. How would you modify this query to return the month end balances for the Current fiscal year? I’m thinking perhaps a join to a table that holds the current fiscal year, assuming there is such a thing?

    Like

    • Hi May,

      If the current fiscal year is the only (or the first) year open in GP, then you don’t need to modify anything, this will work as is.

      If you have multiple open years, this gets more complicated, as you would need to calculate the P&L account balances separately and also add past year P&L totals to Retained Earnings balances. If you have one Retained Earnings account, it might not be so bad, but if you close to divisional Retained Earnings accounts, that adds another complication. In either case, the code may need to be specific to your data and is beyond what I would be posting online.

      If this is something that you need and are looking for help on it, we offer consulting services for reporting help, please let me know if you are interested in that.

      -Victoria

      Like

      • Thanks, Victoria. The current year is not the only one open (there are 3) and your explanation helps immensely to clear up my total confusion! At this point, I’m only interested in the Deferred Revenue and Revenue accounts, so I think I can figure out how to get the final balance without having to muck about with the Retained Earnings. Can I safely assume that I would need to sum all years & months, plus the opening balance, up to and including the final month I’m after? Or is there a simpler way? I was thinking along the lines of concatenating the year and period and summing everything <= the current period on an account basis.

        Anyway, I will give it a go when I'm back in the office on Wednesday.

        Thanks for your help!

        May

        Like

        • Hi May,

          Deferred Revenue and Revenue are typically P&L accounts. In that case, you would not want to add up all the open years, as P&L accounts get ‘reset’ every year and start at 0. So, for example, if you have 2012, 2013 and 2014 years open right now…Deferred Revenue and Revenue for 2014 would be calculated by adding all the activity for 2014 only. Ignoring 2012 and 2013. If you were looking for an account like Accounts Receivable, which is a Balance Sheet account, then you would need to add up the beginning balance and all the activity through and including 2014 to get the balance.

          To make sure what you’re calculating is what your users need, I would recommend having a conversation with them and confirming whether the accounts they need are P&L or Balance Sheet accounts.

          -Victoria

          Like

          • Revenue is a P&L account, but Deferred Revenue is a liability since it is unearned revenue.

            So I assume from your comments that the process of closing a year removes that year from the Open table, updates the balance forward for each Balance Sheet account to the ending balance of the newly closed year and closes the P&L accounts to Retained Earnings. Is that correct?

            May

            Like

            • May,

              You are right of course, I was thinking Accrued Revenue, not Deferred.

              You are also correct about the closing a year in GP, just to rephrase a bit, the process:

              • Moves the ending balance for all Balance Sheet accounts to the beginning balance of the next year
              • Moves the sum of the P&L accounts into the Retained Earnings beginning balance of the next year

              At the table level, all transactions for the year being closed move from the GL20000 to the GL30000 table (if you are keeping history, which everyone should be). The entry for the beginning balances of the next year is created in the GL20000 table. A similar process happens in the summary tables (GL10110 to GL10111).

              -Victoria

              Like

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

  3. This is great. Thanks Victoria!

    Like

  4. 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,640 other followers

%d bloggers like this: