Crystal Reports Tips: Generate Reports Automatically

A lot of Sage 500 ERP clients ask us how they can have some reports generated automatically, put on a schedule and email them to team members. This is what I am going to discuss in the post. For this posting I am running on Windows Server 2012 R2 64 bit, SQL Server 2014 64 bit and Sage 500 ERP v 7.8. If you are running older versions of Window Server please see the blog Windows Server 2008 R2 64 bit, SQL Server 2008 R2 64 bit and Sage ERP MAS 500 v 7.4.

Before I go any further I want to provide a disclaimer. Configuring some of these items requires that you take additional steps to secure your database and network to ensure adequate protection. You could use this process for reports unrelated to Sage 500 as well. I am just using this based on where we have used it before.

What we are doing here is creating a Crystal Report format that we wish to run to compile information for distribution to team members, etc. This could be a sales order listing by Salesperson sent to each salesperson, an AR Aging Report by Customer for your Salespeople or something as simple as a Customer list. We will be using this in our example. As you will see later the output can be various formats as supported by Crystal Reports.

You will need the following items to accomplish this task.

• Crystal Reports Ninja – this is a free utility that can be downloaded from GitHub: https://github.com/rainforestnet/CrystalReportsNinja
  • Click the Deployment link and download the following:
    • CRforVS_redist_install_32bit_13_0_20.zip
    • CRforVS_redist_install_64bit_13_0_20.zip
    • CrystalReportsNinja.exe
• Crystal Report (rpt file) – this is the actual Crystal Report file that you created ahead of time
• Sage 500 user account – this is the user that will be associated with the command line syntax to run the report, I recommend you create a Sage 500 user and make this user a Read Only user as well as limit what this user has access too based on what they need for the reporting process

First you will need to install the Crystal Runtime files. Install both the 32 and 64 bit versions from the zip file downloads above.

You are able to run this from the Sage 500 server or you can run this from another server against the Sage 500 server. In this example, I installed these runtimes on the Sage 500 SQL server.

The downloaded CrystalReportsNinja.exe file should be placed in a directory on the SQL Server. (i.e. C:\ReportsExport). The download link above also provides help information on the syntax options for calling the exe.

The way we use this is to create single command line jobs that we schedule in Windows Task Scheduler. We create one job per report. There are many ways this can be accomplished and that is really a matter of preference and beyond the scope of this blog article. Again the key is to keep it simple.

In our example we are running a Customer Listing which is created with Crystal Reports and called CustList.RPT.

Here is the command line I used:

C:\ReportsExport\CrystalReportsNinja.exe -F “C:\ReportsExport\CustList.rpt” -O “C:\ReportsExport\CustList.pdf” –E pdf

Explaining the command syntax above:

• C:\ReportsExport\CrystalReportsNinja.exe     : This is the drive letter where the exe is running from plus the runtime filename
• -F “C:\ReportsExports\CustList.rpt”     : This is the name of the Crystal Report to use for formatting
• -O “C:CustList.pdf”    : The output path and file name
• -E pdf            : Export file type, in this case it is a PDF (you can change this to any Export file type

NOTE: The space after the file tags (-F and –O) matter. Without it you will get an invalid report file message.

This is not as complex or flexible as Knowledge Sync for Sage 500 but it does provide a nice alternative. Obviously you can use Sage MI, Crystal Web Enterprise or SQL Reporting Services as other alternatives.

Here is an example of a bat file solution. We have used this to as well. In this example we have created a bat file called CrystalReportsNinja.bat. The bat file contains the following commands. Then we just schedule the CrystalReportsNinja.bat file to run from Windows Task Scheduler. This runs all the commands one at a time. This example uses mailsend which is using SMTP and assumes you have a Mail such as Outlook Express or Outlook. This can be used to track all of the sent email this way. You can use other utilities to perform the mail send, some of which do not require the use of a client as described here. One that comes to mind is blat but there are many others and I am promoting one over the other just what works in your situation.

It first deletes all the PDF files in the current folder. This is to prepare for the new files that will be generated. The next commands actually create the extract files in a PDF format. The SPERID in these examples would actually contain the Salesperson ID. You can see 2 new parameters in this example than explained earlier. The –a represents the name of a Parameter attribute in this case Salesperson that you are passing into the Crystal Report. The example below creates 2 different reports for each SPERID. After the reports are created they are then emailed to the appropriate Salesperson. For the mailsend command you would place the corresponding salesperson email address along with replacing the “DOMAIN” with your actual Domain name as well as “MAILSERVERNAME” with your mail server name. The –f is forward, -d is Domain, -smtp is the mail server, -t is the sender, -sub is the email subject and –a is the attachment along with the :application/pdf to describe what is being attached.

del *.pdf

C:\ReportsExport\CrystalReportsNinja.exe –F ”C:\ReportsExport\ShipToBySalesSched.rpt” –O ”C:\ReportsExport\ShipToBySales-SPERID1.pdf” –E pdf –a ”Salesperson:SPERID1″

C:\ReportsExport\CrystalReportsNinja.exe –F ”C:\ReportsExport\ShipToBySalesSched.rpt” –O ”C:\ReportsExport\ShipToBySales-SPERID2.pdf” –E pdf –a ”Salesperson:SPERID2″

mailsend -f SPERID1email@DOMAIN.com -d DOMAIN.local -smtp MAILSERVERNAME -t SENDERNAME@DOMAIN.com -sub “Shipped Orders Report” -a ShipToBySales-SPERID1.pdf:application/pdf

mailsend -f SPERID2email@DOMAIN.com -d DOMAIN.local -smtp MAILSERVERNAME -t SENDERNAME@DOMAIN.com -sub “Shipped Orders Report” -a ShipToBySales-SPERID2.pdf:application/pdf