Categorising Your Outstanding Reports in Dataloy BI

The Accounts Receivable domain in the Dataloy BI tool is a powerful basis for creating a variety of reports on outstanding customer invoices in the VMS. You can filter, group and sort on a range of dimensions and tailor make to fit each user group's requirements.

In this post I will describe how you can use filters to create a main categorisation of your outstanding invoices into "Demurrage", "Freight", "Various", which is a common requirement from our customers.

By using the "Account" filter, you can easily extract e.g. all outstanding Demurrage amounts. However, to get the correct total amount, you need to also include the Address Commission. Typically, you would filter as illustrated in the below screenshot:

By using this filter you would get all demurrage amounts, however you would also get ALL address commission lines, not only those relating to demurrage. So, how can you avoid getting all the additional ones which makes the amount incorrect?

The answer is to use the "Ref Account" field (introduced in version 2017-03 of Dataloy BI). By also adding a filter on this field, filtering only demurrage, you will get only address commissions referring to demurrage amounts. All other address commissions (i.e. referring to freight, various revenue etc) would not be included. So, your filters should look something similar to this:

By using similar filters you can of course create freight only or various revenue only reports, or a mix of any of these to get your flavour of report.