ODBC output versus Dage Report Output
Posted: 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
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