July 9, 2014 
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):
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.
Victoria Yudin 
Hi Victoria!
This is amazing! thank you!!! Is there anyway this view could also pull in each account segment individually? such as utilizing the fields ACTNUMBR_1, ACTNUMBR_2, etc. thats in the GL11110 or GL00105?
I’ve tried modifying this but cant get it to work…Thanks so much!
LikeLike
Hi Kimberly,
Try adding them to the SELECT list and then also to the GROUP BY list at the end. So for example, if you add g.ACTNUMBR_1 to the select list, also add g.ACTNUMBR_1 to the group by list. Let me know if that does not work for you.
-Victoria
LikeLike
Hi Victoria,
I have tried modifying this view to pull the balances as at a particular period when once I restrict the periodid then no data is returned. How can I do this ?
LikeLike
Hi Victoria,
I figured it out – I should have put <= the periodid.
Thanks
LikeLike
Hi Victoria,
I was wondering if you had any idea why the beginning balance I’m getting through this SQL Script is different from the beginning balance I get when running a trial balance summary?
Thanks so much!
LikeLike
Hi Adrianna,
This could be caused by something in your data being different from what the code is expecting. I’ve used this same code with at least a dozen different clients, so I know the code works. But I did have one situation where there something weird in their data for Retained Earnings and we had to hard code some exceptions to make the report work. It is different for all accounts or just one?
-Victoria
LikeLike
What is the difference from using PERDBLNC and the difference of DEBITAMT-g.CRDTAMNT?
LikeLike
Hi Ruben,
Looking at all our live data, there is no difference. However when I look at the data in the sample company that’s probably what made me write the code this way. If you run the script below against the TWO company database, there will be lots of results, at least in my version of it:
select * from GL11110 where PERDBLNC <> DEBITAMT – CRDTAMNT
-Victoria
LikeLike
Hi Victoria,
Where is balance brought forward (BBF) tracked in the database? If you know, can you point me to the relevant tables/fields?
Thanks,
Zak
LikeLike
Hi Zak,
It’s not stored in any one field. If you’re looking at the transaction tables (GL20000 and GL30000) the BBF could be comprised on multiple transactions for one account. Look for SOURCDOC = ‘BBF’. Note that the TRXDATE will be the prior year and the OPENYEAR or HSTYEAR will be the year you want. So for example, the 2018 BBF will have OPENYEAR = 2018 and TRXDATE = 12/31/2017 (if you’re on a calendar year).
If you’re looking at the summary tables GL10110 and GL10111, period 0 holds the BBF.
Hope that helps,
-Victoria
LikeLike
Victoria,
I’ll start this with thank you for everything you do on this blog, it has been more helpful to me than I can express.
I’m currently working on a project to automate some excel financial reports and am trying to write a query that shows the trial balance but, with all accounts all the time. In GL11110 it only shows accounts that have been posted to in that year. One work around I considered was hitting every account as a debit and a credit for $0.01 at the beginning of the year so it shows up in GL11110 but, was hoping you could offer any guidance on a cleaner solution.
LikeLike
Hi Chris,
I would recommend a different starting point for your code: start with the GL00100 or GL00105 table. Those will have ALL accounts. Then link to the GL11110 using a left outer join.
-Victoria
LikeLike
You are awesome for all the info, SQL queries, etc you have. I have been using this all very frequently.
Do you know what tables the Summary Trial Balance GP Window inside GP 2015 accesses?
Some users made errors in posting on the correct date, so I had to modify many GLPOSTDT, POSTEDDT, columns to correct the dates. Everything looks good, pretty much various tables were modded including SOP30200, IV tables, GL20000, a few other tables. What would I need to modify to correct what totals the trial balance pulls?
LikeLike
Hi Alex,
Thank you for your kind words.
For the record, I would not recommend changing transaction dates in SQL. There are so many linked summary tables and other transaction tables that are probably now incorrect, it is impossible to predict the repercussions to your data integrity. You may think that you got everything, then something else will crop up in a few months that will be a result of this. If it is possible, I would actually recommend reversing whatever changes you made in the tables and finding a way inside the UI to correct whatever errors were made.
That said, to answer your question – again, I would not look to fix summaries directly in SQL. There is a Reconcile utility for the GL (Microsoft Dynamics GP | Tools | Utilities | Financial | Reconcile) that should update the summaries based on the new transaction dates.
A few other notes: POSTEDDT has no impact on any financial details or summaries, it is actually the ‘real’ date something was posted. You should not change those, they keep track of when something was actually posted and are helpful to have when researching things in the future. You should run Check Links and Reconcile on ALL modules after doing something like this (after a full SQL backup) to make sure nothing else got broken.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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:
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
LikeLike
Got it! Thanks so much for your help. It is much appreciated!
May
LikeLike
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
LikeLike
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
LikeLike
This is great. Thanks Victoria!
LikeLike
Is there a simple overall way to have every amount formatted with 2 decimals and commas?
LikeLike
Wade,
That is typically done at the reporting level, not in SQL directly.
-Victoria
LikeLike