Microsoft Flow: Creating Dynamics 365 Scheduled Report – Low Code Approach

Recently I’ve been exploring the power of Microsoft Business Application Platform. One of the most common requirements that I think we can solve in the Low Code Approach is the scheduled report.

For this example, I’m going to use this example user story to describe the scheduled report requirement: As a sales manager, I want to receive a daily digest of the new opportunities, so that I have the visibility of new opportunities in my organisation.

Before thinking about the solution, we might need to look at the platform that we are working on right now. Microsoft Business Application Platform, where Dynamics 365 plays an integral part in it, comes with Microsoft Flow that has numerous connectors available. This opens up a very broad capability in building the business workflow.

So, without further ado, here is my “Low-Code” approach generating the scheduled report (if you want the more complex one using SSRS, Bob Guidinger, has the solution here).

The first step in the Flow is to set the schedule:

Next, we need to construct the filter for the Get List of Records from Dynamics 365 oData query, to build it I’m using Jason Lattimer’s CRM Rest Builder 

Once the query is generated, copy the filter as the base to be put in Flow

To get the created on = yesterday, we could use the Flow Built-In Formula/Expression:

You also could try the query whether it’s successful or not by running the Test process on Flow

This will need a bit of Dev knowledge to understand the JSON output of the list records, but we can confirm that it is returning the list correctly or not

Once confirmed, then we can construct the HTML table for the email body using the Create HTML Table action on Flow and to some field mapping.

And then send the email action:

Make sure that to select the Is HTML to Yes, otherwise, the HTML tags will be rendered as literal text

This will produce a very basic email, that tick the box of the requirement, but not pretty

So, to make it more appealing, I’m adding the “Low-Code” CSS to style the table using the compose action before sending the file

Attribution: CSS is a copy from w3schools: https://www.w3schools.com/css/tryit.asp?filename=trycss_table_fancy

Now with the CSS, the table is looking more appealing

Another ask, can we also have this table/report as HTML. The answer is Yes. Microsoft Flow has connectors to convert HTML to PDF via 3rd party service such as Muhimbi PDF or Plumsail Documents (which requires subscription/license)

The quick and dirty option to save to PDF is via OneDrive actions, by creating the HTML and Convert the file to PDF.

Typically you could create a folder in OneDrive to store the report temporarily and do periodic clean-up.

Then this can be added to the email as an attachment:

Which produces the email with the PDF attachment:

And the PDF file opened with PDF reader:

My Final Flow is something like this:

 

HTH! Happy Flow-ing!