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_at_uchicago.edu
2

• Or,Now that Ive 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
  cant be done through ILLiad client or web
  reports.
• Need to provide reports to those who dont know
  how to use ILLiad or dont 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 in Excel Format
Report Exported to Excel
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 ApproachSchedule 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 days ILL activity.
16
Report for Acquisitions, recommending purchase of
itemsthat couldnt be obtained through ILL.
17
(No Transcript)
18
Report showing work performed by borrowing staff.
19
(No Transcript)
20
Report showing work performed by lending staff.
21
(No Transcript)
22
Report showing Direct Request activity.
23
(No Transcript)
24
Report showing users who should no longer be
blocked.
25
(No Transcript)
26
Report showing users who need to be charged fines
for late returns.
27
(No Transcript)
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

1. Create MS Access queries and reports
2. Create MS Access macro to output report as MS-DOS
   text.
3. Create batch file that runs macro and emails
   report using Blat, a free email utility.
4. 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/)
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 NoThis tells MS
Access to suppress messages and warnings that
would typically require a user response.
47
Second Step OpenQueryThis runs the make
table query we created.
48
Third Step OutputToThis 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.
51
Macro is now ready to run.
52
(No Transcript)
53
The illcancels.txt file has been created.
54
Content of illcancels.txt
55
Running the macro a second time will produce a
warning.
56
Even though the macro has been set to suppress
system messages, it will not overwrite existing
files. This illcancels.txt file will need to be
deleted before running the macro.
57
Step Three

• Create Batch File that runs macro and emails
  report using Blat, a free, command-line, email
  utility.

58
Download Blat from www.blat.net.
59
Installing Blat

• Place blat.exe, blat.dll, and blat.lib somewhere
  in your command path (e.g., c\Windows)
• At a Windows command prompt, type Blat -install
  ltserver addrgt ltsender's addrgt lttry n timesgt
  ltportgt ltprofilegt ltusernamegt ltpasswordgt(
  Values in square brackets that dont apply to
  your situation can be replaced with -)
• ExampleBlat install smtp.uchicago.edu
  d-larsen_at_uchicago.edu - - - larsen opensesame

60
Create a Text File
61
Give file a name with a .bat or .cmd
extension, which makes Windows treat it as an
executable batch or command file containing a
series of Windows commands.
62
The first line will delete any pre-existing
illcancels.txt file, using the del command.
63
The second line calls MS Access from the command
line, specifying the file to open and the macro
to run (designated by the /x switch).
64
The third line commands Blat to send the report
generated by the Access macro by email to the
specified recipients.
65
The batch file can be run by double-clicking it.
66
Command window that opens when file is run.
67
Success! Batch file generated report and emailed
it.
68
Final Step

• Use Windows Task Scheduler to run batch file on a
  specified schedule.

69
Open Scheduled Tasks in Windows Control Panel.
70
Choose Add Scheduled Task andfollow wizard to
schedule runningof batch file.
71
(No Transcript)
72
Use Browse to find batch file.
73
(No Transcript)
74
(No Transcript)
75
(No Transcript)
76
(No Transcript)
77
(No Transcript)
78
(No Transcript)
79
(No Transcript)
80
(No Transcript)
81
Batch file will nowrun automatically
atspecified time, as long as computer is
running. (It will run if you log off, but not if
you shut down.)
82
Scheduled tasks can berun manually by right
mousing and choosing the Run command.
83
(No Transcript)
84
(No Transcript)
85
It works!
86
Questions?

• For more information
• David Larsen
• d-larsen_at_uchicago.edu