Customized Reports

1. Customized Reports - Larry Deck - Library Systems Office, McGill

2. Customized Reports Larry Deck Library Systems Office McGill

3. ALEPH and reports • Data from GUI clients • ‘Canned’ Web Services reports • UTIL-S reports Customized Reports - Larry Deck - Library Systems Office, McGill

4. Why customize reports? • You may want to . . . • print a list of data not displayed as a list in the GUI • get a total of some amount not totaled in prefab reports • filter/sort by your own criteria • add data at will Customized Reports - Larry Deck - Library Systems Office, McGill

5. What I mean by ‘custom’ Customized Reports - Larry Deck - Library Systems Office, McGill

6. Oracle connection options • Windows-side: • third-party report tool through ODBC-SQL Net (Crystal, Access) • Win SQL*Plus or Oracle Reports client through SQL*Net • Unix-side • SQL*Plus Customized Reports - Larry Deck - Library Systems Office, McGill

7. Customized reports can . . . • Supplement the GUI clients • Derive statistical information • Validate data ex-post-facto • Extract data for export to other systems Customized Reports - Larry Deck - Library Systems Office, McGill

8. Supplementing the GUI / Services • Reporting on data not displayed in GUI • example: tracking renewals on loans • Reporting on data badly formatted in ‘canned’ reports: • example: columnar report on budget allocations and expenditures • example: reports on security proxies Customized Reports - Larry Deck - Library Systems Office, McGill

9. New statistical reports • Patrons who have registered an email address, by type • Loans • from a circulation desk, or from self-check • Renewals • by staff or through the web • Journal subscriptions costing > $5,000 Customized Reports - Larry Deck - Library Systems Office, McGill

10. Validating data • Processed items reset to ‘In Transit’ • Orders & transactions with currency ‘ESB’ • Bad ‘GST override’ codes Customized Reports - Larry Deck - Library Systems Office, McGill

11. Extracting data for export • Data for patron record updates • List of active serial titles for CISTI Source • “Fine Flag” report Customized Reports - Larry Deck - Library Systems Office, McGill

12. Developing reports • What are the data needed, and what are the criteria? • Where are the criteria and data hiding? • Two or more tables? Where and how to join? • [ Prototype the query ] • Output correct? • Format of output okay? • Finalize and document query Customized Reports - Larry Deck - Library Systems Office, McGill

13. Our “Fine Flag” Report • Registrar’s needs list of student numbers, one per line, of students owing more than $30.00 to the library, in order to block those delinquent individuals from graduating Customized Reports - Larry Deck - Library Systems Office, McGill

14. How fine info appears in GUI Customized Reports - Larry Deck - Library Systems Office, McGill

15. Fine Flag analysis: • Data needed: • student numbers - z303_id_2 • Criteria: • students - z305_bor_type • owing more than $30.00 - z31_sum Customized Reports - Larry Deck - Library Systems Office, McGill

16. Tables for “Fine Flag” report Customized Reports - Larry Deck - Library Systems Office, McGill

17. How the fines data are stored Customized Reports - Larry Deck - Library Systems Office, McGill

18. After the report is written • Presentation to end-user • as flat file (data exports) • as Excel spreadsheets • Automation of report • as ALEPH Job Daemon job Customized Reports - Larry Deck - Library Systems Office, McGill

19. Future directions for McGill • Adding customized reports as new ALEPH Web services • Adding reports as Web pages with PHP • Direct access to queries through Excel • Crystal reports server? Customized Reports - Larry Deck - Library Systems Office, McGill

20. Customized Reports - Larry Deck - Library Systems Office, McGill