Logo
banner

Blogs

Automatically schedule the SSRS Report and send it as PDF attachment with the email in Microsoft D365 CE/CRM

, June 21, 2019 15759 Views

The Microsoft D365 CE/CRM provides the feature to run the selected SSRS reports manually. But I had the requirement to schedule the execution of report on a user-specified frequency like daily, weekly or monthly and send the report to the user via email as an attachment. But I did not find any OOB feature in D365 to schedule and run the report automatically.

I was able to achieve this functionality using the Canvas PowerApp, Microsoft Flow, Custom connector, Custom Workflow, and Plug-in. Let me elaborate the way this was carried out.

The Canvas PowerApp was used as an entry point from where the user would provide the details like the report which is to be executed, frequency of running the report, email to which the report has to be sent. All this data is saved into a custom entity named “Schedule Report”.

On submitting the details for scheduling the report, a record will be created in Microsoft D365 CRM in the “Schedule Report” entity and a plug-in will be fired, which will set the “Next run date” field on the above-said custom entity record.

There is a waiting Workflow that will be called on the update of the “Next run date” field, which will trigger the Custom Workflow Activity on reaching the “Next run date”, which will call the Microsoft Flow using the HttpClient object as below:

#region CallMSFlowScheduleReport
/// <summary>
/// Method to call the MS flow “SCHEDULE REPORT”.
/// reportDetails is the class object with field values of the “Schedule Report” entity filled by the user from the PowerApp
/// setMSFlowUrl is the HTTP Post URL of the HTTP request trigger method of the Microsoft Flow
/// <returns>HttpResponseMessage</returns>
internal static HttpResponseMessage CallMSFlowScheduleReport(ReportDetails reportDetails,
                                                                     string setMSFlowUrl)
{
    HttpResponseMessage httpResponseMessage = null;
    try
    {
       HttpClient httpClient = new HttpClient();
       httpClient.DefaultRequestHeaders.Add("cache-control", "no-cache");       
       StringContent stringContent = new StringContent(JsonConvert.SerializeObject(reportDetails), Encoding.UTF8,          "application/json");
       stringContent.Headers.ContentType = new MediaTypeHeaderValue("application/json");

       httpResponseMessage = httpClient.PostAsync(setMSFlowUrl, stringContent).Result;
     }
     catch (Exception ex)
     { throw new InvalidPluginExecutionException(ex.Message); }

     return httpResponseMessage;
}
#endregion

After executing the flow, calculate the next run date based on the frequency provided by the user and update the “Next run date” again.

The flow has the input elements like report id, email recipients, email subject and email body. The report in Microsoft D365 CRM is fetched using the report id and is executed using the custom connector. Then send the email to the related user along with the report as an attachment.

Below are the steps of the “SCHEDULE REPORT” Microsoft flow:

  • Trigger the flow with an HTTP request:

  • Get the report using report id:

  • Run the report using the custom connector:

Note: In order to install the cusṭom connector, refer the below link:
https://github.com/bguidinger/Xrm.ReportScheduler/blob/master/docs/INSTALL.md

  • Send the email:

I hope this helps you!!


ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://powerapps.microsoft.com/en-us/partner-showcase/inkey-solutions-atm-inspection/

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

3 responses to “Automatically schedule the SSRS Report and send it as PDF attachment with the email in Microsoft D365 CE/CRM”

  1. Megh says:

    Hey Dear,

    Awesome article. I was struggling to generate pdf out of ssrs report within dynamics via workflow but couldn’t able to achieve because of so many limitations. Finally you helped me turning this magic.

    Great article though some missing points which I am going to mention.

    Regards
    Megh

  2. PKar says:

    Does this support excel/csv instead of PDF format

  3. Mohit Patel says:

    Hi Team,

    I recently configured the Report Renderer and it is working as expected when Multifactor Authentication is disabled for my user.

    When I enable it, I’m getting 400 error.

    Any idea?

    Thanks,

Leave a Reply

Your email address will not be published. Required fields are marked *

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?