Creating Custom Views in Horizon

1. Creating Custom Views in Horizon Presented by Gem Stone-Logan IT Application Engineer, Weld Library District gstone-logan@weld.lib.co.us at Colorado Horizon User Group meeting on May 29, 2007

2. What we will cover What are MQ Views Parts of a MQ View Basic Editing Creating Alternate Views Creating MQ Views from Scratch Securing MQ Views

3. MQ Views What are MQ Views? What can they do?

4. Warnings! • SirsiDynix considers MQ Views to be custom work. Thus, if you mess up, they’ll probably charge you to fix the problem. • If possible, test your custom views in a “training” or “test” database before changing the production Horizon database. • SirsiDynix recommends making sure that your custom MQ Views are read only. • Don’t change existing SirsiDynix views. Instead, copy them and then create alternative view sets.

5. Opening an MQ View

6. Opening an MQ View

7. Opening an MQ View

8. Opening an MQ View

9. Parts of the MQ View Screen General Information

10. Parts of the MQ View Screen General Information (continued)

11. Parts of the MQ View Screen List View

12. Parts of the MQ View Screen Edit View

13. Parts of the MQ View Screen Edit Links

14. Parts of the MQ View Screen Column Types If Horizon didn’t provide a translation for dates and other fields, the date for an item created on April 7, 1988 would appear as the number 6671 instead of a human readable date like 04/07/88.

15. Parts of the MQ View Screen Values

16. Basic Editing of an MQ View Displaying a column by default

17. Basic Editing of an MQ View Changing the default number of characters that display

18. Creating Alternate Views Copying an existing view – pt 1

19. Creating Alternate Views Copying an existing view - pt 2

20. Creating Alternate Views Copying an existing view - pt 3

21. Creating Alternate Views Creating a “View Set” – pt 1

22. Creating Alternate Views Creating a “View Set” – pt 2

23. Creating Alternate Views Creating a “View Set” – pt 3

24. Creating Alternate Views Creating a “View Set” – pt 4

25. Creating an MQ View from Scratch Creating an SQL View – pt 1 • Before creating an SQL View be aware that . . . • SirsiDynix will not support any custom SQL views you create or tables/views you accidentally delete. • You should be comfortable creating SQL queries • You will need SQL Advantage or some other program to connect to your database.

26. Creating an MQ View from Scratch Creating an SQL View – pt 2 Example SQL Query SELECT isbn,bib#, count(*) duplicates FROM isbnex_inverted GROUP BY isbn HAVING count(*) > 1

27. Creating an MQ View from Scratch Creating an SQL View – pt 3 Syntax of an SQL View CREATE VIEW [view name you pick] AS [select statement you created previously] Example Creation of a View CREATE VIEW isbn_duplicates_gs AS SELECT isbn,bib#, count(*) duplicates FROM isbnex_inverted GROUP BY isbn HAVING count(*) > 1

28. Creating an MQ View from Scratch Creating an SQL View – pt 4 Granting Permissions for Horizon Users to See the View GRANT ALL ON [view name you picked] TO [user group] Example: GRANT ALL ON isbn_duplicates_gs TO staffgroup

29. Creating an MQ View from Scratch Creating an MQ View – pt 1

30. Creating an MQ View from Scratch Creating an MQ View – pt 2

31. Creating an MQ View from Scratch Creating an MQ View – pt 3

32. Creating an MQ View from Scratch Creating an MQ View – pt 4

33. Creating an MQ View from Scratch Creating an MQ View – pt 5

34. Creating an MQ View from Scratch Finding a newly created MQ View – pt 1

35. Creating an MQ View from Scratch Finding a newly created MQ View – pt 2

36. Creating an MQ View from Scratch Finding a newly created MQ View – pt 3

37. Creating an MQ View from Scratch Finding a newly created MQ View – pt 4

38. Creating an MQ View from Scratch Dropping MQ Views and SQL View • IMPORTANT • Do not drop an SQL View or delete a MQ View unless you know EXACTLY what you are doing. Dropping or deleting an incorrect view can cause Horizon to stop functioning.

39. Creating an MQ View from Scratch Dropping MQ Views and SQL View

40. Creating an MQ View from Scratch Dropping MQ Views and SQL View Syntax of dropping an SQL View DROP VIEW [view name you pick] Example Dropping an SQL View DROP VIEW isbn_duplicates_gs

41. Securing MQ Views Securing MQ Views – pt 1

42. Securing MQ Views Securing MQ Views – pt 2

43. Securing MQ Views Securing MQ Views – pt 5

44. Securing MQ Views Securing MQ Views – pt 6

45. Securing MQ Views Securing MQ Views – pt 7

46. Securing MQ Views Securing MQ Views – pt 8

47. Additional Resources • Any Questions? Please email me at • gstone-logan@weld.lib.co.us • A copy of this presentation is located at: http://www.mylibrary.us/about_my_library/MQViewPresentation.ppt • Eric Graham’s 2005 CODI Presentation includes some information on MQ Views: http://customer.dynix.com/usergroups/conf/us/2005/files/let_sql_do_all_dirty_work-eg.doc pages 38-44 • SirsiDynix’s System Administrator’s Guide for Horizon 7.3 includes a chapter on “Introduction to Horizon Views”