A CRM MVP, our guest blogger David Jennaway is the technical director at Excitation. Excitation is a Microsoft Gold Partner in the UK that specializes in delivering Microsoft Dynamics CRM solutions. He has generously placed all the code for this post on CodePlex.com.
With pre-filtering, CRM offers a powerful way to produce context sensitive reports based on the selected records in a grid, or the current record in a form. In this article I’ll cover an alternative approach that can be used to display report content within an IFrame on a form, or provide a simple way to run a report from a toolbar button or menu item in CRM.
How it works
SQL Server Reporting Services provides a means for running and rendering a report by accessing a URL and passing all relevant report parameters and rendering options in the query string of the URL. CRM offers a simple means to pass the current entity type, name and instance ID as parameters on the query string when accessing a page in an IFrame, or through a toolbar button. The approach covered in this article combines these 2 features.
Creating the report
The main example I’ll use for this article is a report that will be used in an IFrame on the CRM account form. This will display a summary of the total business done with that account, summarising opportunity and case information. This example has been created for SQL 2000 Reporting Services, using Visual Studio 2003, but works identically for SQL 2005.
We will need to pass the instance ID of an account to the report as a parameter. This will be passed by the IFrame control in the CRM form, and is of the form:
?type=1&typename=account&id={5E849F23-FEEF-DA11-A04F-00065B842569}
When passing parameters to a report for URL access, the parameter name in the report must match the corresponding value on the query string. Additionally, each value on the query string must either control the report rendering (see later) or exist as a report parameter. Therefore we need to create 3 parameters for the report: id, type and typename.
As Reporting Services doesn’t offer a Guid datatype for parameters we’ll set the id parameter to be of type string. Although we don’t need to set default values, I tend to add in an id of a valid account to simplify testing in the development environment.
We can now use the id parameter in our SQL query as normal in Reporting Services
Here is the SQL statement I’m using. Note that the @accountid parameter is cast as a uniqueidentifier SQL type.
SELECT statuscodename AS Status,
SUM(CASE WHEN isnull(actualvalue, 0) <> 0 THEN actualvalue
ELSE estimatedvalue END) AS Value
FROM FilteredOpportunity
WHERE (accountid = CAST(@accountid AS uniqueidentifier))
GROUP BY statuscodename
Here is the full report rdl (I’ve kept formatting to a minimum to reduce the file size). Change the connection string in the Data Source to that of your server.
<?xml version=”1.0″ encoding=”utf-8″?>
<Report xmlns=”http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition” xmlns:rd=”http://schemas.microsoft.com/SQLServer/reporting/reportdesigner”>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<Body>
<ReportItems>
<Table Name=”table1″><Style />
<Header>
<TableRows><TableRow><Height>0.53333cm</Height>
<TableCells>
<TableCell><ReportItems><Textbox Name=”textbox2″>
<Style><FontWeight>700</FontWeight></Style>
<ZIndex>3</ZIndex><rd:DefaultName>textbox2</rd:DefaultName><Value>Status</Value>
</Textbox></ReportItems></TableCell>
<TableCell><ReportItems><Textbox Name=”textbox3″>
<Style><TextAlign>Right</TextAlign><FontWeight>700</FontWeight></Style>
<ZIndex>2</ZIndex><rd:DefaultName>textbox3</rd:DefaultName><Value>Value</Value>
</Textbox></ReportItems></TableCell>
</TableCells>
</TableRow></TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows><TableRow><Height>0.53333cm</Height>
<TableCells>
<TableCell><ReportItems><Textbox Name=”Status”>
<Style /><ZIndex>1</ZIndex><rd:DefaultName>Status</rd:DefaultName><Value>=Fields!Status.Value</Value>
</Textbox></ReportItems></TableCell>
<TableCell><ReportItems><Textbox Name=”Value”>
<Style><Format>C0</Format></Style><rd:DefaultName>Value</rd:DefaultName><Value>=Fields!Value.Value</Value>
</Textbox></ReportItems></TableCell>
</TableCells>
</TableRow></TableRows>
</Details>
<DataSetName>Excitation_Test_MSCRM</DataSetName>
<TableColumns>
<TableColumn><Width>2.5cm</Width></TableColumn>
<TableColumn><Width>2.75cm</Width></TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style><BackgroundColor>#eef0f6</BackgroundColor></Style>
<Height>1.06666cm</Height><ColumnSpacing>1cm</ColumnSpacing>
</Body>
<DataSources>
<DataSource Name=”Excitation_Test_MSCRM”>
<rd:DataSourceID>6758697f-a2f1-4a82-afda-a8f660bcc267</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Excitation_Test_MSCRM</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.25cm</Width>
<DataSets>
<DataSet Name=”Excitation_Test_MSCRM”>
<Fields>
<Field Name=”Status”><DataField>Status</DataField><rd:TypeName>System.String</rd:TypeName></Field>
<Field Name=”Value”><DataField>Value</DataField><rd:TypeName>System.Decimal</rd:TypeName></Field>
</Fields>
<Query>
<DataSourceName>Excitation_Test_MSCRM</DataSourceName>
<CommandText>SELECT statuscodename AS Status, SUM(CASE WHEN isnull(actualvalue, 0) <> 0 THEN actualvalue ELSE estimatedvalue END) AS Value
FROM FilteredOpportunity
WHERE (accountid = CAST(@accountid AS uniqueidentifier))
GROUP BY statuscodename</CommandText>
<QueryParameters><QueryParameter Name=”@accountid”><Value>=Parameters!id.Value</Value></QueryParameter></QueryParameters>
</Query>
</DataSet>
</DataSets>
<rd:SnapToGrid>true</rd:SnapToGrid><PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid><PageWidth>21cm</PageWidth>
<rd:ReportID>ac81c2b2-28a7-4125-b56c-8942cd126937</rd:ReportID>
<ReportParameters>
<ReportParameter Name=”id”>
<DataType>String</DataType>
<DefaultValue><Values><Value>{A0492588-AF83-DA11-8077-0003FFBFF4D9}</Value></Values></DefaultValue>
<Prompt>id</Prompt>
</ReportParameter>
<ReportParameter Name=”type”>
<DataType>Integer</DataType>
<DefaultValue><Values><Value>1</Value></Values></DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>type</Prompt>
</ReportParameter>
<ReportParameter Name=”typename”>
<DataType>String</DataType>
<DefaultValue><Values><Value>account</Value></Values></DefaultValue>
<Prompt>typename</Prompt>
</ReportParameter>
</ReportParameters>
<Language>en-GB</Language>
</Report>
Publishing the report
The report can either be published via CRM, or directly to Reporting Services via Report Manager. As reports created this way are not intended to run in the same way as other CRM reports, I tend to create a separate folder in Reporting Services for these reports, and deploy them directly in Report Manager. However, all that really matters is that you know the path to the report in Reporting Services.
Accessing the report from a browser
The syntax for rendering a report from a url takes the following form:
http://server/reportserver?/Excitation+Test_MSCRM/Account+Opportunity+Summary&rs:Command=Render
Where:
http://server/reportserver is the url to the report server (not the Report Manager)
/Excitation+Test_MSCRM/Account+Opportunity+Summary is the path to the report, which you can find via Report Manager.
Using the above url will render the report, but with extraneous information. The following additional parameters will remove the toolbar and parameter list:
&rc:Toolbar=false&rs:ClearSession=true
The first of these removes the toolbar and parameter list, and the second ensures that the report is run with new data each time it is accessed
Creating the IFrame
So, we can now add an IFrame to the CRM account form with the following url:
http://server/reportserver?/Excitation+Test_MSCRM/Account+Opportunity+Summary&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true
To pass parameters, select the ‘Pass record object-type code and unique identifier as parameters’ check box.
We can now publish and test the form
Launching a report from an ISV.Config button
A very similar approach can be used to launch reports via ISV.Config. There is one important difference; the parameters are passed slightly differently, for example:
?oType=1&oTypeName=account&oId={5E849F23-FEEF-DA11-A04F-00065B842569
Therefore the parameters in the report need to be defined as oId, oType and oTypeName.
Also, when you specify the report url in isv.config.xml, any ampersand (&) characters in the url need to be encoded as &
It is possible to specify the output format of the report via the url. For example, add the following to render the report as a pdf document:
&rs:Format=PDF
Further ideas
Another use of this technique with CRM data would be to show related data on a form. An example would be to display on the opportunity form the address information for the opportunity’s customer.
The example given here only accesses data in the MSCRM database, but it is perfectly possible to reference data in other data sources in the report. In one recent example I worked on we used the same technique to display account financial information (such as current credit limit and rating) from Great Plains in the CRM account form.
Links
http://msdn2.microsoft.com/en-gb/library/aa256624(SQL.80).aspx – URL access with SQL 2000 reporting services
http://msdn2.microsoft.com/en-gb/library/ms153586.aspx – URL access with SQL 2000 reporting services
http://msdn2.microsoft.com/en-gb/library/bb407661.aspx – passing parameters to an IFrame in CRM 3.0
http://msdn2.microsoft.com/en-gb/library/aa681363.aspx – modifying in ISV.Config CRM 3.0