Distributing ILLiad Reports Created Using Microsoft Access

1. Distributing ILLiad ReportsCreated Using Microsoft Access David Larsen Head of Access Services University of Chicago Library d-larsen@uchicago.edu

2. Or,“Now that I’ve taken the Generating Reports in OCLC ILLiad Class,how do I get my reports to those who need to see them?”

3. Why Distribute MS Access Queries and Reports? • Need to summarize and display data in ways that can’t be done through ILLiad client or web reports. • Need to provide reports to those who don’t know how to use ILLiad or don’t have access to client. • Want to let individuals see data but not alter information in tables. • Want to automatically “push” information to those who need it or allow them to “pull” it themselves.

4. Two Approaches • Provide users with the actual MS Access “.mdb” files and a simplified interface that allows reports to be run as needed without training in MS Access. • Schedule reports to be automatically run and sent via email to interested individuals.

5. Example of First Approach ILLiad Borrowing Report • Provided to bibliographers • Shows loan requests for borrowing • Provides ability to specify academic department and date range

6. When Borrowing Report is opened, users only see a simple formwith a drop-down menu, two text boxes, and two buttons.

7. Users can select department from a drop-down box thatdisplays a list generated from a dynamic ILLiad query.

8. Users can specify a date range or accept the default daterange (previous week).

9. Example ofResulting Report

10. Reports can be sent to Excel, Word, or Printer.

11. Report Exported to Excel Report in Excel Format

12. Benefits of this Approach • Users can run reports at any time • Can export to Excel, HTML, or Rich Text • Can create interface that allows use by those untrained in MS Access or ILLiad • Users can choose from a wide range of options using drop-down and text entry boxes

13. Disadvantages of this Approach • Users need Microsoft Access • Need to set up ODBC connections on all machines that run reports • Firewalls may prevent access to ILLiad server • Underlying tables remain accessible to users with knowledge of Access • Configuring easy-to-use interface requires lots of time and skill • Users need to remember to run reports

14. Another Approach:Schedule reports to automatically run with results sent via email

15. Imagine opening your email each morning and finding a half dozen ILLiad Reports in your Inbox that summarize the previous day’s ILL activity.

16. Report for Acquisitions, recommending purchase of itemsthat couldn’t be obtained through ILL.

18. Report showing work performed by borrowing staff.

20. Report showing work performed by lending staff.

22. Report showing Direct Request activity.

24. Report showing users who should no longer be blocked.

26. Report showing users who need to be charged fines for late returns.

28. Benefits of This Approach • Users don't need to remember to run reports • Most find email a convenient way to receive information • Eliminates security and firewall concerns • Users don't need to have MS Access or configure ODBC settings • Don't need to spend lots of time simplifying MS Access interface • Reports can be scheduled to run during closed times, reducing load on server

29. Disadvantages of This Approach • Recipients do not have control over report scheduling or content • Report criteria need to be configured in advance (no drop-downs or text entry boxes) • Need to consider privacy implications of using email if reports contain sensitive data

30. How to Schedule Reports to Automatically Run with Results Sent via Email

31. Four Steps • Create MS Access queries and reports • Create MS Access macro to output report as MS-DOS text. • Create batch file that runs macro and emails report using Blat, a free email utility. • Use Windows Task Scheduler to run batch file at regularly specified times.

32. Step-by-Step Example Creating, scheduling, and emailing a report to Acquisitions recommending that the Library purchase recent imprints that could not be obtained through ILL

33. Step One Create Microsoft Access queries and reports. (Just like Stephanie Spires teaches inGenerating Reports in OCLC ILLiad)

34. Create a Microsoft Access File

35. Create a “make table” query that outputs results to a table called “ILL Cancels”

36. This query produces a list of all borrowing loans from the past week with the “Cancelled by ILL Staff” status, an imprint date in this millennium, and a “Reason for Cancellation” matching the specified criteria.

37. Criteria for the “Reasons for Cancellation” field.

38. Complete SQL statement for the query.

39. Create a report based on the “ILL Cancels” table created by the query.

40. PossibleReport Design

41. Avoid random blank lines by following the advice in Microsoft Knowledge Base Article 208436(http://support.microsoft.com/kb/q208436/) Fraction Decimal Equivalent 1\8 .125 1\4 .25 3\8 .375 1\2 .5 5\8 .625 3\4 .75 7\8 .875 Set the Height property of a report's detail section to a value divisible by .125 inches. For example, if the detail section's Height property contains a fractional number, set the fractional portion of this property value to one of the following decimal equivalents:

42. Report Preview

43. Step Two Create Microsoft Access macro that will run report and output results to a text file.

44. Create a new macro called “cancels”

45. Macro consists of four steps that automate running of query and output of report.

46. First Step: “SetWarnings” to “No”This tells MS Access to suppress messages and warnings that would typically require a user response.

47. Second Step: “OpenQuery”This runs the “make table” query we created.

48. Third Step: “OutputTo”This outputs the “ILL Cancellations” report as an MS-DOS text file named “illcancels.txt” in the “c:\ill” directory.

49. Third Step (Part Two):Select the “Conditions” option under the “View” menu. Then enter a logical condition that tells MS Access to only output the report if the report contains data.

50. Fourth Step: “Quit” Shuts down MS Access at end of macro.