I have had a couple of people posting comment on one of previous blogposts “Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate” asking me on how to specify values for multi value parameters in a Paginated reports.
So, I decided to write a blog post on how you can do it – if you have solved in other ways than my solution please let me know in the comments
How to do it
In my example I have created a simple report that has one multi value parameter that is called “StoreChain” and the parameter label is Chain.
And in the Power BI Report Builder the Report parameter is setup to Allow multiple values
Now let’s create the flow
I will create a flow where the user can type in the values for the Chains that the report should be filtered by – So I choose to create an instant cloud using the trigger “Manually trigger a flow”
I add an Text input called Chain and add a description to the input field
When we then select the Action “Export To File for Paginated Reports” – we can select the workspace and report we want to run
At the bottom of the action dialog we have the ParameterValues name and value section
But if we specify the values by referring to the trigger input – we will get a string value containing all the chains separated by ; (semicolon)
For example running with this
Will give us
And because there is no chain called this we will get an error when we try to test the flow
Solution
We need to specify the parameter values a little bit different
If you click on the button “Switch to input entire array” we can specify the Parameter values as an array instead
And this reveals that in order to specify more than one chain we need to specify an array of values containing a column called name and value – like this.
This means that before we call the action to export the paginated report we need to build an array of the specified chains (parameter values).
First I create an array variable called pv containing an empty array
Next, I use an “Apply to each” where I use the split function to create an array of the text specified in the trigger.
And inside the Apply to each step I build the parameter value array by using the action “Append to array variable” where I construct an item in the pv array for each of the result of the split function
Then I can use the pv Array variable to Export To File for Paginated Reports – ParameterValues setting
And then to finish the flow we can add a “Send an email (V2)” action to send the report
The final flow will have the following steps.
Next – let us test it
With this entered as text for the input
The steps will specify the ParameterValues as an array
And after about 35 seconds the flow has emailed me the requested reports
Success 🙂
Hope you find this useful.
Hi Erik, thanks so much for following up on this!
I’m attempting this in a flow now, although using a SharePoint Excel file as a source for the multi-value parameter rather than the Power Automate trigger text. I haven’t worked out how to change the split expression to reference a column from the Excel file rather than the trigger yet though – is this something you could share any wisdom on? 🙂
i.e. Replacing split(triggerBody()[‘text’], ‘;’) with something like split(outputs(‘List_rows_present_in_a_table’)[‘ExampleColumn’], ‘;’)
I’m trying to figure this out as well! Has anyone figured out how to split the string from an excel list?
I could try – do you have an example data file ?
Yes I can share a sample file. Basically, I’m using Excel, stored in SharePoint, to fill in the report parameters, but one or more of the parameters could require multiple values. Let me know how to get you a sample file.
You sir, are a life saver! I was about to give up, great walkthrough
Thank you – glad one more life is saved 🙂
Thank you, thank you, thank you! This was exactly the information I needed.
😀
Hi sir, I’m trying to do the same thing but without create the array manually,
I need to get all parameter values from the report and then do the loop
Hi – can you explain how the array is created and the structure of it ?