Workbench Blog

Create a Debtors report using Sharperlight

[fa icon="calendar"] 04-Aug-2017 11:30:28 / by Justin Gardner

Justin Gardner

The following Tutorial steps you through how to build a debtors report using Sharperlight.

There are a few different ways in which you could approach this report so we will focus on the concepts and you can the tailor the report to your own requirements.

 Pic1.png

Start by creating a new Query in the Publisher.
We want to create a Summary Report and report from the Job Invoices table.


Pic2.png
Select the main outputs for the report e.g. Client, Invoice No., Invoice Date, Job Code etc.

 

Pic3.png

Preview the query to ensure you are returning some appropriate data.

 Pic4.png

We will run this report based on dates so include all Periods and also add the Paid field to the filter list. We want to set this to N so we are only including Invoices that are not flagged as Paid.

 

Pic5.png

We then want to drag a number of Invoice Date filters onto the report. We will use these to create Filtered columns where we will display the Invoice balance so that we can show the aging.

 

Pic6.png

We will use the DATE parameter as part of the first aging column and then we will offset this date for all f the different aging periods we want to report. It is a good idea to rename the filters so that it is clear what they are and also specify the filter values for the aging columns.

To the right of the filter you will see the calculated dates that column will be filtering on. By using the DATE parameter the report will always calculate the aging starting from todays date. You may wish to using something different here like the period end date if so then you would need to set this as the first filter value in place of the date parameter.

 

Pic7.png

Once you have finished it should look something like this :

 Pic8.png

The next step is to output the Invoice balance and tie it to each aging filter.
It is a good idea to now rename the Invoice Balance fields also.

 

Pic9.png

You then want to tie each filter to a specific date filter. In the above example we have tied this value to the 0 to 30 days filter.


Pic10.png

 When you are finished it should look like this, notice the filter names on the outputs show which filters apply to which output.

 

Pic11.png

Preview your report and you should see that the invoice balances are now split across the aging periods.

 

Now that you have the basic data together it is a good idea to save the report and then you can play around with some nice formatting. 
Some formatting ideas that look nice are : 
      • Specific column widths for the Balance columns
      • Format the numbers to remove decimals
      • Use Sorting and grouping options.
      • Collapse Table Rows (Do this when applying the sorting)

 

Pic13.png

You can further refine how the report is presented using the Table tab under the query designer. With a few simple selections.

 Pic15.png

Now preview your report in the web, you should have something similar to the above. 
You should be able to click on a client and expand to see the list of individual invoices outstanding.

 

Congratulations on creating your Debtors report, remember you can apply these same concepts to other things you may wish to report out of Workbench.

Topics: Business Intelligence, Sharperlight, Education

Justin Gardner

Written by Justin Gardner

Senior Workbench Consultant based in Melbourne Australia.