Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Paginated report with multi valued parameter not working

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.