One Decisions report I’ve been asked about is how you report loans that have been completed from start to finish within a certain period of time. This is relevant to the Summer Reading Challenge in public libraries where participants are “challenged” to read 6 books between two dates.

The problem

Listing the number of issues that a borrower has had in a date range is easy. Listing the number of discharges is also easy; but what we want is to count loans where the issue and the discharge both occur in the date range: i.e. we want to ignore any discharges without a corresponding issue and vice versa.

The solution

There are several ways we could tackle this. One is to take advantage of the fact that Alto uses a unique LOAN_ID for each loan, and use a “combined query” in Decisions. For this example, I’m assuming that participants in the Summer Reading Challenge in a particular year are identified by a particular interest code. If this is so, the procedure is as follows:

Create a query with the following data in it:

• Borrower Barcode
• LOAN_ID. This is at the bottom of the tree structure of objects in the universe:
• Total Loan transactions

NB: it is vital to limit the date range on the loans. Here I’ve add start and end date prompts which make the report re-usable – you can change the dates any time you refresh it

Ad a query filter on the Interest code (or other identifier of participating borrowers)

Now click on the Combined query icon in the toolbar. This creates two queries out of the original one with the same data in both. You can select which one you want to look at in the bottom left pane.

Combined queries must return the same data items but can have different filters: indeed you will have to add the date and Interest filters back into Combined Query 2. Ideally use exactly the same text (including spaces, capitalisation and punctuation. Decisions will the combine the two prompts)

Here comes the clever bit

• In Combined Query 1, add a Query Filter to restrict the rows returned to just the issues
• In Combined Query 2, add a Query Filter to restrict the rows returned to just the discharges
• Double click the word “Union” on the left of the Combined Query box until it reads “Intersection”.

Now refresh the report. What Decisions does under the bonnet is go away and get a list of all the issues in the period for Borrowers with the selected interest code. It then gets a second list of all discharges in the period for Borrowers with the selected interest code. But it displays only those Borrowers and Loan ID combinations that are in both lists. In other words if the one query returns a Borrower barcode/LOAN_ID but the other one doesn’t, it isn’t displayed.

After the refresh you will get a default report with a table in it. This contains all the barcode/LOAN_ID combinations  as described above along with a column of transaction counts. The latter will be a bit dull, being uniformly “1”. Delete the LOAN_ID column (right click and select “Remove Column”). This will leave you with a list of barcodes. The “Total loan transactions” column should adjust itself to the total for each barcode. As described in the post on fines, you can add a filter to the table and limit the data displayed to whatever value you like (e.g. 6 or more for the Summer Reading Challenge. As before, select the table before adding the filter)

If your interest is primarily the total number of qualifying borrowers rather than the detail, you can right-click the barcode column and select Count in the maths drop down  to get a total at the bottom of the table.

You can tidy the report up further by tidying up headings and column widths. In this example, I have also sorted the table on descending number of loans. This is done by right-clicking the column you want to sort on and selecting the sort option. The end result looks something like this:

You can add further report filters to remove known exceptions. For example the top barcode in this list might be a dummy used for outgoing ILLs that had been mistakenly coded with a Summer Reading Challenge Interest Code.

If you are a Capita Libraries customer, you can download a zipped BIAR file below containing a sample report. If you download it and try it, please do let me know how you get on with it. If you would like it but cannot download it, drop me an email.

2011_09_05_SRC.zip (log in required)