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
Chad, check out Victoria Yudin's blog - victoriayudin.com/.../payables-sql-views. You may just find a SQL view that will help you.
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.
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.
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.
I always put a segment in the GL number.....
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.
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, 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
#1
André Arnaud de Cal...
293,202
Super User 2025 Season 1
#2
Martin Dráb
231,923
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
Share
Report
All responses (
Answers (
