Reports in Horizon

1
Reports in Horizon

• The Easy, the Difficult, and the Nearly Impossible

2
Easy Reports

• Item Report and Table Editor can answer many
  quick questions.
• For example How many borrowers do I have?

3
How many borrowers?
4
After double clicking on Table Editor, type
borro in blank.
Click on the borrower_dms table. Click OK.
5
Highlight Location. Type in your location Code.
Click OK.
6
Result is a list of borrowers at Malone. The
total is given.
You can also display more columns with Display
and sort the borrowers in a variety of ways with
Sort.
7
More easy reports To find out how many times an
item has circulated

• Search for the item
• Click on Detail Status to see the CKOs
• or
• Send it to the copy/item list
• Click Edit and page down to see the number of
  checkouts.

8
To see usage since a certain date

• Find an item, send it to Copy/Item List, then
• Send it to View Usage Statistics

9
Item_Report
10
Best to cancel the search window!
11
Then cancel the next message.
12
Then do Alt-F2.

• Repeat after me
• Cancel, Cancel, Alt-F2
• Cancel, Cancel, Alt-F2!
• This gives you the compound search window.
  Since there are SO many items, you want to limit
  your selection by a compound search. Its very
  powerful.

13
The Compound Search Window
If it looks familiar, you probably remember it
from the Item Group Editor.
14
Some good questions for Item_Report

• Give me all the items in the CEF Large Print
  collection that were created before April 2004
  and have not circulated at all.
• Show me all the items with status lost at my
  location and Ill sort them by last status
  update to see the older ones.

15
More questions for Item_report

• Show me all the items at my location that are due
  today.
• Show me all the items at my location with item
  type f7d that havent circulated since May.
• I bet you can come up with some I havent thought
  of that you can use.

16
Use Item_Report for a New Items List
Remember Cancel, Cancel, Alt-F2.
17
Choose your location
18
Click AND, Highlight Creation Date and
Click Date Range.
19
Enter a date, click OK.
20
Now click Search.
21
Close the search window,
Maximize the results window.
22
Click Display to choose columns to show.
23
Choose Call Number, Title, Item Type and Author
Deselect others that may be highlighted. Then
Click OK.
24
Now click Sort.
25
Sort by Item Type, then Title
Click Ok.
26
Select all the Audio Cassettes, then Click File,
Export Records
27
Choose a name and location for your file, and end
it with .txt
28
Choose the default option,click OK
29
Find your file, double click
Heres a text file you can edit to show the new
audiobooks at the library.
30
Ad Hoc Reports

• Easy to run, but sometimes give you too much.
• You can save the file and give it a name with
  .txt on the end. This will let you open it in
  Notepad or Word, or even Excel or Access.
• After that, open it in your chosen program, and
  delete those extra lines of zeros!

31
Running the Ad Hoc Reports
Double Click on the AdHoc Stat Reports Icon.
32
Choose Report Options

• You must choose a report from the left side,
• A location or locations from the right side, and
• A date it could be yesterday, the end of last
  month, or the end of last year

33
Choose Report Options
34
Choose Output Options
I prefer to save to a file, then you can edit the
report.
35
Example
36
Saving to a file
Click Save and the report will start to run.
Remember where You saved it and its name!
37
Report Generating
This might take awhile. Time to get some coffee.
38
Find the report
There it is on my desktop.
39
If you double click.
It will open in Notepad. Thats why I put the
.txt on the end.
40
If you want to work in Excel, you could save your
report with .xls
This time, dont double click on your file, find
it and open it within Excel.
41
Open Excel, then open the report
Be sure to change the Look in box to wherever
you saved your File. Highlight the file and
click Open.
42
Go through the wizard
Choose fixed width. Click Next.
43
Change the column breaks
Follow the instructions to tell Excel where the
columns are.
44
Click Finish

• The report will be put into Excel, and you can
  play with it from there.
