Decisions Blog

Archive for August, 2010

Reservations – time awaiting collection example

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 Query

clip_image002

The base query uses the Reservations Universe. These are the minimum data items for illustration. Other data (like barcodes) can be added if required.

 

 

 

Default report

clip_image004

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

clip_image006

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:

=DaysBetween([Date satisfied];CurrentDate())

This uses the built in functions (similar to Excel functions) DaysBetween() and CurrentDate().

clip_image007

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

clip_image009

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

clip_image011

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

 

 

 

    clip_image013

  • This opens a dialogue. Set the Operator to “Greater Than” and the value to “14”:

 

 

 

 

Subdividing by Site

clip_image015

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”

 

 

 

 

 

 

 

clip_image016

This creates a separate section for each site thus:

 

 

 

 

 

Tidying Up

This is largely up to the user. Some options might include:

  • The Date Satisfied can be removed if not required

    clip_image018

  • Columns can be re-order by dragging (the same insert/overwrite convention mentioned earlier applies)

 

 

 

  • Column widths can be adjusted by dragging

clip_image020

  • Sort order can be changed by right-clicking the relevant column

 

 

 

 

 

 

 clip_image022

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