Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Building Chart of Accounts report

Report design issues and solutions.
Post Reply
FDW
User
User
Posts: 2
Joined: 15 Jun 2012, 05:52
Sage Version: v2012 18 UK/Europe/Africa

Building Chart of Accounts report

Post by FDW » 15 Jun 2012, 06:04

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4237
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Building Chart of Accounts report

Post by brucedenney » 15 Jun 2012, 11:36

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?
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

FDW
User
User
Posts: 2
Joined: 15 Jun 2012, 05:52
Sage Version: v2012 18 UK/Europe/Africa

Re: Building Chart of Accounts report

Post by FDW » 17 Jun 2012, 13:48

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.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4237
Joined: 28 Feb 2006, 09:56
Sage Version: v2017 23 UK/Europe/Africa

Re: Building Chart of Accounts report

Post by brucedenney » 18 Jun 2012, 11:48

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.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

Post Reply

Who is online

Users browsing this forum: No registered users and 4 guests