Report (Excel) on customer (invoices) & Supplier (invoices)

1

I’m looking to make an export each month with the following on customers:

Customer name
Customer Identification number (VAT no)
Invoice Number
Invoice date
Subtotal amount (ex VAT)
VAT Amount
Grand total Amount Incl. VAT

For Suppliers the following fields:

Supplier Name
Invoice number
Invoice date
Subtotal Amount (ex VAT)
VAT Amount
Grand Total Amount Incl. VAT

Als when exporting, the creditnote for both customer and supplier needs to be in it, or a way to do similiar as exporting invoices.

I hope someone has been doing this and can help!

2

I’m able to run these Custom Reports in Manager:

SELECT reference, issuedate, customername, balancedue FROM SalesInvoices WHERE IssueDate > DateTime(2016, 9, 1)

SELECT reference AS Invoice, issuedate AS Date, customername AS Customer, balancedue AS Balance_Due FROM SalesInvoices ORDER BY issuedate, reference

You can try to change the variables for your needs.

3

Dear ntrim,

Thank you so much.
SELECT reference, issuedate, customername, balancedue FROM SalesInvoices WHERE IssueDate > DateTime(2016, 9, 1)
This one seems to be fine, however I’m missing a few fields e.g:

Business Identifier
Subtotal amount (ex VAT)
VAT Amount

Do you know how to get these from the tables?

4

Custom Reports are work in progress, so not all Variables or Operators are available yet.

There was a page with variables but I can’t find it.

Report “SELECT businessidentifier FROM Customers” is working but when I try UNION operator returns syntax error.

EDIT: I found “totalamount” is working…

You can try some tables to see what’s returned.

SELECT * FROM Customers
SELECT * FROM Suppliers
SELECT * FROM SalesInvoices

and so on…

5

I have been working on this, to see if I can get the report I wanted. However it seems impossible, as SQL data structure is uknown. I hope this will be solved, or someone has information ont it.