Automating Healthcare
Solving business problems with savvy automation

Reporting on Full Auto

Business problem
There's nothing more wasteful of an analyst's time than manually rerunning the same reports — over and over again. Routine reports should always be fully automated. But what is the best way to automate a wide variety of reports, many of them driven by variables such as department number or clinician name?

"Wild card" reports
Reports that are not driven by parameters are relatively simple to deploy; just schedule them for production (Building a Data Portal). Many reports, however, are driven by parameters such as department name or number. In these cases, a single report definition may generate upwards of 750 individual files, each with separate permissions.

For a process this complex, we needed a homegrown solution. We built something we call the "wild card" process, with four basic components:

  • Document profile
  • Security profile
  • "Wild card" profile
  • "Wild card" details (one record per parameter value)

Document profile
Every report that uses parameters has a document profile (shown below). Among other things, this profile defines:

  • the folder where the reports will be stored;
  • the category where the reports appear on the intranet;
  • whether existing reports are replaced or archived;
  • any security profile; and
  • whether to notify users of the report via e-mail.

Instructions for using parameter values may be exposed or hidden by the user at any time:

Security profile
The security profile, discussed in more detail in Transparent Security and Permissions, identifies the type of security applied to this report. In the example shown below, permissions are granted to the responsible manager for each department, as well as an e-mail distribution list and certain individuals:

The "wildcard security parameter" offers a number of different types of parameters:

"Wild card" profile
Each parameter-driven report is listed in a table of these reports. This table contains certain meta-data about the report. The two most important items are:

  • Grouping ID (each group of reports is run on the same schedule by a single script)
  • Export type (Acrobat, Word or Excel)

"Wild card" details
For every instance of a parameter-driven report, certain meta-data is stored in another table. For instance, in the example above, there are several hundred departments with staff who are eligible for fit testing. Each department's report is be listed in the wild card details table, with these items to be used by the "wild card" script:

  • Title parameter (department number in this example)
  • Security parameter (department number in this example)
  • Title with parameter (example shown below in yellow)
  • Filename with parameter (example shown below in green)

"Wild card" script
The document profile, security profile, wild card profile, and wild card details all come together in the wild card script. There are actually several dozen of these scripts, each customized for a group of reports and scheduled to run on a specific frequency.

The script connects to Crystal Enterprise to apply the correct report definition, then runs a series of stored procedures in the Backbone database to cycle through the wild card details table, creating a separate report for each record in the table and storing the report in the correct location on the intranet. If the reports are not replacing existing reports, the new reports are automatically added to the Backbone database with the correct permissions.

"Crawler" script
Many reports do not use parameters, and can be scheduled to run using Crystal Enterprise. Each analyst manages his/her own report definitions in Crystal Enterprise. Each report definition is named to match the document profile for that report (see above). Reports are automatically output to that analyst's folder on the server.

Every night, a script crawls through all the analysts' folders, looking for new reports. The "crawler" script:

  • takes the name of the report as a variable,
  • uses that variable to pull relevant data from the document profile and (if indicated) security profile in the Backbone database,
  • copies the new reports to the correct folder on the intranet,
  • adds the new report to the Backbone database with the appropriate permissions,
  • moves the new reports to an archive folder for audit purposes, and
  • repeats the process for the remaining folders.

Lessons learned

  • Reporting tools can't do everything. Homegrown automation may be required to deliver reports in the most user-friendly manner.
  • Building the profile infrastructure to support the automation was the hard part. The actual scripting is relatively straightforward by comparison.
  • The right infrastructure can provide tremendous power and flexibility for automatic report delivery.
  • Surprisingly, a relatively complex process is pretty reliable, because each of the parts is well-thought out and as simple as possible.

Posted 1 April 2008


Custom Applications
ADT Event Alerts
Clinical Operations

Integrated Clerkship

On-call Schedules
People Profiles
Chronic Disease

Security Badge Requests
Charge Capture
Mental Health Treatment
      Plan Tracking

Earned Time Calculator

Supervisory Tree
E-mail Distribution Lists
User Access Requests
HR Requests
Employee Health &

Interpreter Dispatching
Generic Patient Registry
Conference Room

Tuition Reimbursement
Equipment Rental
Code Cart Tracking
Nursing Audits

Show me the data
Growing a Data

Building a Data Portal
Reporting on Full Auto

Intranet Design
Driving With Databases
Speeding with Static

Transparent Security
      and Permissions

Redesigning the

Who works here?
Organizational buckets
System access: Who
      has what?

System access: Use
      it or lose it

Integrating Security

Integrating Provider

Creating A Supervisory

Data Quality Dashboard


RSS Feed