Crystal Reports and Sage CRM

24 Apr Crystal Reports and Sage CRM

With Sage CRM now supporting browsers other than Internet Explorer, then can be issues when trying to run Crystal Reports. The issues stems from the fact that Sage CRM uses an ActiveX component to display the report. This ActiveX component is quite powerful giving you access to export the report to multiple formats, search for text, change the display, etc. Only problem is that it is limited to Internet Explorer only.
I personally have a problem with using IE and use Chrome on a daily basis. My main issue with IE is with the limited support for HTML5, particularly in older version of IE. I will concede that there has been an improvement in the latest version and I probably could use it without any problems, but I’ve now moved to Chrome and haven’t come up with a good reason to not to use it. There is a cool little website at html5readiness.com which outlines which features of HTML5 and CSS3 have been implemented in the main stream browsers. Speed is also another consideration and I think the image below sums it up perfectly.

Now that I have had my little rant, I will move on to what can be done with Crystal and Sage CRM. In this blog post I will show how to generate a Crystal Report while using Chrome. Unfortunately we do have to forgo some of the functionality that is available through the ActiveX control, however I believe that being able to generate the report in the first place is a real plus.
When trying to run the report in Chrome you should see something like this appear.

This is not really all that useful. As I mentioned before this is a problem with Chrome not being able to use the ActiveX components, or at least cannot use it when using a vanilla installation. There are a couple of options available here to get it working:

1. Use an add in to allow ActiveX components to run, for example you can use IE Tab or ActiveX for Chrome
2. You can change the way the report is generated and presented to screen.

Both options are valid solutions to the problem; however point 1 requires that software is installed on the workstations for it to work correctly. This blog post will go through point 2 and the changes that are required for a system wide solution.
To make this work you will need to edit the standard Crystal Report asp files that are provided with an installation of Sage CRM. These files will be found in {installation directory}\crm\wwwroot\reports\CrystalReportViewer. In this directory you will see the following files:

1. Cleanup.asp
2. RDCrptserver11.asp
3. RunReport.asp

The only file that needs to be modified to work is the RunReport.asp. The modifications are reasonably straight forward and are documented below.

1. Backup the RunReport.asp
2. Find the linesession("oRpt").EnableParameterPrompting = False

   Add the following code:dim CrystalExportOptions
set CrystalExportOptions = session("oRpt").ExportOptions
FileNameExport = year(date())&"-"&month(date()) & "-" & day(Date()) & "-" & hour(Time()) & "-" & minute(Time()) & "-" & second(Time())
FileName = Server.MapPath("../../") + "\Temp\" + FileNameExport + ".pdf"
CrystalExportOptions.DiskFileName = Server.MapPath("../../") + "\Temp\" + FileNameExport + ".pdf"
CrystalExportOptions.FormatType = CInt(31)
CrystalExportOptions.DestinationType = CInt(1)
session("oRpt").Export False
3. Find the linesset session("oPageEngine") = session("oRpt").PageEngine
End If
4. Add the following code at the endResponse.Redirect("../../Temp/" + FileNameExport + ".pdf")

dim fs
Set fs=Server.CreateObject("Scripting.FileSystemObject")
if fs.FileExists(FileName) then
fs.DeleteFile(FileName)
end if
set fs=nothing
5. This step is optional as it will run with the code still there, however it is no longer needed. To clean it up remove the script:<script language="javascript">
function getQueryParam( name )
…
</script>Leave the script that contains the function CallDestroy()

This will now produce a report below.

Now because coding can be loads of fun, I have provided the modified file RunReport available for download. Feel free to use it at your own site(s), the only thing I ask is that if you come up with any cool features let me know so I can share it around.
This method does make use of the Temp directory that is within the Sage CRM website, however the files are immediately deleted after they are returned to the client. This method means that the reports are never stored in the CRM library, however the method for producing reports that are stored against the company or other entities will be investigated in future blog posts.
Some things that I have noticed when doing this is that you don’t need to have the Crystal Report Server on the CRM server itself, it will run using the Crystal Runtime files. This works really well in the situation where you have Sage CRM integrated with Sage ERP 300 as they are provided as part of the workstation installation. One other benefit that I have noticed is the speed at which the report is generated, it is surprisingly quick. I wish I could say that this was by design, but unfortunately it was just one of those items that was just plain good luck.
Please note that the change to these files won’t be supported by Sage, however we do have this running live at a lot of our sites with no issues. These files have been tested with Sage 7.1 and 7.2.
The next post will concentrate on how to generate a Crystal Report from the various screens within Sage CRM. This will concentrate mainly on using button groups for the standard entities and using the CRM.AddButton function for asp pages.