Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

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: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

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: 4619
Joined: 28 Feb 2006, 09:56
Sage Version: v28 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 :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: No registered users and 11 guests