[Scott Shankel]

Hello

Does anyone have a SQL view that shows monthly ending balances for all GL accounts, both open and historical?  The view I have does not show balances for accounts that don’t have activity in that month.  

Thanks in advance for any help,

Scott

——————————
Scott Shankel
Controller
Woodings Industrial Corp.
Mars PA
——————————

[Jo deRuiter]

Hi ?

GP has two views out of the box that can help.  I usually use them in Excel Refreshable Reports, so I can Pivot and get beginning balances in Period 0, etc.

You can start with these and make modifications (JUST DO NOT ALTER THE OUT OF THE BOX VIEW, NAME IT SOMETHING ELSE BEFORE MODIFICATIONS)

——————————
Jo deRuiter, MCP, DCP
“That GP Red Head”
AISLING DYNAMICS CONSULTING, LLC
WEBSITE: https://aislingdynamics.com/
BLOG: https://community.dynamics.com/gp/b/gplife
770-906-4504 (Cell)

——————————
——————————————-

[Blair Christensen]

If ‘s comments aren’t quite what you were looking for, I’d suggest also taking a look to see if any of Victoria Yudin’s views might help.  You can get them here:  https://victoriayudin.com/gp-reports/general-ledger-sql-views/  And if none of that is exactly what you need, you may have to engage a consultant to write some customer SQL for you.

One note: The type of view you are asking about is going to necessarily have to span the entire date of operation, meaning it is likely to be slow and get slower as you add information.  There are some strategies to mitigate this, just wanted you to be aware.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Zubin Gidwani]

Scott,

My understanding of your requirements:
Report 100% of the accounts for all years, ie report an account even if its 0 balance for all periods in a year.

The view Jo referred to is probably what you already have and will not report values if the account had no activity in a period.

I put the attached together for you tonight.
disclaimer: it’s late so you’ll need to test this 

–Started with some of the guts of Victoria Yudin’s script at https://victoriayudin.com/2014/07/09/sql-view-for-dynamics-gp-open-year-gl-trial-balance-with-month-end-balances/
–Swapped back to primary GL10111 (historical balances)  and GL10110 (Open Year Balances) tables
–Added a union of GL10111 and GL10110 tables to fetch all years
–Used left outer join on GL00105 to fetch 100% of the accounts for each year:
–Cross Joined respectively against a the Years found in the open and historical balances tables to ensure Year did not report as null for Accounts which had no balances at all

Intuition makes me think I would have needed a Coalesce to force zero balances, but I believe Victoria’s case statements are doing the equivalent. 

Hope this helps deliver what you need.

Zubin

——————————
Zubin Gidwani
Dynamic Budgets
San Francisco-Bay Area CA
——————————
——————————————-