Dynamic Subscriptions and a Paginated Report with a multi-value parameter

[dwa777]

I've got a Paginated Report that has a multi-value parameter list. Multiple customer numbers can be selected with this parameter for generating the report output.

For a Dynamic Subscription, how can I pass multiple customer numbers to a multi-value parameter in the report?

For example, I don't want to have to use a single parameter value and generate the report output three times like:

email                             customer number

joe@somplace.com      12345

joe@someplace.com     23456

joe@someplace.com     34567

I'd like to be able to do something like the following to pass all desired customer values to the report parameter once and generate only one subscription email:

email                             customer number

joe@somplace.com      12345,23456,3467

Hope that makes sense. Can this be done? Thanks for any suggestion!

[annelebel]

There is an easy solution to this, have one report be customer facing where the use of the multi-value works as normal from the drop down parameter selection. Then have a subscription based version of the report where the multi-value parameter is essentially just text i.e. remove the check for multi-value, remove any defaults, etc. Then when you go to do a dynamic subscription just pass 'abcd, bcde, cdef' as a single string. This is assuming of course you are doing a STRING_SPLIT or using some kind of split function on the database side. This delivers a single file output to the user with all three values having been passed for their report run. I've seen postings where a dummy parameter is used and this allows for the report to work on demand and also in a subscription, but it's pretty awkward and very confusing. This seemed the simplest way to accomplish what was needed and I can train other developers on my team to handle this scenario the same way.

[MP78]

Go the other way and do it inside the Paginated Report and not on the Dynamic Scheduling side.

I have a listing of Project ID's (MACROID) against a Project Manager.

| Bob Smith | BobSmith@email.com | 12345, 23456, 3467 |

My Parameter has the Project Manager name as the Label and the Project ID's (MACROID) as the value.  So on selection of the PM you get the value of "12345, 23456, 3467" returned to use in the filter.

Within your dataset then for your output set a Filter using the IN operator.

Ensuring that I'm using a String here on the Expression.

Then the Split for the Value section.

Then on the dynamic report you can use the Parameter to select the Project Manager for the report.

[v-yilong-msft]

Hi @dwa777 ,

I think you can do this in Power BI by setting up dynamic subscriptions for paged reports.

First you can select the "Dynamic per recipient (preview)" option in the subscription pane.

Select the Power BI dataset that contains the recipient data, including the email address and the corresponding parameters Map the columns in the dataset to the corresponding parameters in the subscription settings. This will ensure that each recipient receives a copy of the report with the respective parameters applied.

If you want to send a single report to joe@someplace.com with multiple customer numbers, there will be a row in the semantic model where joe@someplace.com is the email and 12345, 23456, 34567 are the customer numbers. When setting up a dynamic subscription, you need to map the Customer Number column to a multi-valued parameter in the report. This way, you can send a personalized copy of the report to each recipient and apply the specified parameters without having to generate multiple reports for each parameter value.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dwa777]

Hello,

Thank you very much for the reply and suggestion. Unfortunately passing a comma separated value from the semantic model column to the multi-list parameter of the report doesn't work. You would think logically it would work but it dosen't.

It sends the following error in the subscription output email:

"DataDrivenSubscriptionEmailNotDeliveredError

1 out of 1 subscriptions failed.

Render Failed - Recipients: aaaa@bbbb.net, ExtensionType: EXCELOPENXML, Parameters: [CustomerNumber]:815, 577, 794, ErrorDetails: Unable to render paginated report, ErrorCode: rsParametersNotSpecified"

Note that if I only pass a single value, it works. I also tried to remove the spaces from the comma separated string, keeping only the commas, and that did not work either. I think it's trying to lookup the entire value "815, 577, 794" rather than separating the values.

After researching, it looks like this same issue was known with the old SSRS Data-Drive Subscriptions also.

Thanks you for the help however. If you have anything else to try let me know please.

[dwa777]

Thanks very much for your reply but this is a little different situation (I think). I'll try and clarify.

I do already have a multi-value parameter set up in my report but the data set is based on a semantic Power BI model that lives in the Power BI workspace. The Paginated Report is run within a Power BI workspace and it works fine just running the report manually from the workspace after selecting the paramater value(s).

I'm trying to set up a "Dynamic Subscription" (in preview) to send copies of this same report via email. The report is fed it's parameters from a table in the semantic model and when setting up the Subscription, you specifify where in the semantic model table the report parameter(s) comes from. My question is how I can set up multiple values in the Dynamic Scription table to pass all at once.

When setting up the Dynamic subscription in the "wizard", you select the name of the parameter and the field in the table where the value(s) for the parameter can be pulled from.

Hope that helps clarify a little. Thanks.

[GilbertQ]

Hi @dwa777 

In order to do that, you've got to use a multi value parameter. Here is how to use this in the old version of reporting services SSRS Using Multi-value Parameters (mssqltips.com)

[dwa777]

Hello again. I've reviewed the links you provided and appreciate your help very much. Just as with SSRS Data Driven Subscriptions it appears there is no simple solution to this issue in Paginate Reports Dynamic Subscriptions either. It looks like it can be done, but passing multiple values to a multi-value parameter list in either one of these technologies is not a straight-forward task. Maybe sometime in the future this will be supported.

If anyone knows anything differently please let me know. Thanks again.