Customer Open Invoice Report
In this report, we will demonstrate how to create a customer invoice report using Jet. This report contains documents that record the service or product delivered to a customer, the amount paid, and the payment method. A customer invoice report is just a record that shows a transaction between a buyer and a seller. The invoice always specifies and provides valuable information, even if the products are bought on credit.
We also show how to add a Report Options page. in this case, (the Date filter) to a report.
Our Report will be created from the following tables as shown.
Step 1 Grouping the Report
This report will be grouped by the customer no and name (Primary list). To do that, we separate our primary list from our secondary list by moving our secondary list to the next row under our primary list, as shown below.
Step 2 Adding Report Options to the report.
Navigate to the Ribbon tools and click on Report Options.
when you do that, a new Report Option Page will pop up. on the pop-out, you can fill in as shown below.
Note: If you want to know more about Report Options, Kindly check our Article on that.
Step 3(a) Fill in the formulas for the primary list.
I’ll show you how to fill in the formulas for the Primary list and also the secondary list.
Filling in for the Primary list
Go to cell E9, click on the NL function, and fill in as shown below.
Go to cell F9, Click on the NL function, and fill in as shown below.
Note: the No. the referencing to cell E9.
Step 3(b) Filling in the formulas for the secondary list
for the NL (Rows)
Note: we only what the report to show an Open invoice.
There is an open column on the Cust Ledger Entry, apply the filter here and select true. That’s what determines receivables. (Open invoice).
For the NF(s)
Fill in for each NF with the same Key as shown in the table above and also with the respective fields.
Step 4 Applying a filter to the report
I included just the Date as a filter on the Report Options page in this report. Feel free to use as many filters based on the criteria of the report.
The NL Rows(primary list) must be filtered to the posting Date from the report options.
The NL First Customer name must also be Filtered to the customer no on the report. Cell E9.
The NL Rows (secondary list) must be filtered to the Customer no on the report, and also the posting date From the report options. in this case, cell E9.
To filter for the “posting date“, make sure your cursor is on the filter box, then click on Insert Report Option.
A new page pops out when you do so, click on Date range then click on add. This will add the date to the filter.
Filter applied to NL (Rows) Primary list
Filter applied to NL(rows) Secondary list
Step 5 Adding Subtotal and Grand Total to the report.
This empty row is typically selected so that the report can automatically adjust and expand as new data is added to the data range
When you run your report, it should look as shown below.