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

ODBC output versus Dage Report Output

Report design issues and solutions.
Post Reply
gazmoz17
User
User
Posts: 112
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

ODBC output versus Dage Report Output

Post by gazmoz17 » 17 May 2024, 13:59

Hi Bruce,

Is a report via ODBC into excel always going to be slower than directly output from Sage natively? (as in just normal report output within sage itself)

Is this because:

Program/system wise its got to wait for the odbc driver/dsn pull from the data tables into power query then run wahtever steps/transformations?

But also for groupings in power query?...

...eg Report "Product Sales by Stock Category"

Will this always be slower in power query because it has to list all the individual Product itemised prods then have a secondary "Group by step" to group them via Stock category.

[*]Same principle for cost price per invoice item and overall cost price per invoice?

I dont think theres a data field for total cost price per invoice item line is there or even for invoice cost price overall? So in power query Im merging invoice, invoice item and stock table (for lastcostprice). To give me itemised cost prices (on each invoice item line) and then summing this with a last Group by transformation....to give me a total cost of each invoice and from there (ref that new custom cost col) to give me profit etc with custom columns. Or is there a quicker more succinct step than this to reduce my transformations and speed of query.

Many Thanks

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

Re: ODBC output versus Dage Report Output

Post by brucedenney » 17 May 2024, 14:11

The sage report designer, extracts data form sage using the ODBC drivers, so performance should be comparable.

The drivers are slow.

What you do with PowerQuery may be complex and slow things down even more.

A popular solution, where data does not have to be up to the moment, is to cache the data in a different more reliable database and then run SQL queries against that
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 2 guests