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. In 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 need to 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 on creating or update of 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://.crm.dynamics.com/rsviewer/reportviewer.aspx”
• 2nd HTTP GET URL: “https://.crm.dynamics.com /Reserved.ReportViewer WebControl.axd?ReportSession=&Culture=1033&CultureOverrides=True&UICulture= 1033&UICultureOverrides=True&ReportStack=1&ControlID=&OpType=Export&FileName= Public&ContentDisposition=OnlyHtmlInline&Format=PDF”

You should be passing the authorization header in the HTTP requests as a bearer token. In order 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 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 the 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 definitely go with Option 1. You can utilize your expertise and make necessary changes as per your requirement. You can also go with Option 2 if you want to try out Power Automate flows.

If you 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.

About the Author

Nithya Gopinath is currently working as a Senior Engineer at Suyati Technologies. She is part of the MS Dynamics CRM team at Suyati and has excellent knowledge and experience in MS Dynamics 365 CRM, ASP.NET platforms and Power BI. She loves solving logical puzzles and playing badminton. She is interested in keeping herself updated about the trending technologies.

The post How to generate PDF email attachment of SSRS Report in Dynamics 365 CRM? appeared first on Suyati Technologies.