Friday, May 2, 2014

Automating Crystal Reports

Crystal Reports is a very useful tool for businesses, but automating Crystal Reports can be costly if you are using tools like Crystal Enterprise or one of the other solutions/services that are out there. In this article, I am going to show you how to automate Crystal Reports for free using VBScript, Command Line code and a free eMailing solution called Febooti. Febooti must be installed for this to work properly. If you have another free emailing solution, feel free to use it of course. I choose Febooti because it's my personal favorite/preference to work with.
Firstly, with this solution the Crystal Report(s) have to have their record selection formulas hard-coded with no parameters if these are to run automatically with no human interaction. Once your report has been set up to produce the desired results just by running the report, we are ready for the next steps to automate the running, exporting and eMailing of the data.
The next step is to set up the VBScript file to run your report and export it...the following two links are to the VBScript code(s) on pastebin.com:
Crystal Report - Export to Excel
Crystal Report - Export to PDF
Crystal Report - Export to Text Delimited
Crystal Report - Export to CSV
Copy and paste the desired code and change the report paths and database log on information accordingly. Now we can create our Powershell or CMD Batch file to run our VBScript, rename the report, eMail it and archive it (if desired, any of those steps can be omitted or enhanced of course: it all depends on exactly what you want to accomplish).
Crystal Report - Run, eMail and Archive CMD
Change the batch file code accordingly - paths, output filename and extension (excel/xls in this example, change "xls" to "pdf" of course if using the PDF version), etc to accommodate your specific needs. Once you've saved your batch/powershell file, schedule it using the Windows Task Scheduler and you're all set!
Stay tuned for more helpful hints, code and projects in the making! C/O SkiyeLab