Multi Parameters

BishwaR — Thu, 26 May 2022 21:19:36 GMT

I am working on Report Builder Paginated Report. I have three parameters Company Name, State and City

When I select the value for the State ( just one parameter) it asks me to input the value in the rest of the parameters also then only I can view the report.

Is there any way I can use the single parameter without inputting the value in the other two parameters?

Thank you

Re: Multi Parameters

v-luwang-msft — Mon, 30 May 2022 09:03:06 GMT

Hi BishwaR ，

A sample you could refer:

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.(in my opinion, this is what you want)

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

To learn more details ,refer:

https://community.powerbi.com/t5/Desktop/Paginated-report-with-multi-valued-parameter-not-working/m-p/919338

Best Regards

Lucien

topic Re: Multi Parameters in Report Server

Multi Parameters

Re: Multi Parameters