How to generate PDF email attachment of SSRS Report in Dynamics 365 CRM?

Introduction

MS Dynamics CRM users may need to generate an SSRS report in PDF format and send it as an email attachment. This can be done using Javascript event handlers like form on-load, form on-change, form on-save, ribbon button click, etc. But if there is a need to send this email on the creation or update of any entity, we need a different solution.

The solution explained here applies to the Unified Client Interface (UCI) of Microsoft Dynamics 365 v9.1 and above.

Customer Problem

Email the work order details of an upcoming event to their end customer.

The detailed email should also have a PDF attachment of an SSRS report for that work order. To generate the PDF email attachment of the SSRS report, we need to generate the base64 encoded string of the PDF file and add it to the attachment body. The email should be sent on the creation of the work order in Dynamics CRM. The real issue occurs if the work order is created from the backend (server-side) and not from the CRM form.

Solution

We have two different options to generate the SSRS report on the server side.

1. Using the plugin with CRM OAuth Access Token
2. Using Power Automate

We must pass the following parameters to generate the SSRS report on the server side for a specific record in CRM.

• Report ID: The GUID of the report in MS Dynamics CRM
• Report Name: The name of the report in MS Dynamics CRM
• Organization Unique Name: The unique name of the Dynamics CRM instance to which the report belongs.
• Report parameters in the FetchXML format: A set of parameters is passed to the report, which is dependent on the specific record on which we are generating the SSRS report. These parameter names can be fetched from the .rdl file. Every parameter needs to be prefixed with ‘p:’. For example, if the parameter name is “CRM_msdyn_workorder”, the parameters should be passed as “p:CRM_msdyn_workorder”.

Option 1: Using the plugin with CRM OAuth Access Token

This option utilizes the out-of-the-box (OOB) plugin functionality in CRM. We can register the plugin for creating or updating the record and it works on the server side. First of all, we are making an HTTP POST request in the plugin to get the report session and control ID parameters. These parameters along with the above-listed common parameters and OAuth access token are passed to another HTTP GET request to get the final base64 encoded string of the PDF file that can be directly passed to the email attachment body.

• 1st HTTP POST URL: “https://<crmorgname>.crm.dynamics.com/rsviewer/reportviewer.aspx”
• 2nd HTTP GET URL: “https://<crmorgname>.crm.dynamics.com /Reserved.ReportViewer WebControl.axd?ReportSession=<Report Session from 1st API Request>&Culture=1033&CultureOverrides=True&UICulture= 1033&UICultureOverrides=True&ReportStack=1&ControlID=<Control ID from 1st API Request>&OpType=Export&FileName= Public&ContentDisposition=OnlyHtmlInline&Format=PDF”

You should be passing the authorization header in the HTTP requests as a bearer token. To get the OAuth Access Token, we should register the Dynamics 365 application in Azure Active Directory and the access token can be fetched by passing the registered client ID & user credentials.

• Pros
  • Can customize the code to any extent as per our requirements.
• Cons
  • Coding knowledge is required.

Option 2: Using Power Automate

Power Automate, previously known as Microsoft Flow, is another option to generate the PDF version of the SSRS report. We can invoke an HTTP POST request to the below URL from Power Automate with the above-listed common parameters.

                    “/CRMReports/rsviewer/reportviewer.aspx”

We will get the response as a JSON string and it will have a parameter called “PDFDownloadURL”. We will extract this URL from the response and invoke another HTTP GET request to the same URL. We will get the final base 64 encoded string as the response of this GET request. This can be directly passed to the email attachment body.

• Pros
  • No coding experience is required.
• Cons
  • Only a set of inbuilt connectors, actions, and functions can be utilized. For creating custom connectors, coding knowledge is required.

Selection of the appropriate solution

If you are a skilled developer possessing excellent knowledge in Dynamics CRM plugins using C# and .NET, you can go with Option 1. You can utilize your expertise and make necessary changes as per your requirements. You can also go with Option 2 if you want to try out Power Automate flows.

If you are looking for a solution with minimal coding, Option 2 is better. You can easily create Power Automate flows without having any technical knowledge by just adding the steps as required.

Conclusion

We have discussed two different ways of generating an SSRS report as a PDF email attachment on the server side in MS Dynamics CRM. Have questions? Let us know.