• You can open a .txt file in Word also. In
  Notepad, Word, Excel, or Access, you can delete
  the rows you dont want. Save and print your
  results.

45
More difficult reports

• There are some tables that are hidden from you in
  Horizon. Only the System Administrator can see
  them, because by making changes to them you could
  impact the database adversely.
• Some reports are hard to do from within Horizon.
  Theres a tool called SQL Query Analyzer that I
  can use to do them.

46
Examples
select name, location, btype, borrower.borrower,
address1, city_st, postal_code from borrower,
borrower_address where borrower.borrowerborrower
_address.borrower and location'akw' order by
btype This is the SQL Query to get a list of
borrowers with addresses for Akwesasne. The
results are shown on the next slide.
47
Results of SQL Query
I would run the query for you and send the
results as a spreadsheet, access or text file.
48
Mailmerge

• Word can use an Excel file to produce mailing
  labels, using the Mailmerge feature. I can help
  you with this if you want.
• I was also able to get the names to display with
  the first name first, and the full city and state.

49
Another example
Heres a report of how many borrowers and how
many checkouts by zip code.
50
Another Example
This gives the number of bibs with no items in
our database.
51
If you can think of it.

• I can ask the Horizon Users Group listserve how
  to do it with SQL.
• Ive seen examples of collection age studies,
  reports of most requested items for a purchase
  alert, and shelf lists for inventory purposes.

52
Annual Report Time

• Dynix has almost finished a pair of custom
  reports for us to help with the Annual Report.
• They basically give us item holdings and
  circulation using the broader categories asked
  for by the state. For example

53
Plattsburgh Holdings

• --------------------------------------------------
  --------------
• Item Holdings report for Plattsburgh Public
  Library
• Category Holdings
• --------------------------------
• Adult Audio 1211
• Adult Fiction 14564
• Adult Nonfiction 36217
• Adult Periodicals 2354
• Adult Video 1001
• Electronic 27
• Juv Audio 505
• Juv Fiction 14170
• Juv Nonfiction 11845
• Juv Periodicals 62
• Juv Video 377
• Other Materials 178
• Unknown 362
• YA Audio 0
• YA Fiction 1992

54
Plattsburgh Circ 2004 to date

• --------------------------------------------------
  --------------
• Annual Circulation report for Plattsburgh Public
  Library
• Category Circ Count
• -----------------------------------
• Adult Audio 3364
• Adult Fiction 19905
• Adult Nonfiction 11310
• Adult Periodicals 892
• Adult Video 2934
• Electronic 15
• Juv Audio 476
• Juv Fiction 10577
• Juv Nonfiction 3750
• Juv Periodicals 32
• Juv Video 764
• Other Materials 163
• Unknown 19
• YA Audio 0
• YA Fiction 1359

55
Annual Reports

• Ill need to run these for you because they use
  the SQL Query tool.

56
Impossible Reports

• We cant tell you who borrowed an item in the
  past, unless they are the current or immediate
  past borrower or owe fines.
• This is a good thing for privacy of our patrons.
  The link is broken and the information doesnt
  exist in the database.
• Some statistics are collapsed over time. Circ
  data is preserved at the monthly level, daily
  stats are lost after the month changes.

57
Thats all, but its only the beginning

• Theres a lot to learn about reports. Please
  dont hesitate to ask me when you wonder if the
  system can do something.
• There may not be many impossible reports, but
  there may be some difficult ones.

58
To improve statistics, clean up the unk
collection with Item Group Editor
59
With Item Group Editor, you dont need to cancel,
cancel, alt-F2.
60
Highlight location, and type the name of your
unk collection
Click And.
61
Now highlight Call No. and put 5 in the blank.
This will search for call numbers beginning with
5 (to keep the list manageable).
62
Here are 148 vnunk books that can be batch
changed to vnnf.
Choose other call number ranges such as j 3 or
F/A and operate on them.
63
The End