Decisions Blog

Summer Reading Challenge

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

SRC1

SRC2

SRC2a

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

SRC4

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.

SRC3

SRC5

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”.

SRC7

SRC6 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.

SRC8 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)

SRC9 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:

SRC10

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)

4 Responses

  1. Lionel Aldridge Says:

    We have only just started using interest codes for other purposes. Using for the SRC looks useful for the 1st year & could be used for publicity the 2nd year but is there a way of automatically removing them (we get 5-7000 kids every year so manually is not on). Otherwise at the end of the 2nd year any stats will include children who did it the 1st year & not the current year.

  2. John Hardy Says:

    Hi Lionel,

    Would it work for you to use different interest codes for each year (eg sr11, sr12, sr13…)? This would also allow you to analyse “repeat business” (e.g. how many people did the SRC more than once)

  3. Lionel Aldridge Says:

    We thought of that but decided it was not on: 1) Interest codes soon cluttered up 2) Too easy for staff to enter wrongly as they come to the 1st sr & click

  4. John Hardy Says:

    There is no way to bulk-delete borrower – interest code links in either Alto or the Alto Set Config application.

    They can be bulk-removed from the database using SQL. If your system manager is comfortable with this, it can be done in house although our support staff would strongly recommend putting the SQL into a script and testing the script on the MIS server before running it on the live server. Using SQL interactively for deletion risks problems if any typing mistakes are made.

    It is also something that can be done by Capita on your behalf although this would normally be chargeable

Leave a Reply