Last week at Convergence I lead a session on reporting for Microsoft Dynamics CRM. At that session I’d promised to provide some more details on how you can embed SQL Server Reporting Services reports into your CRM forms. This technique improves adoption of CRM by adding context to forms and by providing a richer user experience.
To use this technique you’ll need to take the following steps (I’ll explain each in turn below):
- Create a report that can accept parameters from CRM.
- Published the report (RDL) so that it is accessible outside of the CRM application.
- Create an iframe on your form pointing to the report.
Create a Report with Parameters
The first step in the process is to create a report that is going to look good and make sense on a CRM form. I’ll let you figure out what “looks good” but for the report to make sense on a form it has to take in the context of the current record. CRM iframes can pass that context into the report via query string parameters but the report has to be built to accept those parameters. There are six parameters that you will need to add to your report. You can read about the details of those parameters in the CRM SDK or consult the table below:
To add these parameters to your report open your RDL file for editing. Within BI Development Studio right click the “Parameters” folder icon and choose “New Parameter…”. Enter the names of the parameters in the Parameter Properties dialog box with the properties as shown below.
When you’ve created entries for all of the parameters your parameter listing should like this:
Of these parameters, the most useful for adding context will be the @id parameter. This parameter is the GUID of the record currently loaded onto the form. You can use this to filter the results of the report that you’re running – either using filtering within the report or as part of your SQL query as in the following example:
SELECT customerid, owneridname, statecodename, subjectid, ticketnumber, title
FROM FilteredIncident
WHERE (customerid = @id)
The @id in the query is a reference to the id parameter, the actual value of the parameter will be substituted in when the report is run. This query retrieves the Cases (Incidents) associated with the id passed into the report. You could display these as a listing or chart within the Account form to provide an immediate view into open and closed cases for a given account.
Publish the Report
In order for your report to be viewed within a CRM form you will need to publish it to a location where it can be accessed directly from the SSRS server. (Reports within CRM are usually viewed from the CRM application server and not the SSRS server). There are a couple of ways to do this; the most straight forward way is to publish the report directly through the SSRS Report Manager. I’d recommend placing the report in the folder for the CRM organization to which it belongs.
To do that navigate to the SSRS Report Manager (usually this is http://[servername]/Reports/) and then click on the appropriate folder. An organization named “Contoso” will have its reports in a folder called “Contoso_MSCRM”. Click the “Upload File” button and upload the RDL. (For other ways to publish the report see the post How it Works: SQL Server Reporting Services and Dynamics CRM).
Note that if you try to run the report from here it won’t work properly – the parameter value isn’t being set. To get that parameter value set you’ll need to invoke the report from an iframe within a CRM form.
Create an iframe on a CRM Form
The last step of building this type of customization is the creation of the inline frame on a form. I’ll presume that you know how to edit a form in CRM, to prepare a form to display an inline report add an iframe to the form and create it with the properties as show below:
Note that the “Pass record object-type code and unique identifier as parameters” is checked. This will ensure that the parameter data is passed into the report. Note too that the “Restrict cross-frame scripting” box is unchecked. On the “Formatting” tab of this dialog (not shown) you’ll probably want to check the box “Automatically expand to use available space”. This will increase the height of the iframe so that your report displays properly.
But you aren’t done yet; the key piece of information you’ll put into the dialog box is the URL for the iframe. That URL will point to the SSRS server and to the report you published in the previous step. Those URLs take several parameters, you can get the full details from the SSRS SDK topic “Using URL Access Parameters”. If you’re not interested in the details just modify the following URL for your environment:
http://[servername]/reportserver?/[crmorganizationname]_MSCRM/[reportname]&rs:Command=Render&rs:ClearSession=true&rc:ToolBar=false
The servername, organization/folder name and report name will all need to be modified to work with your report. The query string parameters indicate how the report should be displayed, the “ToolBar=false” parameter will cause the report to display without the Report Viewer controls which is likely the effect that you want for an inline report. By checking the “Pass record object-type code…” checkbox that URL will get some additional query string parameters added to it – the ones you set your report to accept in the first step.
The result is a CRM form that can provide more information and context to users, much like the Lead form below.
Cheers,