Generating Reports from A command Line

A lot of Sage ERP MAS 500 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 2008 R2 64 bit, SQL Server 2008 R2 64 bit and Sage ERP MAS 500 v 7.4. Please see this blog if you are running Windows Server 2012 R2 64 bit, SQL Server 2014 64 bit and Sage 500 ERP v 7.8

»Need more help with Crystal Reports? Contact our team.

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 this for reports unrelated to MAS 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 Report runtime – this needs to be the actual version of the Windows Server that you will run this from
• Crystal Report (rpt file) – this is the actual Crystal Report file that you created ahead of time
• MAS 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 MAS 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 the Crystal Runtime files. You can download the files at the following locations.
32-bit http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_redist_install_32bit_13_0_1.zip
64-bit http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_redist_install_64bit_13_0_1.zip

You are able to run this from the Sage ERP MAS 500 server or you can run this from another server against the MAS 500 server. So for my server configuration above if I place this on my server I will need the 64 bit version of the Crystal Report runtime. I could use the 32 bit version of Crystal Report runtime but I would need to have another server to run this from.

Install the appropriate Crystal Report runtime to your selected server. The zip file also contains information about the parameters you pass to the crexport.exe runtime engine.

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 formatted with the CustList.RPT format.

Here is the command line I used:

C:crexport.exe -F "C:CustList.rpt" -U "USER" -P "PWD" -S"SERVER" -D"mas500_app" -O "C:CustList.pdf" -Epdf -XFile -N1

Explaining the command syntax above:

• C:crexport.exe     : This is the drive letter where the exe is running from plus the runtime filename
• -F "C:CustList.rpt"     : This is the name of the Crystal Report to use for formatting
• -U "USER"         : This is the MAS 500 username to connect with
• -P "PWD"        : This is the user password
• -S"SERVER"        : The name of the SQL Server
• -D"mas500_app"    : The database name
• -O "C:CustList.pdf"    : The output path and file name
• -Epdf            : Export file type, in this case it is a PDF (you can change this to any Export file type
• -XFile            : To a file
• -N1            : Number of copies

NOTE: The space after the file tags (-F and –O) matter. Without it you will get an invalid report file message. You will also need spaces after the –U and –P tags, but not the –S or –D tags. Without that exact configuration it would get an invalid connection message.

This is not as complex or flexible as Knowledge Sync for Sage ERP MAS 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 CRExport.bat. The bat file contains the following commands. Then we just schedule the CRExport.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 –J is the actual value for the –A parameter. This would be the actual value of a SPERID to create results for. 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:Mas500ReportsCrystalExportcrexport.exe -U"USER" -P"PWD" -S"SERVER" -D"mas500_app" -F"c:Mas500ReportsSOReportsShipToBySalesSched.rpt" -O"c:Mas500ReportsCrystalExportShipToBySales-SPERID1.pdf" -Epdf -XFile -N1 -A"Salesperson" -J"SPERID1"

c:Mas500ReportsCrystalExportcrexport.exe -U"USER" -P"PWD" -S"SERVER" -D"mas500_app" -F"c:Mas500ReportsSOReportsSalesOrderBySource.rpt" -O"c:Mas500ReportsCrystalExportSalesOrderBySource-SPERID1.pdf" -Epdf -XFile -N1 -A"Saleperson" -J"SPERID1"

c:Mas500ReportsCrystalExportcrexport.exe -U"USER" -P"PWD" -S"SERVER" -D"mas500_app" -F"c:Mas500ReportsSOReportsShipToBySalesSched.rpt" -O"c:Mas500ReportsCrystalExportShipToBySales-SPERID2.pdf" -Epdf -XFile -N1 -A"Salesperson" -J"SPERID2"

c:Mas500ReportsCrystalExportcrexport.exe -U"USER" -P"PWD" -S"SERVER" -D"mas500_app" -F"c:Mas500ReportsSOReportsSalesOrderBySource.rpt" -O"c:Mas500ReportsCrystalExportSalesOrderBySource-SPERID2.pdf" -Epdf -XFile -N1 -A"Saleperson" -J"SPERID2"

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

mailsend -f SPERID1email@DOMAIN.com -d DOMAIN.local -smtp MAILSERVERNAME -t SENDERNAME@DOMAIN.com -sub "Sales Order By Source Report" -a SalesOrderBySource-SPERID1.pdf:application/pdf

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

mailsend -f SPERID2email@DOMAIN.com -d DOMAIN.local -smtp MAILSERVERNAME -t SENDERNAME@DOMAIN.com -sub "Sales Order By Source Report" -a SalesOrderBySource-SPERID2.pdf:application/pdf