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.

Browsers

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.

Image 1
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.

Image 2

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.

Detailed Change Tracking in Sage CRM

A requirement that we’ve come across a few times now is to track when a particular field value changed and what the previous value was (e.g. when did the Assigned User change for Company record X?).  A co-worker of mine came up with a method of achieving this, that involved adding only two custom tables and triggers on triggers; I still remember my feeling of admiration at such an elegant solution!

Detailed setup steps are included below, but the gist of it is:
  1. Create a configuration table
    This defines what fields should be tracked.
    Image
  2. Create a tracking table
    This will contain each recorded change.  In the screenshot below, you can see that on 30/08/2013 the Admin user (trac_CreatedBy = 1) changed the Salutation on Person 1791, from “Mr.” to “Dr.”.
    Image2
  3. Create a master trigger on the configuration table
    This trigger runs whenever the configuration table is updated, has an insert or a delete action performed on it.  This master trigger will loop through the configuration rows and create update/insert triggers on the required entities (e.g. the Company table), that will add insert/update changes to the tracking table.

Detailed Setup

I’ve bundled all the SQL commands to set this up, in this script (it’s a 7z file, so you can use something like 7-Zip to extract it):  crm-detailed-tracking-creation-sql
To start tracking fields, just edit the configuration table in SQL Server and specify:
  • Tbl_Name:  The entity table name to track
  • Action: If insert, update or both (insert,update) actions should be tracked
  • Updated_Collist:  The comma-separated list of fields that should be tracked
  • Is_Enable: Set to ‘1’ to turn it on
Once you commit your changes to the table, the master trigger will pick them up and create or re-create the defined triggers.

Next Steps

  • The configuration table could be exposed in CRM via a regular custom page, allowing CRM admins to maintain the tracking definitions
  • Create CRM reports allowing managers to see what changes have been made, to records managed by their team

 

Update

02/01/2014: This setup assumes that your CRM database has defined primary key constraints on the table that you want to track.  This assumption is correct for new installs of Sage CRM 7.2, but if you have upgraded from an older instance, the database will not have those keys defined, and you will need to set them up manually.

 

 

How do I output a link to a CRM record, in an email?

Jeff Richards describes how to do it in this post:
community.sagecrm.com/…/embedded-links-in-workflow-emails.aspx

Basically, your Email template needs to have the following text:

#recordanchor# <Anchor text goes here> #recordanchorend#

If you’re manually constructing the link (e.g. via a script), you can use the same “SessionFind” URL structure e.g.:

/CRM/eware.dll/SessionFind?Act=200&CLk=T&Key0=1&Key1=300123

The above URL will redirect the user to the Company Summary page (Act=200) for the company (key0=1) with a comp_companyid of ‘300123’ (key1=300123).

NOTE: The user will always have to login when clicking these links (even if they already have an open session).

How do I set my account to login to a custom screen?

Sage CRM allows users to customise which screen should be displayed by default, when they login.

image2013-8-7 16-35-27

But what if you want to login to a custom screen (e.g. an ASP file)?  In that case, you need to:

  1. Open the following file for editing
    \CRM\WWWRoot\InteractiveDashboard\InteractiveDashboard.asp
  2. Put in a Javascript redirect for the page you would like to redirect to e.g.:
    <script type="text/javascript">
        var strURL = window.location.href;
        strURL = strURL.replace(/\/InteractiveDashboard\/InteractiveDashboard\.asp/, '/CustomPages/CA/Dashboard/index.asp');
        strURL = strURL.replace(/\?.*(SID=\d+).*/, '?' + "$1");
        window.location = strURL;
    </script>
  3. Because the Dashboard is hardcoded to display a set of tabs at the top of the iframe, you will also need to remove those – you could do this by hiding the tabs. For example, in ‘Dashboard/index.asp’, include the following:
    <script>
    // Hide the InteractiveDashboards tabs, that are included in the parent frameset.
    $("#landingpage_top_frame", parent.document).hide();
    </script>
  4. Now, any user with “Dashboard” set as their “Log Me In To” option should be redirected to your custom page.

How do I summarize, in days, when each Opportunity was last modified?

More specifically, this information is usually intended to be outputted as an additional column, in the Opportunity pipeline screen.

How the "Days in Stage" column appears in the Opportunity pipeline.
How the “Days in Stage” column appears in the Opportunity pipeline.

