Paginated report with multi valued parameter not working

[Anonymous]

Hi,

I am trying to pass muliple values in the paginated report, i.e. not multiple parameters.

For instance I am trying see users aged 21, 22, 23, when I am passing 21 it works fine. But when I pass all three values it doesn't work. i.e. it gives blank page.

Any suggesstions are much appreciated.

I have posted it in the ideas Please click here to open .

Regards,

Uday

[Anonymous]

What was your final solution for power bi dataset?

[Cluster_Ovs_R]

Hi,
i have the same problem but with driver ODBC so i use ? instead of @ 
And this solution is not working,do u know something??

[Narukkp]

Hi Team,

I got the solution, We need to give the expression like below

Dim_Corp_Hier/Cur_Area_Nbr in ('" & Join(Parameters!Area.Value,"','") & "')

[v-lid-msft]

Hi @Anonymous ,

Based on my research, We need to set Allow multiple values property for the parameter, please refer to this document: https://docs.microsoft.com/en-us/power-bi/report-builder-parameters#bkmk_Report_Parameters

If you need to Pass a report parameter in a URL , The format for a multi-value parameter is to repeat the parameter name for each value. please refer to the example in this document: https://docs.microsoft.com/en-us/power-bi/report-builder-url-pass-parameters#additional-examples

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft ,

I have set the parameter to be multi valued and also in the dataset I have set the default to select all the values as showed in the below video.

https://www.youtube.com/watch?v=KYfhclGrI48&t=922s

Regards,

Prime

[v-lid-msft]

Hi @Anonymous ,

How about the result if you repeat the parameter name for each value in a URL?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lid-msft ,

I have managed to send the selected parameters using the concatenatex function, i.e. to split using a comma.

But in the Paginated report when I try to run the report by passing multiple values it give me blank records and when I use one value then it works fine.

[Anonymous]

I was about to create a post asking if it's possible to filter a dataset or tablix with multiple values from a multi-value parameter, as I've tried every trick in the book that I can find for SSRS, but nothing works. I'm thinking this is a bug. A costly bug at that in the case of this project!

[Anonymous]

Hi @Anonymous ,

Looks like that is the case, I have created a incident with the microsoft support team, and he advised me to go through the following websites

1. Use of DAX PATHCONTAINS: https://prologika.com/ssrs-multivalue-parameters-in-dax/
2. Another Example: https://blog.crossjoin.co.uk/2018/05/16/a-new-approach-to-handling-ssrs-multi-valued-parameters-in-dax-queries/
3. About the @RSCustomerDaxFiler (available only inside the PBI Report Builder Query Designer): https://blog.crossjoin.co.uk/2019/11/03/power-bi-report-builder-and-rscustomdaxfilter/

But the solution in these links work with SSAS data source and not PowerBI data source. The guy himself tried with a remote dektop session and he couldn't figure it out.

I am waiting for him to get back to me, Once I hear anything will give you an update.

[stevenamani]

I am also having this same issue. Passing multi-value parameters in the URL doesn't work.

Has there been any progress on this issue?

[Anonymous]

Hi @stevenamani ,

I have finally managed to crack this,

Things to do in the Paginated report:

1. Create a main dataset and then the other filter datasets, for example we have a 2 parameters (Division and Site)

2. In the main dataset add this code 

FILTER(VALUES('Sites'[Division]),OR(@Division ="All",PATHCONTAINS(@Division,'Sites'[Division]))),
FILTER(VALUES('Sites'[Site]),OR(@Site="All",PATHCONTAINS(@Site,'Sites'[Site]))),

3. Under parameters section of the main dataset add this for the relevant parameters

=Join(Parameters!Division.Value,"|")

=Join(Parameters!Site.Value,"|")

4. Under parameters make sure that you are allowing multiple values and the available values and default values are set to the relevant parameter datasets.

Finally make sure that when you run the paginated report  it should give you all the results i.e. the parameters are optional and unless you select the parameters the data should not be filtered.

Things to do in the PBI reoprt

1.Create a measure to export 

Excel Export URL =

VAR __Division =

IF (

NOT ( ISFILTERED ( Sites[Division] ) ),

BLANK (),

"&rp:Division="

& CONCATENATEX ( ALLSELECTED ( Sites[Division] ), Sites[Division] , "&rp:Division=" )

)

VAR __Site =

IF (

NOT ( ISFILTERED ( Sites[Site] ) ),

BLANK (),

"&rp:Site="

& CONCATENATEX ( ALLSELECTED ( Sites[Site] ), Sites[Site], "&rp:Site=" )

)

VAR __baseURL = "https://app.powerbi.com/groups/e29303d3-a2bd-4e1f-b6cb-a85be2cbf72f/rdlreports/5670f37c-f056-4cc5-8c...?" &__Division&__Site

VAR __FinalURL = CONCATENATE(__baseURL, "&rdl:format=EXCELOPENXML")

RETURN

__FinalURL

***** Make sure that you replace the __baseUrl hyperlink with your rdl report link. The best way to do so is to run the rdl report on the power BI service and copy it.

2. Add the measure to pass parameters to the excel.(in my report I have downloaded a excel image and in the action select web url and select the measure you have created).

Hope this makes sense.

[chiragdesai25]

Hi, I didnt understand your point 2 of the solution (In the main dataset add this code). Where do mean to add the code? In the Where clause or Select Caluse?

[Narukkp]

Hi Team,

How we can achive the same in source as sql in report builder. In above example source as power bi report in report builder so that we can use that dax expressions but in my case source as sql server in report builder and using URL action we are trying to call the power bi serice report in report builder and it is working as expected for single selection but multi selection parameters it is not working

[Neka]

I know this post is super old but in case it helps anyone else with this problem. I solved this issue when using a stored procedure that connected to my paginated report. You need to add the following code to your WHERE clause in the stored procedure: 

WHERE YourDesiredField IN(SELECT TRIM(value) FROM STRING_SPLIT (@YourParam, ',')).  

This will split up the multiselect into individual strings instead of showing as one giant string. This is why no data shows up in the report with the multiselect, because it shows up as 'Item1, Item2, Item3' instead of 'Item1', 'Item2', 'Item3'. 

[Anonymous]

That join solution WORKS. Thank you so much for posting this, I searched through about 10 other blog posts looking for the right answer. 

Best,

Rob

[Anonymous]

Hello Prime257,

I followed your approach for passing multiple values in url, itw working perfect. thank you so much for the solution. However, with the code you provided to put as a filter in report dataset code (FILTER(VALUES('Sites'[Division]),OR(@Division ="All",PATHCONTAINS(@Division,'Sites'[Division]))),) . My paginated report loses the ability to set a default value for parameter. So when I run the report it doesnt take the default values instead it throws a warning that value is required for paramter. Same with the url, unless untill i select all parameters values via power bi report, paginated report renders and if i dont select even single paramter, it  gives me a warning to select a value of that unselected parameter value. Any thoughts?

Thanks for the help.

[moons]

Hi  @Anonymous

I have similar situation that needs to pass multiple values to paginated reports from power BI.  I'm new to paginated report/report builder, so would yo be able to provide the steps for things to do in paginated report. In my case I already added parameters in paginated report with multiple value. If you can post screen shot would be great!

Thanks!

[stevenamani]

Thank you, I will give this a shot.