Page 1 of 1

Statements O/S to Excel Help

Posted: 08 Mar 2018, 12:04
by pauljj
Hi Bruce,
Sage v24

When i export a basic customer Outstanding Statement via the Data To Excel function, i see each itemised line on the invoice with it's price.

what i am attempting to do is to produce an excel sheet that will show the Invoice/Credit number, Customer name, customer PO ref and the gross amount for that invoice/credit.

I have managed to include the customer order ref and the joins are in place.

When i preview the report i can see the data i want without each "TransactionItemised.ForeignAgedAmount"
i've literally removed all debit and credit fields and just left the "AUDIT_HEADER.FOREIGN_AGED_BALANCE" on the report and i'm still seeing the splits.

Any ideas?

Re: Statements O/S to Excel Help

Posted: 08 Mar 2018, 12:38
by pauljj
Think i have sorted it, i pulled out Audit Splits headers and footers , and changed the report filter expression from AUDIT_SPLIT to AUDIT_HEADER

AUDIT_HEADER.FOREIGN_AGED_BALANCE <> 0 AND AUDIT_HEADER.INV_REF NOT LIKE "REVAL"

So after removing some hidden headers and footers , i created a group of my own
AUDIT_HEADER_INV_REF
Put my fields into that

So, what i have now is
ACCOUNT_REF header
AUDIT_HEADER_INV_REF header
AUDIT_HEADER_INV_REF footer
ACCOUNT_REF footer

and that's it, so now when i export to excel it's removed the invoice line items and just shows the gross amount for the invoice number, with the customer order reference. Happy days :)

Re: Statements O/S to Excel Help

Posted: 08 Mar 2018, 12:46
by pauljj
Actually, forgot to ask, does anyone know how to re-order the fx fields in the report explorer?
a bit like defining a tab order in say access reports ?
this should then export the columns in order to excel, as they aren't at the moment.
And also , how to have an expression field export aswell ?

Thanks
Paul

Re: Statements O/S to Excel Help

Posted: 09 Mar 2018, 07:49
by brucedenney
You might be better off using odbc and pulling the data from sage into excel, rather than using reports and pushing data to excel.

If you remove all the splits then splits will not be there.

It is unclear how you are exporting to excel are you saving this as an excel file?
If so you can move the fields in the report and they will be in different positions in the output.
If you are saving as CSV then there is a property on each field to give the order.


Can you post your report so far and I will see what I can do to help.