Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Statements O/S to Excel Help

Report design issues and solutions.
Post Reply
pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Statements O/S to Excel Help

Post by pauljj » 08 Mar 2018, 12:04

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?

pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Re: Statements O/S to Excel Help

Post by pauljj » 08 Mar 2018, 12:38

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 :)

pauljj
User
User
Posts: 48
Joined: 11 Sep 2014, 11:38
Sage Version: v2018 24 UK/Europe/Africa

Re: Statements O/S to Excel Help

Post by pauljj » 08 Mar 2018, 12:46

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

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

Re: Statements O/S to Excel Help

Post by brucedenney » 09 Mar 2018, 07:49

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.
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 2 guests