Custom report - can't find table/field with the Invoice Amount

In the AR_InvoiceHistoryHeader table, the sum of the lines is:
TaxableSalesAmt + NonTaxableSalesAmt

The invoice total would be: TaxableSalesAmt + NonTaxableSalesAmt + FreightAmt + SalesTaxAmt

I can't remember if you need to subtract DiscountAmt (you can test this).

Hi andymc

>The invoice total would be: TaxableSalesAmt + NonTaxableSalesAmt + FreightAmt + SalesTaxAmt
>if you need to subtract DiscountAmt
Thanks.

While I'm at it there is one other thing I've been struggling with - joining tables.

Experimenting is taking a lot of time.
Since this is a remote legacy system the VPN/ODBC is dead slow.
A 5 minute wait is common for retrieving small data samples from a single table.

AR_InvoiceHistoryHeader and AR_CashReceiptsHistory appear to have all the raw data I need.
But they don't seem to share common primary keys.
I'm trying a new join on ARDivisionNo + CustomerNo + InvoiceNo now.

Any tips you can share?

Thanks









Bruce O

The report is slow because the tables are so large. I would suggest either importing the tables into an Access database or writing SQL Specific Pass Through Queries in Access for each table used in the report and base the report off that.

Visit Sage's Online Community

> I would suggest
> importing the tables into an Access database
> or writing SQL Specific Pass Through Queries in Access
>for each table used in the report and base the report off that.

Thanks, I finally did end up bringing everything into Access a few days ago.
Originally I just wanted to speed up the verification of the data.
Now I'll follow your advice and finish up the whole report in Access too.

However, I wonder if something could be set up wrong?
I've built ODBC reports that connect to other types of databases, using more, larger tables with more records.
I've never seen speed issues like this before.



Bruce O