Need more help now? use pay-as-you-go support, call now 03333 445950

We offer rebates on Sage50 subscriptions, discounts on products, service sagecover (not renewals) and stationery.

We also provide Sage 50 integration and email/web services.

ODBC/Report Designer Help Please!! :)

Report design issues and solutions.
Post Reply
natheplas
User
User
Posts: 4
Joined: 12 Jan 2017, 18:40
Sage Version: v2017 23 UK/Europe/Africa

ODBC/Report Designer Help Please!! :)

Post by natheplas » 08 Feb 2017, 18:33

Sage 50 Accounts Version 23.0.3.140
Sage 50 Manufacturing 2013
Server 2012 R2 Standard
Client machines operating system Windows 7 SP1 Pro.

Hello,

I've got into using the ODBC function on excel with Sage 50, it's particularly useful for creating reports for the company I work for. I wanted to know if anyone can help me with creating a report that uses ODBC which for me is more advanced than I have previously created, or let me know if it won't be possible.

The table of data I need to return with each column labelled:

Customer Reference, Customer Name, analysis 1 (sales account managers), Month to Date Invoices, Month to Date Credits, Year to Date Invoices, Year to Date Credits, Month to Date product list 1 Month to Date product list 2, Month to Date product list 3 Month to Date product list 4, Month to Date product list 5.


Pulling customer reference, customer name and analysis 1 is straight forward from sage to excel using odbc.

I also know how to link tables in the microsoft query so that I show how much a customer has been invoiced in a certain month, what I don't know how to do is set it up so that I can run this the first day of the month, for the previous month (for example set it up so i can run 1st February, reporting all January).

I then need to work out how to make it keep adding the monthly invoices and add monthly credits, to produce 1 cell with year to date total invoices and another cell with year to date total credits.

The penultimate hardest action I don't know how to do is, return data relating to a specific list of products against certain customers. For example, the customer 'Smiths', buys a sprite can, coco cola can and a fanta can. Smiths buys lots of other products too, but in cell A we are only concerned with a particular month, and those 3 products invoices and in cell B those 3 products credit notes for that month for that particular customer and cell C the total yearly invoices for those products, and cell D total yearly credits for those products . How can I incorporate that in this report?

The last thing and probably hardest, is certain customers have branches that have different sales managers look after them, but on sage the customer has only one account so we can only use the one sales manager analysis 1 code, but this does not accurately show which branch is assigned to which customer. We get past this by putting the sales order on the system with a specific prefix on the customers sales order number, we know which region/sales account manager that will apply to as it relates to a specific sales manager. How can I incorporate that level of break down within an odbc link?

I know I've written and asked for a lot..., or this could be particularly basic for many people. I'm fairly new to odbc reporting, but would love to know if something like this is possible and any advice on getting me there or showing me how to set up the query table that would really good and I would really appreciate it!

Thank you!

Nathan

mdonaldson
User
User
Posts: 2
Joined: 16 Jun 2017, 12:58
Sage Version: v2017 23 UK/Europe/Africa

Re: ODBC/Report Designer Help Please!! :)

Post by mdonaldson » 28 Jul 2017, 10:28

Hi Nathan

I appreciate your query was a few months ago, however I'm interested to know if you solved the problem? We have created fairly sophisticated ODBC reports in the past but have spent the last 3 years creating a web based product for Sage 50.

Given you have what seems as some fairly specific reporting requirements - I'd be delighted if you would try our product for a 14 day free trial. I think it would achieve much, if not all of what you are trying to do. Feel free to contact me at mdonaldson@cascade.bi or via our website www.cascade.bi for more info.

Look forward to hearing from you,

Many thanks,

Mark

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

Re: ODBC/Report Designer Help Please!! :)

Post by brucedenney » 02 Aug 2017, 10:26

Unfortunately very long posts that cover a lot of issues are difficult to reply to.

I think you are better off splitting up these sorts of things into several smaller more specific posts.

The data such as monthly values can all be pulled form the Sales_Ledger table

You can use the offset function in excel to get a value form the monthly buckets based on a number in another cell.

You can add filters to ODBC using parameters and these can be linked to values in cells.

It is all easily done, it just can't be explained in one reply.
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 1 guest