Generating Reports Using Word Documents And Flow

The new “Populate a Microsoft Word template” action in Flow that was released last week got me thinking: apart from the obvious mail-merge applications, this could be used to build reports in Word! Here’s a very simple example.

Let’s say you have a table in an Excel worksheet stored in OneDrive for Business:

image

Let’s say you have a Word document that looks like this:

image

Shown in this screenshot are two Plain Text Content Controls called ProductName and SalesAmount, where text can be entered and with placeholder text already added. The screenshot above is with the document in Design Mode with the controls visible; the document would normally look like this:

image

The idea is to loop through the rows in the Excel table and use the data on each row to populate the content controls in the template and then create a new Word document. Here’s a Flow that does this:

image

Looking at some of these actions in a bit more detail:

  • List rows present in a table gets the data from the Excel table shown above

    image

  • Apply to each loops over each row in the table
  • Populate a Microsoft Word Template takes the Word document shown above and then puts the value from the Product column in the current row from Excel into the ProductName content control, and the value from the Sales column in the current row into the SalesAmount content control:

    image

  • Create file saves the resulting new Word document in a separate folder:

    image

Here are the three Word files that are created:

image

And here’s what the contents of AppleSales.docx looks like:

image

This is only a very, very basic example of what’s possible: you could convert these documents to pdf, email them out as attachments, and even in the future get your source data direct from Power BI.

Why would you want to build reports in Word though? Some reports are very text heavy, and although you can do text substitution in Power BI (for example using the Enlighten Data Story visual), nothing does highly-formatted text as well as Word. OK, it’s not going to be something that you do very often – the vast majority of reports you build will be better off in Power BI, as paginated reports/SSRS, or in Excel – but there are definitely some use cases. The new Flow action is very limited right now but with support for more types of content control even more sophisticated reports will be possible using this technique.

6 responses

  1. Pingback: Last Week Reading (2019-01-27) | SQLPlayer

  2. Pingback: Generating Reports In Word Via Flow – Curated SQL

  3. Thanks for this Chris – very handy. You may want to know that Word Online (Business) is a Premium Connector and therefore is only accessible through Flow Plan 1 (USD5.00/user/month) or Flow Plan 2 (USD15.00/user/month) but not through the Free plan or Flow for Office 365 plan.

    https://emea.flow.microsoft.com/en-us/pricing/

    I’ll leave others to consider whether connecting to Word should have been made available as part of the Office 365 plans.

  4. this is great, but there’s one problem that I am running into with doing something very similar using Microsoft Excel as a template.

    If multiple people run the flow at the same time, and especially for reports with lots of rose, the data can get mixed up in the template.

    Is there a way for flow to check out the document so that it does not get edited by other flows? And is there a graceful error handling when a flow runs while a document is already checked out?

Leave a Reply to sam Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: