Yesterday I posted in example of reporting fines and charges together. Today I’d like to do an example of another issue that folk struggle with in this area: reporting the loan data associated with charges or payments.

The problem

Suppose you have a list of payments similar to the one in yesterday’s blog post (query on the left, results on the right):

Now merely drag in the Item type and refresh. This returns wildly spurious results, and to add insult to injury takes a long time to run:

The reasons for this are complex but part of the issue is that payments and the items related to those payments are not straightforward. Alto must store its data so that it can handle part payments, reservation charges, hire charges, fines, payments that are waived: even payments that are unrelated to a loan at all. Here for example are two lists for a single loan (a loan of a given item to a given borrower, identified by LOAN_ID). We can surmise that this was a hire item with a hire charge of £1.85. There is more discussion of this in this forum post.

A solution

The key to handling this area is to use queries linked on LOAN_ID. Suppose that we want to list fines or hire charges by loan type and location and include the borrower barcode so we can easily cross-check against Alto.

First we need to be clear about what exactly we mean by “location”. Do we mean the location where the item was issued or discharged, or where the payment was made? What if (to take an extreme example) a hire item was renewed three times at different locations and payment taken as a mixture of transfers from credit and part payments – and some of the charges were waived? The answer is likely to depend upon the use to which the information will be put. If it is about planning location of tills, presumably location of the payment is critical. If it is about circulation, then the location of the original issue may be significant. We have assumed the latter in this example.

The type of charge (Fine, hire charge etc) is related to the charge so if we want to restrict our charges to a particular type, we will have to get the charges too. So in this example we have three queries. All three contain LOAN_ID which is what we will use to link them together in the report.

Note that the charge query is filtered to return just Loan Fines, and the Loan query has a filter on date range. The latter is critical if the query is going to return fairly quickly. The loans are also limited to Issues. This means that the “site” will be the one where the item was issued.

Having refreshed these queries, you will have the usual default reports. As in the previous example you can delete the report tabs and start again. As before I created an “Owing” variable. To create the report, drag everything except the CHARGE_INCURRED_ID into an empty report tab. Select the table and place a report filter of “Amount of Charge > 0” on it. This ensures that only charges for the selected Charge Type (loan fine in this case) are displayed. After the usual formatting, the final result looked like this: