Forum Discussion
Paginated report with multi valued parameter not working
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
***** 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.
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
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'.
