A Talis customer recently asked me about reporting on Reservations that had been sitting on the Reservations shelf for longer than a defined period of time. They wanted this to speed up the process of pruning uncollected Reservations. I thought that it might be useful to share my suggested approach more widely.
The base query uses the Reservations Universe. These are the minimum data items for illustration. Other data (like barcodes) can be added if required.
When this query is run, the data are retrieved, a default report created and the view switches from Query to Report
Getting the Days Waiting
There is no measure in the Universe for Days Waiting. This can however be constructed using a variable. Click the variable button and fill in the dialogue in the usual way. I called the variable “Days Waiting” but you can use any name that you wish.
The “formula” used in this example was:
This uses the built in functions (similar to Excel functions) DaysBetween() and CurrentDate().
Once created, the variable can be dragged and dropped into the report like any other data item
Note that to add the column (rather than overwrite the Borrower Name in this example) there should be a small highlighted rectangle on the end of the column heading as shown.
You might want to set the Qualification to Measure if for example you want to reuse it in a chart or graph on a different report
NB. You might find that the data displays as “#FORMAT” rather than the expected value. This indicates a mismatch between the format expected by the column and the numeric format of the data. Select the data in the column then right click and select Format Number:
Filtering down to just those with excessive waiting times
So far the report lists all items waiting collection regardless of the length of time that they have been waiting. Suppose that you just want to display those cases where the item has been waiting more than 14 days. This could be done by filtering on the query, but it is easier and more intuitive to filter the report instead. If you are unsure of the principle here, there is a five-minute video at http://www.talis.com/decisions/videos/video_intro.shtml entitled Using Filters in Talis Decisions. Here is a brief overview:
- Select the table
- Click on the “Show/Hide Filter Pane” button. This opens up the filter pane at the top of the report. Drag Days Waiting into this area.
Subdividing by Site
If you want to subdivide this report by site, you have several options. One is to right click the Site for collection column and select “Set as Section”
This creates a separate section for each site thus:
This is largely up to the user. Some options might include:
- The Date Satisfied can be removed if not required
- Columns can be re-order by dragging (the same insert/overwrite convention mentioned earlier applies)
- Column widths can be adjusted by dragging
- Sort order can be changed by right-clicking the relevant column
- Colours, fonts etc can be adjusted in the “Properties” tab
The report title can be removed or tidied up
I hope this example is useful. As ever, please do add any comments or observations on this report or any alternatives that you use locally.