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

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

    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's avatar
      stevenamani
      Icon for Advocate II rankAdvocate II
      6 years ago

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