Open Forum

Expand all | Collapse all

GL Summary SQL View

  • 1.  GL Summary SQL View

    Posted Mar 14, 2019 08:12 AM
    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
    ------------------------------


  • 2.  RE: GL Summary SQL View

    TOP CONTRIBUTOR
    Posted Mar 14, 2019 08:22 AM
    Hi @Scott Shankel

    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)

    ------------------------------



  • 3.  RE: GL Summary SQL View

    TOP CONTRIBUTOR
    Posted Mar 15, 2019 10:25 AM
    If @Jo deRuiter'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
    ------------------------------



  • 4.  RE: GL Summary SQL View

    GPUG ALL STAR
    Posted Mar 18, 2019 05:19 AM
      |   view attached
    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
    ------------------------------

    Attachment(s)

    txt
    MonthlyGLBalances.txt   4K 1 version


If you've found this thread useful, dive deeper into User Group community content by role