Page 1 of 1

Building Chart of Accounts report

Posted: 15 Jun 2012, 06:04
by FDW
I am trying to build a simple report that shows the Chart of Accounts grouped by:

Level 1: CAT_TITLE.TITLE (e.g. Sales)
Level 2: CATEGORY.NAME (e.g. Product Sales)
Detail: NOMINAL_LEDGER.ACCOUNT_REF (e.g. 4000) + NOMINAL_LEDGER.NAME

How do I link the NOMINAL_LEDGER.ACCOUNT_REF field to the LOW and HIGH fields within the CATEGORY table?

Any help would be gratefully appreciated.

Re: Building Chart of Accounts report

Posted: 15 Jun 2012, 11:36
by brucedenney
You can not!

On 2 counts

First It is possible to have nominal codes that do not appear in charts of accounts and with overlapping ranges, codes that appear twice.
Second the Chart of Accounts provides a range, not the actual number included.

Why are you trying to do this?

Re: Building Chart of Accounts report

Posted: 17 Jun 2012, 13:48
by FDW
Wanting to build a P/L trend (12 month view) which adds the Parent levels (e.g. Sales and Product Sales) - I can write the SQL needed and wasn't sure if you could use raw SQL to make joins in Sage 50 Report Designer.

Re: Building Chart of Accounts report

Posted: 18 Jun 2012, 11:48
by brucedenney
I would do it in Excel, link the data in with ODBC.

I cheat a little and only use the low end number, vlookup the nominal code in the charts of accounts low number column using an inexact match and that gets you the heading.

This doesn't work if there are missing nominals or if there are duplicates, but I assume that this is not the case.