To do this, you’ll need to:

  1. Modify the database view used by the Opportunity pipeline.
  2. Create a translation.
  3. Add the new column to the pipeline screen.

 

Step 1: Modify vListOpportunities

The Pipeline screen uses the vListOpportunities view, to provide the columns for it to display.  In Sage CRM, go to the Administration section, select the Opportunties entity and under the Views tab, update the view definition to include our new field:

CAST((DATEDIFF(hh, wi.WkIn_UpdatedDate, GETDATE()) / CAST(24 AS DECIMAL(18,1))) AS DECIMAL(18,1)) AS 'oppo_daysinstage',

and an inner join to the Workflow Instance table (we’re using the Updated Date on the workflow instance as an indicator of when the Opportunity was last modified; you could just use the Opportunity’s Updated Date, but then that wouldn’t include workflow progression).

INNER JOIN WorkflowInstance AS wi 
		ON wi.Wkin_CurrentEntityId = 10 
			AND wi.WkIn_CurrentRecordId = oppo_opportunityid

Your view definition should end up looking like the following:

View definition for vListOpportunities.
View definition for vListOpportunities.

Step 2: Add a translation for the new field

To display a user-friendly name for the oppo_daysinstage field, go to Administration -> Customisation -> Translations and create a new translation using the following details:

Parameters for a new oppo_daysinstage translation.
Parameters for a new oppo_daysinstage translation.

 

Step 3: Add “Days in Stage” to the Pipeline screen

For the last step, go to Administration -> Customisation -> Opportunities -> Lists and select the Opportunities List item.  Select the “Days in Stage” field in the Field drop-down, click Add and then save your changes.

Adding the "Days in Stage" field as a new column.
Adding the “Days in Stage” field as a new column.

When you go back to the Dashboard, your Opportunities Pipeline should now display this brand new field!

My CRM buttons are no longer appearing in Outlook?

If the Sage CRM plugin for Outlook is working correctly, you should see  a “File e-mail” button in your Outlook toolbar.

Sage CRM plugin buttons in Outlook
Sage CRM plugin buttons in Outlook

If not, try the following steps to get it working:

Requirements

Check that your PC has the required libraries installed.  In the Add/Remove Programs window, you should see:

  • Visual Studio Tools for Office 2010 (VSTO)
  • Microsoft Office 2007 Primary Interop Assemblies
  • Microsoft .Net Framework 4 Client Profile

We’ve also encountered issues if the user account is setup as a roaming profile.  If so, try changing the account to a local profile.

Check Trust Centre

In the Outlook client, open the Trust Centre and check if the Sage CRM plugin is listed in the Add-ins section.  If it is, make sure that it is enabled and trusted by Outlook.

Re-install the plugin

You can find the installer for the plugin in the <Sage CRM Install Directory>\CRM\WWWRoot\Plugin directory (e.g. C:\Program Files (x86)\Sage\CRM\CRM\WWWRoot\Plugin).  Run the plugin appropriate for your setup (e.g. for Exchange integration, use CRMClientExSetup.exe; for just Outlook-client integration, use CRMClientSetup.exe).

Keep in mind that the Outlook user must have local administrator permissions on their PC, the first time that they use the plugin (e.g. in Windows 7, go to the Control Panel, select Users, click on Give other users access to this computer and then add the Outlook user account as an Administrator).

If this still does not work, try logging into the PC as Administrator and re-installing the plugin.

Cleaning up the registry

If the plugin is still not working, you might need to search the Windows registry to see if there are any old or corrupt entries that are confusing the plugin.  As always, make sure to backup your registry before making any changes.

Ideally, the plugin’s registry entries should be under HKEY_CURRENT_USER\SOFTWARE\Sage\Exchange Outlook Plugin.

Outlook itself will also keep registry entries for its plugins (e.g. HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\Outlook\Addins\OutlookAddin2003).

Delete any CRM Outlook entries you find in these folders.

After that, just try searching the registry for any matches on ‘CRM’ and manually checking each.

Remove downloaded files

Check C:\Windows\Downloaded Program Files for any CRM Outlook related files (e.g. crmoutlookplugin1.dll).  Delete all of these files.

Restart the CRM server

Sometimes the plugin will be unable to connect to the CRM server (either during the install or after), due to corrupted metadata on the CRM server.  In these cases, refreshing the metadata on the CRM server (or even restarting the CRM server) will fix the issue.