web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Consolidated GL Trial ...
Microsoft Dynamics GP (Archived)

Consolidated GL Trial Balance

(0) ShareShare
ReportReport
Posted on by Frank Hamelly | MVP... 46,625 Super User 2025 Season 2

One of my clients has a need for a consolidated trial balance in a very specific format.  They have 9 different companies that consolidate and they want to see account balances for each account from all 9 companies on one report (not in tree format).  Ultimately, they want to be able to export to Excel such that all accounts are in one sheet.

Is there a way to do this in FRx?

Thanks in advance,

*This post is locked for comments

I have the same question (0)
  • Ron Draganowski Profile Picture
    Ron Draganowski 1,575 on at

    Frank, they want to see full GL account detail?  No summarization except totals by account? 

     If the end goal is to get this to Excel, I think you'll have great luck using SmartList Builder (SLB).  With SLB, you can create a new smartlist object based either on the GL20000 table, or if you want to include historical years I would suggest (in GP 10.0) reporting against the AccountTransactions view.  SLB allows you to have your smartlist be multicompany.  Just choose which databases you want to gather together.  Then set the summarization settings to you get totals by account.  Just to be sure of what I was talking about, I did a quick test on this end and it worked perfectly, tied out to the GL across two companies.  (based on the AccountTransactions view)

    Plus, starting with GP 10.0 SP2, you can auto create a linked Excel spreadsheet based on the same SLB query.  Open the SLB object, choose Options>>Duplicate, as an Excel Report Builder object.  Then complete the Excel Report Builder setup, and you'll have an Excel spreadsheet that pulls data across companies.  And, there you go!

     

    Ron Draganowski
    Solution Services Practice Manager
    Olsen Thielen Technologies, Inc.
    St Paul, Minnesota
    rdrag@ottechnologies.com
    http://www.ottechnologies.com

    Find me on LinkedIn: http://www.linkedin.com/in/rondraganowski 

     

  • Suggested answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    In SmartList Builder, create a multi-company SmartList.

    Use calculated fields for each column, picking only the account totals from company 1 in the first column (Case statements!), et cetera.

    Of course add an account number and a totals column

    Then summarize by account number.

    Pooof!

     

    All described in our soon to be released SmartList Builder manual.

  • Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

    Thanks Ron & Richard.  This is working great so far.  I'm grouping on account number to get my debit & credit totals and added a calculated column to net the debits & credits for the account balances.

    One last question - what's the easiest way to add the Company ID to the transactions?  I haven't been able to locate a GL field that I can link to the Company Master.

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    I always put a segment in the GL number.....

  • Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

    I ended up using SmartList Builder, created a multi-company view in SQL and hard-coded the company IDs into the view.  it works great.

    Thanks again for your help, gentlemen.

  • vsquared Profile Picture
    vsquared 105 on at

    If you're a real stickler about hard coding things you can always utilize the Company Location Master and the Company Master tables.  It sounds like you're using a SQL View.  The tables you'll want to access are [DBID]..SY00600 and DYNAMICS..SY01500.  You can get the Company Name by doing a join on the Company ID field.  In the Company Location Master (SY00600) you only need one of the rows since the company id is the same on every line.

     Good luck.

  • Chad Hundley Profile Picture
    Chad Hundley 50 on at

    I have been trying to create a Smartlist that links Payables to the GL.  I have this part working okay, but I need to add the Company ID or Company name to the Smartlist using SLB, but i recieve duplicate lines when trying to link the tables together.  Does anyone know how I can accomplish this?

    Thank you.

  • Suggested answer
    Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

    Chad, check out Victoria Yudin's blog - victoriayudin.com/.../payables-sql-views.  You may just find a SQL view that will help you.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans