I have been playing around with report transformations and am very excited about the ways that they’ll help me once I get a few set up. However, I’m having a little difficulty with my first attempt.
I need to generate a report that lists all purchase invoices within a specified period, showing the following columns:
- Date
- Supplier
- Vat number
- Invoice number
- Cost excluding VAT
- VAT amount
- Amount including VAT
- Description of goods/services
I have managed to access the data for all the columns, but not the right amounts. For each line item on each purchase invoice I am getting a row with the VAT amount appearing in all three amounts columns, followed by a row with the tax exclusive amount appearing in all three columns. I want the right amounts as per the headers, and I want to see only a single row for each purchase invoice, not for each line item. Is there a way to group by purchase invoice? Or should I be selecting something other than tax_inclusive_purchases from transactions?
I will also need to create a similar report for the sales invoices, but I think that once I figure this one out that will be easy.