CRM – Creating report attachments for emails in Workflows

I had been asked by a client to generate a report, based on an Account, attach this report to an email and send this email as part of a workflow. My server setup was fairly standard; separate servers for the CRM front-end, back-end, database and reporting. The workflow would consist of a number of steps:

  1. Create an email entity with the necessary content.
  2. Run some custom workflow code to generate the report with the following Inputs. These would include the Email created as part of Step #1, the Report to generate and attach to the email, the Primary Contact for the Account and the URL to the report server.Custom Step Inputs
  3. Run some additional custom code to send the email with the following inputsCustom Step Inputs

The problem I had to solve was that when I tried to generate the report in code using the ServerReport Render method I would get the following error:

An error has occurred during report processing (rsProcessingAborted)  Cannot create a connection to data source ‘CRM.’ (rsErrorOpeningConnection)  Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

As it turns out, this KB article gave me the clue to solving the problem. This error occurs in Microsoft Dynamics CRM 2011 because the Microsoft Dynamics CRM Reporting Extensions requires the user’s SystemUserId value as the Log in name and the user’s OrganizationId value as the password. When a report runs inside Microsoft Dynamics CRM, these values are passed automatically. The OrganizationId and SystemUserId can be pulled from the IWorkflowContext interface.  I then needed to get details of the DataSource and apply the credentials. This can be done in a few easy steps:

  1. Use the ServerReport GetDataSources method to retrieve the ReportDataSourceInfoCollection; since my reports only have one DataSource we can just pull the first item from the collection.
  2. Create a DataSourceCredentials object to set the username (SystemUserId), password (OrganizationId) of the DataSource. Add that object to the DataSourceCredentialsCollection.
  3. Use the ServerReport SetDataSourceCredentials method to apply the credentials to the ServerReport.

The code snippet below provides a bit more detail on the information from these steps.

ReportViewer viewer = new ReportViewer();
			
Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string fileExt;

ActivityMimeAttachment attachment = new ActivityMimeAttachment()
{
    MimeType = "application/octet-stream",
    FileName = attachmentName
};	

viewer.ServerReport.ReportServerUrl = new Uri(reportServerUrl);
viewer.ServerReport.ReportPath = string.Format(@"/{0}_MSCRM/CustomReports/{1}", organisatioName, reportName);

ReportDataSourceInfoCollection reportDataSources = viewer.ServerReport.GetDataSources();
if (reportDataSources == null || reportDataSources.Count == 0)
{
    throw new InvalidPluginExecutionException(string.Format("The report {0} does not contain a valid datasource", reportName));
}
DataSourceCredentials dsc = new DataSourceCredentials() { Name = reportDataSources[0].Name, Password = organisationId.ToString(), UserId = systemUserId.ToString() };
DataSourceCredentialsCollection dscc = new DataSourceCredentialsCollection();
dscc.Add(dsc);

viewer.ServerReport.SetDataSourceCredentials(dscc);
viewer.ServerReport.SetParameters(reportParams);
viewer.ServerReport.Refresh();

byte[] bytes = viewer.ServerReport.Render("PDF", null, out mimeType, out encoding, out fileExt, out streamids, out warnings);
attachment.Body = Convert.ToBase64String(bytes);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s