The reporting of fines and charges in Decisions can be unexpectedly difficult. This post tries to explain why and gives a simple example of a fines/charges report that can be extended/adapted to suit local needs. The main issue that can cause confusion  is the way that databases handle joining two lists of data together.

The problem

If you ask most databases or information system to join two lists and present you with the results, the system has a choice to make. Did you want:

• All of both lists?
• Only those items that appear in both lists?

For example if you have a list of borrowers and a list of books and you ask for a list of which borrowers have borrowed which books, do you want to include all the inactive borrowers and all the books that were not loaned at all? These sorts of questions are often (but not always) obvious to a human but aren’t obvious to a computer. Sometimes they aren’t obvious even to a person: for example if you ask for a list of books by format, what about those that aren’t classified by format at all?

None of this is a problem if you just want a list of charges incurred by borrowers, or just want a list of their payments. The problem comes when you want a list of what each borrower owes, because that is logically all their charges minus all their credits (paid/waived). If you compose a single query in Decisions to get a list of borrowers, their charges and their credits you are in effect asking for a combined list and (unlike a human observer) the database cannot figure out from the context that you will want all the borrowers with charges regardless of payments. In this case unfortunately it will assume that you only want data where there are both charges and payments and the results will not be at all what you want.

The solution

Fortunately the solution is fairly straightforward: ask Decisions to get the two lists separately and then combine them in Decisions. Here is a simple example which lists all borrowers who owe more than £50.00. The logic is exactly the same as that described by Brian in this forum post.

The Queries

Ask Decisions to get all the charges by barcode using the Circulation Universe in one query and then all the payments in a second query:

The report

When you refresh these queries, Decisions will go and get the two lists and will display them side by side in two tables in a default report. You can delete that report tab if you wish and start again, or do as I have started to do here and delete one of the tables (right click the edge of the table and select “remove”)

Note in the data pane on the left that Decisions has recognised that “Barcode” is a common data item and has merged it (notice the “+” alongside it). Having deleted the table listing the payments, drag and drop the “Amount of Payments into the first table:

[if you aren’t used to doing this, note that you are looking for the small dark blue vertical rectangle at the edge of the cell labelled “Amount of Charge”). If the whole cell heading is highlighted you will just over-write the amount of charge rather than adding a new column]

You will now have a list of total charges and total payments for each barcode. At this point you can if you wish save it as Excel and take it from there, but if you would like to finish the job in Decisions to save having to redo the Excel manipulation every time, here is how:

Amount Outstanding

In Decisions you can create variables that are built on existing data. There are a  couple of short videos explaining variable on the Decisions website. In this example, we create a variable for the amount owed by making it equal to the amount of charge minus the amount paid:

Drag this new variable into the table in exactly the same way that you did the amount of payment

Limiting to Borrowers owing over a given amount

If you would like to limit your list to Borrowers owing over an certain amount (say £50.00) then you can use a filter on the table (this is different to filtering the query). The steps are as follows:

• Click on the Show/Hide Filter Pane button
• Select the table (a selected table has a thick grey border). Don’t omit this step.
• Drag the “Amount Owed” variable into the filter pane
• In the pop-up that is displayed, select  “greater than” or “greater than or equal to” and enter “50” of whatever your chosen limit is and click OK.

 

 

Tidying Up

If you would like to distribute the report routinely it may be worth tidying it up visually. You can for example

• Format the Currency columns
• Resize columns
• Amend Column heading text, centre it, set word wrap on etc
• Add a Title
• Reposition the table