How Can I Automate a Crystal Report?

Posted on by Adam Patrick

Every day at Deft Flux, we are mapping unique paths from problem to solution, and we like to share them with you from time to time.  Today, our question is a common one — “How can I automatically run and e-mail a Crystal Report?”

Let’s consider this case where you want to schedule a report to be run and delivered to some users via e-mail.  For our readers who use Epicor, scheduling a report is as simple as setting up the system agent, loading the report, selecting the schedule, and pressing submit.  For the e-mailing, Epicor offers a module called Advanced Print Management (APM) and it would be the simplest way to e-mail reports in Epicor.

It may be, however, that your company does not have a license for APM or you may need to report on data outside of Epicor — perhaps another relational database or an XML file.  In either case, you want to e-mail the report to a set of users.  Let us use the external XML file as our example.  Let us say that another program drops a sequentially numbered XML file into a given directory on the network, and each time it does, you want to use that file to run a Crystal Report and e-mail the output to the user(s).  To do this, we could take the following approach:

  1. Create a User Defined Code in Epicor (or other ERP system) to store the e-mail addresses to which this report should be sent.  That makes it easy to change them later on.
  2. Create a SQLServer Integration Services (SSIS)[1] project to run the report and e-mail it.
  3. At the first step in the SSIS work flow, go to the database and the User Defined Codes table to pick up the list of e-mail addresses to which this report should be sent.
  4. Use an SSIS “For Each” loop to detect and process the XML file(s) in question.
  5. Inside the for each loop:
    1. Create a .NET program to run the Crystal Report, create the report from the XML file, and then export the output to a PDF file.
    2. E-mail the PDF to the addresses we found at step #3.
    3. Archive or delete the XML file.

Here we have a common business problem with an elegant solution, one that is quick and easy to implement, with reduced future maintenance.  We shall leave the details of the implementation up to your specific environment, but when you need assistance, we are here to help.

This is how Deft Flux is leading the way back to elegant information design.  If you have a question for Deft Flux, ask us on Twitter @DeftFlux or Contact us directly.

 

1. In this case, we choose SSIS because it offers built-in error handling and easily integrates to SQLServer Agent.  We could almost as easily write the same program using .NET or even C++.