{"id":970,"date":"2020-12-07T20:42:49","date_gmt":"2020-12-07T20:42:49","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=970"},"modified":"2023-02-28T14:48:35","modified_gmt":"2023-02-28T14:48:35","slug":"power-bi-paginated-reports-multi-select-url-parameters","status":"publish","type":"post","link":"https:\/\/jackofalltradesmasterofsome.com\/blog\/2020\/12\/07\/power-bi-paginated-reports-multi-select-url-parameters\/","title":{"rendered":"Power BI Paginated Reports & Multi Select URL Parameters"},"content":{"rendered":"\n

When building drill to \/ drill thru reports in Power BI Paginated Reports & Multi Select URL Parameters, the best way to do this is using an Action URL as the basic default linking does not work. Here are the steps to call a child report using multiselect parameters to a child report.<\/p>\n\n\n\n

Side Note: Want to learn SQL <\/a>or Python <\/a>for free. In less then 10 minutes a day and less than an hour total? Signup for my free classes delivered daily right to your email inbox for free! <\/em><\/p>\n\n\n\n

\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n

Now back to the article…<\/p>\n\n\n\n

Setting up the Child Report <\/h2>\n\n\n\n
  1. Deploy the child Report to app.powerbi.com and run the report to obtain the URL for the report.
    1. This cannot be done directly from app.powerbi.com as this URL will not render when passing parameters in the URL<\/li><\/ol>
      1. Copy and paste this URL into a Notepad window<\/li><\/ol>
        1. Below are the parameter dataset query and multiselect parameter set up in the child reports.<\/li><\/ol><\/li><\/ol>\n\n\n\n
          \"\"<\/figure>\n\n\n\n
          \"\"<\/figure>\n\n\n\n
          • It is best to test with the URL before adding this to your parent record
            • Paste the URL into a web browser to confirm the child reports work<\/li><\/ul><\/li><\/ul>\n\n\n\n
              • Hardcode the passing of parameters in the URL\nEdit the URL in notepad by adding \u201c&rp:parametername=xyx\u201d to the end where\nthe parameter name is the exact same name as the name in the Report builder\ntool. If you need to add more, continue to add \u201c&rp:parametername=xyx\u201d to\nthe string.<\/li>
              • I.e\n\u201chttps:\/\/app.powerbi.com\/groups\/me\/rdlreports\/617a3daa-0d7b-412b-9vs2-0213123123?ctid=eeb53a15-a2e4-46de-8608-22657ab58979&rp:param1=test&rp:param2=202012\u201d<\/li><\/ul>\n\n\n\n
                • For multiselect parameters, you can add the same\nparameter to the string to select multiple values\n\u201c&rs:parametername=xyx&rs:parametername=xyx&rs:parametername=xyx\u201d<\/li><\/ul>\n\n\n\n

                  Building the Parent Report <\/h2>\n\n\n\n
                  • Now that we have a working tested URL, Head back to the parent report to build the dynamic URL. In the field that needs to contain the drill to, right click the box and select \u201cTextbox Properties\u201d<\/li><\/ul>\n\n\n\n
                    \"\"<\/figure>\n\n\n\n
                    • Select \u201cActions -> URL\u201d and use the Fx to create an expression<\/li><\/ul>\n\n\n\n
                      \"\"<\/figure>\n\n\n\n
                      • The expression should read =\u201dURL&rp:parametername=\u201d + Parameters!paramtername.Value. You can use the string function to concatenate the actual parameter values on the parent report to create a dynamic URL at run time that matches what you have in your test notepad file. <\/li><\/ul>\n\n\n\n
                        \"\"<\/figure>\n\n\n\n
                        \"\"<\/figure>\n\n\n\n
                        • For multlselect parameters you can append the code JOIN(Parameters!parametername.value, \u201crs:parametername\u201d) and this will dynamically create a string of all the parameter values needed for the multiselect.<\/li><\/ul>\n\n\n\n
                          \"\"<\/figure>\n\n\n\n
                          1. To test, put this string in a Text box\nexpression on the report so you can see it render at runtime to be able to\nexport and test.<\/li><\/ol>\n\n\n\n

                            Large Multi Select Parameters<\/h2>\n\n\n\n
                            1. If you have a multiselect that is large, it may exceed the 2048 characters of a URL available when generating the dynamic string. For this to work, in your parameter dataset, add a Union to a \u201cAll\u201d value. Make \u201c0\u201d the default in your parameter as well, which represents the \u201cAll\u201d value.<\/li><\/ol>\n\n\n\n
                              \"\"<\/figure>\n\n\n\n
                              \"\"

                              <\/figcaption><\/figure>\n\n\n\n
                              \"\"<\/figure>\n\n\n\n
                              1. When you run the report, the All parameter should be selected by default.<\/li><\/ol>\n\n\n\n
                                \"\"<\/figure>\n\n\n\n
                                1. Go back to the report to create the full select statement for the values of the report in your main query and reference the parameter with a Or Statement the uses the new default \u2018All\u2019 value.<\/li><\/ol>\n\n\n\n
                                  \"\"<\/figure>\n\n\n\n
                                  1. The report will run with the default \u201cAll\u201d value and bring back all results. <\/li><\/ol>\n\n\n\n
                                    \"\"<\/figure>\n\n\n\n
                                    1. The same logic can now be applied to the child\nreport when the parameter value is passed down in the dynamic URL<\/li><\/ol>\n\n\n\n

                                      Power BI Paginated Reports & Multi Select URL Parameters<\/p>\n","protected":false},"excerpt":{"rendered":"

                                      When building drill to \/ drill thru reports in Power BI Paginated Reports & Multi Select URL Parameters, the best way to do this is using an Action URL as the basic default linking does not work. Here are the steps to call a child report using multiselect parameters to a child report. Side Note: […]<\/p>\n","protected":false},"author":1,"featured_media":969,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[96],"tags":[101],"class_list":["post-970","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-power-bi","tag-power-bi"],"_links":{"self":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=970"}],"version-history":[{"count":2,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/970\/revisions"}],"predecessor-version":[{"id":1309,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/970\/revisions\/1309"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/969"}],"wp:attachment":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}