Blogs

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

Inkey, June 21, 2019 16838 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