Adding Custom Reports to Horizon

I have been putting a lot of our reports into Horizon so that staff can take a look at them any time and do not need an intermediary. While not even SirsiDynix could help me put all of them in Horizon, I have a few in and I am adding more as I encounter them. Unfortunately, custom reports do not seem to be documented very well (or at least not that I could find, the one article I found did not make a lot of sense to me the first time I read it), so I figure I might as well post it online.

There are three major parts to creating a custom report or view:

  1. Create view holding SQL query/data.
  2. Create mq_view in Horizon to display results of query.
  3. Add the view to Horizon.

Creating View in SQL

First thing of course is building the SQL query to pull the data you want in your custom report. I will not cover how to build queries here since that’s a whole other topic, but there are a few differences from building a query you would run in a SQL interface and what to use for Horizon since a lot of the controls around display and related columns will come from setting up the view in Horizon rather than having to do it in the query itself.

Query Building Tips:

  • Do not pull title and other basic bib information in the query. This information will be pulled via MARC map in the Horizon view.
  • Leave data unconverted. For example, dates are commonly converted with dateadd or datediff, but again the conversion will be done by Horizon.
  • Friendly reminder to use unbroken strings for column names.

Creating the view is actually very easy once you know what your query will be.
[code lang=”sql” light=”true”]
create view viewname
as
QUERY[/code]
where viewname is whatever you want to call your view.

If you make a mistake and need to replace the view, add before the create view:
[code lang=”sql” light=”true”]
drop view viewname
go[/code]

Here is an example of a query that we use to pull holds that are about to expire. Notice that it is not pulling title information here nor converting things like date:
[code lang=”sql” light=”true”]
select distinct r.bib#,
name_reconstructed as ‘borrname’,
r.pickup_location, r.expire_date,
collection,ibarcode,
item_status,
last_status_update_date
from request r
join borrower b on r.borrower#=b.borrower#
join item i on r.bib#=i.bib#
where request_status = 0
and r.expire_date
between datediff(day,’1 jan 1970′,getdate())
and datediff(day,’1 jan 1970′,getdate())+61[/code]

After you’ve created the view, give Horizon staff clients permission to pull the view:
[code lang=”sql” light=”true”]grant all on viewname to staffgroup[/code]

Step 2: Create Horizon View

Under Table Administration, load mq_view and create a new view, or search for an existing one to clone and edit. Some good examples include looking at:

  • item_report for how to pull title entry using the MARCmap
  • item view for conversion of dates and other types of data

When adding a new view, you can add just the basic information to get it up an running quickly:

  • MQ View Name: use a single string (e.g. underscores instead of spaces)
  • Table Name: the ‘viewname’ from the SQL query
  • List View: add all the fields.

screenshot of example mq view

List View Settings

At minimum, add the fields that need to be displayed, but you can all the fields that the query pulls.

  • Column name: column name based on the query. For example, in the above query, ‘borrname’ is used in place of ‘name_reconstructed’ (the column name in the borrower table), so ‘borrname’ would need to be entered.
  • Column label: fill in even if it’s exactly the same.
  • Ord: order to display. Enter any number so that lowest to highest displays left to right.
  • No. of Display Characters: take a guess, and adjust as needed after.
  • Displayed: Check if the column should be displayed by default. Those that are not checked users can add using the “Display…” button and add manually.
  • Code Translate: Check if you want the human readable version of the field data instead of the stored value e.g. ‘trace’ instead of ‘t’ in item status.
  • MARC Map: To map bib information. Enter the appropriate MARC map in the form of Dfield (typically Dtitle, Disbn, Dauthor). For example, if you want the title, use ‘bib#’ for column name, ‘Title’ for label, and ‘Dtitle’ for MARC map (see below).
  • Sort Processor: use ‘None’ in most cases except where it’s an alphabetic string, where it may be appropriate to use SrAlpha, but can still use ‘None’.

horizon mq view marc map example

Edit View

The edit view is to set up fields that staff should be able to edit directly from the report. This is generally not required since most reports are for viewing and does not require editing.

However if you need a reference, the item_status_group_edit mq view is a good example of a simple edit view.

Column Types

Column types is where you would specify any conversions, particularly dates. This is generally straight forward for typical fields. For dates for example, simply add the ‘viewname’ under table name, the column name, and mark the data type as ‘Date’.

horizon mq view column type example

For reference there is also the Horizon Customizing MQ-Views Training Guide (Support portal access required).

Step 3: Add the View in Horizon Navigation Bar

There is a knowledge base article (requires login) that covers adding the custom view for access. The instructions below are a bit different in that it focuses on setting up the view for a specific user rather than through the group manager.

  1. Right click in the white space of the navigation bar.
  2. Click on the Navigation Bar tab.
  3. Click on the ‘Table Editor’ entry.
  4. Click on the Copy button on the right side.
  5. Enter a label for whatever you want to call it.
  6. In the ‘Arguments’ box, enter /v followed by the name of the mq_view which you want to access.
  7. Click on Apply and/or OK to save.

horizon custom nav entry

For certain views, the client likes to prompt users to search. To force the search window not to pop up, add ‘/f”field>0’ where field is typically something like item#.

The SD staff suggest that it’s best to use the the Table Editor entry because some of the other views have slightly odd settings that can be copied over.

Benefits

Hopefully the benefits are self evident, but the main one for our library is that it eliminates the intermediary. We no longer need someone to run the reports just to send it to another person. The staff who need the report can run it any time they want, and export or print when they need to. One of the reports is now being used daily instead of weekly since they have the convenience of it being in their Horizon, which allowed them to change their routine.

Of course, it also saves time, since the intermediary person no longer needs to run these reports. Added to that, there’s also no lag time between when the report needs to be done and when staff will receive it.

Big thanks to the staff at SirsiDynix who helped me figure this out. A number of the tips