Alto records Overdue notices at the level of a loan. The bundling of several such notices into a single printed letter for a borrower happens outside Alto itself. The notices may even be sent by email rather than printed.

Capita is currently looking at enhancements to the whole area of notifications; but in the meantime, it is often possible to get a good approximation of the number of actual physical letters sent out. This assumes that the letter despatch process creates one physical letter for every Borrower Barcode for all notices sent out on a given day. It may not give correct results if the letter creation scripts are run more than once for the same borrower on the same day.

In this post I’ll illustrate a basic report and also a more advanced one, assuming in both cases that an analysis is needed by Borrower Type. The examples are created in Web Intelligence in Decisions based on BusinessObjects XI R3.1 but the same approach could be used in Desktop Intelligence and/or Decisions based on BusinessObjects XI R2.

The simpler report

Start by creating a query like this:

Obviously the date cut off can be changed to suit your preferences. You may also want to filter on Overdues/Recalls and/or Letter Number. When refreshed, this produces a default report like this

The next step is to create a variable that counts the number of unique dates for each barcode. There is a gotcha lurking here. By default a date displays as (say) “2011/11/29” but under the bonnet it is a date and time, so Decisions would regard these two as different dates for the same barcodes:

The solution to this is to turn the dates into ordinary strings before you count them. This bit is somewhat “techie” so unless you are happy to get involved in the details you might just want to cut-and-paste the formula:

=Count(FormatDate([Date of letter];”dd/MM/yyyy”))

[…depending on your browser you may see vertical bars around dd/MM/yyyy above. This should be double quotes]

Drag your new variable and drop it onto the Date of Letter column (you could delete the Date of Letter column and add the Letter Count separately: we are just re-using the redundant column).

You now have a list of the barcodes with the number of letters. You can do two more things just to make it a bit easier to interpret, Firstly you can right click the Borrower Type column and select Set as Section. Secondly you can add a column total to the Letter Count column:

With some tidying up of headings and column widths, the report might look something like this:

Exported to PDF there would be a bookmark for each section:

A more Advanced Report – Adding a Summary

The approach described above will get you the data but not necessarily in the most convenient form. You may not want pages of individual barcodes. If all you want is a total for all barcodes in each Borrower Type, there is a way to do this, but it requires a modification to your variable. If you just drag borrower type and your letter count variable into a new report tab, you might see something like this:

The numbers look ridiculous. The reason is that Decisions can’t guess what you want and is faithfully counting the number of unique letter dates for each Borrower type – roughly, letter runs for each borrower type. To get the result you want, the easiest way is to get Decisions to count each unique data and Barcode combination. This involves creating a new variable (or modifying the old one) thus:

The formula counts each unique combination of day/month/year plus barcode and is now:

=Count(FormatDate([Date of letter];”yyyyMMDD”) + [Barcode])

If you use this new variable in a summary report you get something like this:

I hope this is helpful. Please do add comments to let me know how you got on with it