What if we want to create a report that shows the amounts paid and unpaid for each invoice, including total values? This type of report can be created and the step by step method is detailed below.

Before we begin, it is worth noting that the 'Payment Status' field is not a standard field but actually a custom field. This or indeed many other fields can be created using the method described here.

We start with an invoice that includes a payment status column that allows picklist options to be selected of Paid, Unpaid or Missed.  For the purposes of this example, both Unpaid and Missed are being treated as Unpaid.

Create an invoice report (Start > New Report > New prepopulated report > Customer Invoices).

Next we want to customise the report to only show the data that we want. Select the Details tab and then on the right hand side the columns that will be used in the report are listed. We can delete any of these by clicking on their red cross to customise the report to look exactly how we want it.

In order to generate the report that we want, we need some columns to be added to this list as described below.

• Add a column for Payment Status (Add Column > Line Items > Payment Status).
• Add another column for Gross Line Item (Add Column > Line Items > Gross Document Currency).
• Add another column for Line Description (Add Column > Line Items > Line Description).

Please note, in this example, we've changed the name of the column for Gross Document Currency to Gross Line Item.

We now want to add two calculated columns: one called Line Items Paid and the other called Line Items Unpaid.

Line Items Paid

Create a calculated column that uses the IF function to identify whether or not to include the value of the line item in the column, which looks like this:

• IF(order_line_items.cf_customer_invoice_line_item_payment_status='Paid',order_line_items.document_currency_gross_value,NULL)

Line Items Unpaid

Create a calculated column that uses the IF function to identify whether or not to include the value of the line item in the column, which looks like this:

• IF(order_line_items.cf_customer_invoice_line_item_payment_status='Paid', NULL ,order_line_items.document_currency_gross_value)

You'll notice that this is the inverse of the calculation for Line Items Paid, which means it will include all lines where the Payment Status is Unpaid or Missed.

Now we want to create a summary view that shows the gross, paid and unpaid totals for each invoice as shown below.

Click Add Summary View > Add Summary View from Details.

Click Add grouping column > Invoice Number.

Now add in a value column for Invoice Name.

Finally add in subtotal columns for Gross Line Item, Line Item Paid and Line Item Unpaid.  (Click Add value column > Subtotal Column and select the name of the field from your detail view using the picklist.)  You can rename the titles if you like.

NOTE:  There is a check box for Display summary that should be ticked if you'd like a grand total below the column as shown below.