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. It’s 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 I’ll 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 haven’t circulated since May. • I bet you can come up with some I haven’t 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 Here’s 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. That’s 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, don’t 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 don’t 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. There’s 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.borrower#=borrower_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 Here’s 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.