Last week I was looking at a Talis Decisions report problem: some results were returned but they were wildly inaccurate. In the end it turned out to be a fairly simple issue: the wrong object had been used in the report: "Home Site" (i.e. the borrower’s home site) was being used instead of "Item Home Site".

This kind of mistake is easy to make: clearly every object has to have a unique name so occasionally object names have to be chosen to be unique rather than purely for clarity in the context. Also if later on experience suggests an improvement in naming, this is difficult to do because changing the name of an object breaks all the reports that use it. Every object does however have pop-up help associated with it (just hover over the object), so if you get odd results from a query one of the first things to check is the pop-up help just to make sure that you have the right objects.

There are also situations where what really is the same thing pops up in two places: an example is Item sequence and Sequence Where this occurs, the difference is often between whether the results are inclusive or exclusive (i.e. all items, with Sequence if they have one; or just those items that have a Sequence). This issue is covered further on the Exploring the Talis Decisions Universes webinar.

The real test of the usefulness of Management Information is the action you can take on the basis of it, or what learning you take from it that in turn may lead to action. A customer (Queen’s University Belfast)  recently ran a student survey at one of their libraries and on the basis of this re-located some self-service terminals. This was the impact of the change on self-service terminal usage (note that figures for July 2009 are interpolated).

Surveys are a form of Management information, and this one didn’t just produce “Fancy that” information. It produced results that could be (and in this case, were) put into action. The subsequent monitoring of results closed the loop. If (in the absence of any other change) transactions had fallen rather than climbed, the terminals could have been moved again either to somewhere else or even back to their previous locations.

This was an example of good practice, but there are far too many examples of wasted effort in the Management Information world. Information is expensive to collect, collate, present and (an often overlooked point), expensive to absorb. You can waste end-users time (and therefore by implication, the institution’s money) by sending them heaps of data that they can neither learn from, nor take action on. Furthermore, a mass of information presented in different reports at different intervals can hide the important stuff as surely as a forest can hide a leaf or a blizzard a snowflake. Before you specify a report or decide on a data-collection exercise, it is always worth pondering what actions are available to you when you get the output.

Several universities have told us that they have had what sounds like the same Freedom of Information (FOI) request from a national newspaper. Most of the data requested are easy to extract using Talis Decisions but one area is statistics on historical overdues. Data on current Overdue are easy to extract using Talis Decisions, but the need to extract detailed data on historical Overdues was not envisaged. This raises some interesting issues of principle:

• How should permanently lost items be accounted for? Without some thought here, the statistics are likely to reflect items most commonly lost or stolen rather than those which are eventually returned.
• Should the data be at the level of individual item or barcode (e.g. this particular instance was most commonly overdue), at the level of an individual catalogue record (Bib ID) or even at the level of all editions (e.g. all editions of “Macbeth”)
• Should all types of Loan and all types of Item be included? What about home delivery, books on prescription and all those edge cases?

Illustrated below is one example of a possible approach to this problem. The assumptions made are that:

• Data should be accumulated at the level of Control Number (i.e. catalogue record)
• Overdues above a particular threshold should be excluded
• Certain item types should be excluded

The queries

There are many ways to organise this. In the example report created for this, I reported on Fines as well and used four queries, merging the results . Only two are needed for the Overdue report. These are discussed below

Bib Data

This query brings back the Bibliographic Data Author and Title for the items that you want to include:

The list is limited by Item Status as a prompt, in part to reduce query execution time by (for example) excluding redundant items.

Overdue Items

This is the most complex query. It brings back the Transaction Date and the Due Date by Item Barcode. These data are used to calculate aggregate overdues:

Note the prompt limits on Transaction date. Without limits on the date range, this query could run for a very long time. A further filter could be used to bring back only discharge transactions.

As it stands, this query only brings back current overdues. The generated SQL must be modified to bring back all overdues. To do this, proceed as follows:

Click on the SQL button on the control bar in this query:

This will open up a window which allows you to modify the SQL. Delete the line ringed in red:

NB: if you make any changes to this query (including filters) the SQL is regenerated and this step must be repeated

Once these queries are produced the report can be run

Variables

To transform the raw dates into an elapsed overdue days, I used a variable thus:

The Formula used is: =DaysBetween([Due date];[Transaction date]). This calculates the number of days between the due date and transaction date for each item.

I also used variables to calculate and display parameters. Here is an example for a variable I called End Date (this displays the transaction end date used in the prompts in the most recent refresh of the report:

This uses the UserResponse function. Note that you may need to include the optional Query Name (“Overdues”) in this example if you are using the same prompt text in multiple queries as described earlier. This allows you to create a report page which documents the parameters used in the report thus:

Reports

This should give you the data that are needed to create the reports that you need. Two other ideas might be helpful:

• You can use a Report filter (as opposed to a Query filter) to restrict the Days Overdue or Underdue to a range  (say 1 – 365) to filter out those where the item has been returned before the due date and also those that will almost certainly never be returned.
• If you right click the Days Overdue or Underdue column in a report you can sort it descending

These features are illustrated in this sample report:

In Conclusion

If this has been helpful, please email me directly, or comment below. If you would like a BIAR file containing an example report, please email me.