MS Dynamics CRM 2013: Generating SSRS Report as PDF Using Plugin

One of our customers wanted a command bar button on the Order form that would automatically create an email activity and attach a PDF invoice to it, in order to be able to just review the email content and click ‘send.’ There are third party tools that can do this kind of thing and more, and some blogs out there that show this can be done with custom code for MS Dynamics CRM 2011. More specifically, I’m referring to the following two links:

Creating an e-mail activity with PDF format custom SSRS report as an attachment

Extracting the SSRS Report in PDF programmatically using Plugin or Wokflow in mscrm 2011

In this case, we decided to leverage the new tools available with MS Dynamics CRM 2013 – Custom Actions.

Here are the things we had to do:

– Trigger the action when user clicks ribbon button on order form

– SSRS report should take the current Order’s ID as input parameter

– Generate the PDF output of a SSRS report

– Create email activity and prepopulate From, To and Subject fields from Order

– Generated PDF output is attached to a new email activity

For the first part, we leveraged the “Custom Actions” feature to trigger a plugin from a ribbon button. Refer to this link to learn how to trigger the plugin by invoking Custom Action from a ribbon button.

One of the challenges was that this report could be executed by a user in the CRM Reports section, as well as the plugin that we are creating. This means we had to use the SalesOrder view instead of FilteredSalesOrder in report queries and passed in the logged-in user’s ID to the report as a parameter. We also had to elevate the access rights of App Pool user to fetch the records using its default credentials. Adding app pool user to CRM is not recommended by Microsoft. Please refer here for more information.

When Generating Report from Sales Order Ribbon:

The User parameter for the report is explicitly set from the custom action plugin with InitiatingUserId. The report query has filters to retrieve only the records that the user has access to, so it works the same way as fetching records using the FilteredSalesOrder view.

When Generating Report from Reports Area:

The User parameter is retrieved as default value using the following query, and the sales order records are filtered using this parameter.

SELECT systemuserid

FROM FilteredSystemUser

WHERE (systemuserid = dbo.fn_FindUserGuid())

The Plugin Code is Explained in Detail Below:


