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!

[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.