Schedule Power Automate to run weekly reports from Survey 123

Question

I have a survey where users can provide feedback on software including options for feature requests, or notify of bugs/errors. I am using the new Microsoft Power Automate Survey 123 Create Reports action (which is amazing by the way) and have successfully managed to get the flow to create a single report based on a survey when it is submitted and save to a SharePoint folder of my choosing as well as notifying me of the report. This flow has a condition to run when a the option of Bug/Error is selected in the survey

This is so that i can be notified if a bug is found and fix accordingly. However, I would also like to run a summary report from Survey 123 collating all records submitted including the bugs and feature requests once a week. I have found how to schedule the flow to run once a week at a set time, but am struggling with working out how to connect the survey 123 report creation and filter by survey date within the last 7 days?

I know that the dates need to be formatted from epoch to something more workable but am hitting a it of a wall

any ideas would be massively helpful

@IsmaelChivite

RobertAnderson3 · Answer

Hi @SeanNagy1 This is the way the flow looks that I have that creates a summary report every week. I have a Condition in there to check for no results because sometimes I won't have any based on my WHERE clause.

Hopefully that can get you started, but let me know if you have any questions.

SeanNagy1 · Answer

Ismeal, do you have an example of how this would work with the Power Automate workflow? I want to generate a summary report each week and then email the final PDF out to a client.

IsmaelChivite · Answer

Hi. See attached XLSForm and corresponding Report template. To test this, publish the XLSForm making sure the name of your survey form is BugsAndEnhancements v2.  If you choose a different name, then you will need to edit the template to reference your survey name accordingly.

Highlights:

* I used a summary section. For details on summary sections, check this blog or this help topic.
* I made extensive use of !important.  If you add !important to the end of a where statement, the report service queries the entire layer, as opposed to just the records passed to the report task.  Here are some examples:

${BugsAndEnhancements_v2 | where:"category = 'Bug' and submittedOn BETWEEN CURRENT_TIMESTAMP - 7 AND CURRENT_TIMESTAMP !important" | stats:"count,objectid"}

${#BugsAndEnhancements_v2 | where: "category= 'Bug' !important" | orderByFields: "submittedOn DESC" }... ${/}

* The idea here using !important is that your report will ALWAYS query the entire survey layer and get back records in the last 7 days.  !important will override the records passed to the report task. In PowerAutomate, you can harcode the ObjectID parameter of the Create Report action and say, for example, 1. !important will ignore the ObjectID and work with the entire table.

This worked pretty good for me. I was able to schedule this and have the report run as expected. Hope it helps.

For context, you can go about scheduling reports in a couple of ways:

* In Power Automate, you can use the WHERE parameter in the Create Report action to dynamically pass the records against you want to run the report. I think this is how you originally approached this. Unfortunately, this will not work today, because of a BUG, although we will fix this in our upcoming June release.
* Alternatively, you can use the !important approach I describe above. In this case, as I said, the WHERE and ObjectID parameters in the Power Automate Create Report action will be ignored. In your particular case, since you want to always create a report for the past 7 days, I think this approach will do the trick.

We will cover all of this in more detail in our upcoming Automate Survey123 Reports webinar.  Hope you can make it.

Weekly Report.docx

BugsAndEnhancements v2.xlsx