//Check if the Plugin has Report name parameter and whether the parameter has value.
public override void ExecutePlugin(DemoPluginExecutionContext context)
{
var callingreport = string.Empty;

if (context.PluginExecutionContext.InputParameters.Contains("Data") &&
context.PluginExecutionContext.InputParameters["Data"].ToString() != null)
{
callingreport =
context.PluginExecutionContext.InputParameters["Data"].ToString();
}
if (string.IsNullOrWhiteSpace(callingreport))
{
throw new InvalidPluginExecutionException
(OperationStatus.Canceled,
"To generate this report, the Report Name must be spcified.");
}

/* Create an instance of SalesOrder entity from early bound class and retrieve the
necessary columns. SalesOrder is a custom class that inherits Entity and
has several constants defined for ease of use. */
SalesOrder currentOrder =
context.OrganizationService.Retrieve(SalesOrder.LOGICAL_NAME,
context.TargetEntityReference.Id, new ColumnSet(SalesOrder.CUSTOMER_ID,
SalesOrder.NEW_CONTACT, SalesOrder.OWNER_ID)).ToEntity();

//Fetch the ReportServer URL from a configuration entity and form the report service URL.
string reportservice = Configuration.ReportServerUrl +
"/ReportExecution2005.asmx";

/* Pass the report service url and default credentials to create an instance of
ReportGenerator class. Separate class file needs to be created for
ReportGenerator class and respective code is available below after the
plugin code. */
ReportGenerator reportGenerator = new ReportGenerator(reportservice,
CredentialCache.DefaultCredentials);

//Pass the required parameters for SSRS report.
ParameterValue[] parameters = new ParameterValue[2];
parameters[0] = new ParameterValue();
parameters[0].Name = "CRM_FilteredSalesOrder";
parameters[0].Value = string.Format("Select * From SalesOrder Where
SalesOrderId = '{0}'", currentOrder.Id);

parameters[1] = new ParameterValue();
parameters[1].Name = "CRM_UserID";
parameters[1].Value =
context.PluginExecutionContext.InitiatingUserId.ToString();

//Build the database path of report.
byte[] reportresult = new byte[0];
string databasenamepath = "/" +
context.PluginExecutionContext.OrganizationName + "_MSCRM/" + callingreport;

//Save the report result in a binary array.
reportresult = reportGenerator.Render(databasenamepath,FormatType.PDF,parameters);

//Create Email Activity and associate with Order.
EntityReference sender = null;
sender = new EntityReference("systemuser", currentOrder.ownerid.Id);
Entity fromParty = new Entity("activityparty");
fromParty["partyid"] = sender;

Entity email = new Entity("email");
email.Attributes["from"] = new Entity[] { fromParty };
Entity toParty = new Entity("activityparty");

EntityReference toPartyReference = null;
toPartyReference = currentOrder.new_contact;
toParty["partyid"] = toPartyReference;
email.Attributes["to"] = new Entity[] { toParty };
email.Attributes["subject"] = callingreport + " for " +
currentOrder.customerid.Name;
email.Attributes["description"] = "PDF " + callingreport +
"Attached";
email.Attributes["regardingobjectid"] = new
EntityReference(currentOrder.LogicalName, currentOrder.Id);
email.Attributes["ownerid"] = sender;
Guid EmailId = context.AdminOrganizationService.Create(email);

//Create Order SSRS report as PDF attachment to Email
Entity attachment = new Entity("activitymimeattachment");
attachment.Attributes["objectid"] = new EntityReference("email", EmailId);
attachment.Attributes["objecttypecode"] = "email";
attachment.Attributes["filename"] = callingreport + ".pdf";
attachment.Attributes["subject"] = callingreport + " for " +
currentOrder.customerid.Name;
attachment.Attributes["body"] =
System.Convert.ToBase64String(reportresult);
context.AdminOrganizationService.Create(attachment);
}

Add the Separate Class File for ReportGenerator


class ReportGenerator
{
#region Privates

ReportExecutionService _reportexecutionservice = null;

#endregion Privates

#region CTOR

internal ReportGenerator(string ServiceUrl, ICredentials credentials)
{
if (string.IsNullOrEmpty(ServiceUrl))
throw new Exception("Parameter ServiceUrl has to contain value");

if (credentials == null)
throw new Exception("Parameter Credentials has to contain value");

_reportexecutionservice = new ReportExecutionService()
{
Credentials = credentials,
Url = ServiceUrl
};
}

#endregion CTOR

#region Methods

internal byte[] Render(string Report, FormatType formattype)
{
return this.Render(Report, formattype, new ParameterValue[] { });
}

internal byte[] Render(string Report, FormatType formattype, ParameterValue[] parameters)
{
byte[] result = null;
string format = GetFormatType(formattype);
string historyID = null;
string devInfo = @"False";
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
string[] streamIDs = null;

try
{
ExecutionInfo execInfo = new ExecutionInfo();
ExecutionHeader execHeader = new ExecutionHeader();
_reportexecutionservice.ExecutionHeaderValue = execHeader;
execInfo = _reportexecutionservice.LoadReport(Report, historyID);
_reportexecutionservice.SetExecutionParameters(parameters, "en-us");
result = _reportexecutionservice.Render(format, devInfo, out
extension,
out mimeType, out encoding, out warnings, out streamIDs);
}
catch (Exception ex)
{
if (ex is SoapException)
{
SoapException sexc = ex as SoapException;
throw new Exception(string.Format("Error generating report - {0}",
sexc.Detail.InnerText));
}
else
{
throw new Exception(string.Format("Error generating report - {0}",
ex.Message));
}
}

return result;
}

private string GetFormatType(FormatType formattype)
{
switch (formattype)
{
case FormatType.XML:
case FormatType.CSV:
case FormatType.IMAGE:
case FormatType.PDF:
case FormatType.MHTML:
case FormatType.EXCEL:
case FormatType.Word:
return formattype.ToString();
case FormatType.HTML40:
return "HTML4.0";
case FormatType.HTML32:
return "HTML3.2";
default:
throw new Exception(string.Format("Rendering type {0} is not
available", formattype));
}
}

#endregion Methods
}

internal enum FormatType
{
XML, CSV, IMAGE, PDF, HTML40, HTML32, MHTML, EXCEL, Word
}

Additional Insights

Governance eBook Sample

If you’re using CRM as a platform for deploying line of business applications, make sure you’re getting the most from your investment by reading CRM Governance: What It Is, What It Isn’t, and How to Do It Right, an informative eBook written by governance experts.

7 Responses to “MS Dynamics CRM 2013: Generating SSRS Report as PDF Using Plugin”

  • Michael Karls says:

    Hi Kara, I wrote a similar plugin that pulls a report from SSRS that uses the DefaultCredentials, but when I get the email the report that is attached contains just the template of the report, none of the data that I passed through shows up. Do you have any ideas why this could be happening? Thank you, really stuck on this.
    -Mike

    • Hugo says:

      How we can accomplish this in CRM Online?

      • Michael Karls says:

        Do you have a report server? If not, I’m not sure you will be able to.

        • Hugo says:

          We have a server with the Report Server

        • Hugo says:

          We have the report Server but our doubt is we need to import the reports that we want generate in Report Server? But the Report use the CRM Online to render

  • Michael Karls says:

    Nevermind, I found that the issue was with our Data Source. I needed to have a setting changed.

  • Prashant says:

    Hello
    Can i download Generated PDF to local drive?

Leave a Comment