power bi

Power BI Paginated Reports & Multi Select URL Parameters

by

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: Want to learn SQL or Python 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!

Now back to the article…

Setting up the Child Report

  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
    1. Copy and paste this URL into a Notepad window
    1. Below are the parameter dataset query and multiselect parameter set up in the child reports.
  • 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
  • Hardcode the passing of parameters in the URL Edit the URL in notepad by adding “&rp:parametername=xyx” to the end where the parameter name is the exact same name as the name in the Report builder tool. If you need to add more, continue to add “&rp:parametername=xyx” to the string.
  • I.e “https://app.powerbi.com/groups/me/rdlreports/617a3daa-0d7b-412b-9vs2-0213123123?ctid=eeb53a15-a2e4-46de-8608-22657ab58979&rp:param1=test&rp:param2=202012”
  • For multiselect parameters, you can add the same parameter to the string to select multiple values “&rs:parametername=xyx&rs:parametername=xyx&rs:parametername=xyx”

Building the Parent Report

  • 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 “Textbox Properties”
  • Select “Actions -> URL” and use the Fx to create an expression
  • The expression should read =”URL&rp:parametername=” + 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.
  • For multlselect parameters you can append the code JOIN(Parameters!parametername.value, “rs:parametername”) and this will dynamically create a string of all the parameter values needed for the multiselect.
  1. To test, put this string in a Text box expression on the report so you can see it render at runtime to be able to export and test.

Large Multi Select Parameters

  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 “All” value. Make “0” the default in your parameter as well, which represents the “All” value.

  1. When you run the report, the All parameter should be selected by default.
  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 ‘All’ value.
  1. The report will run with the default “All” value and bring back all results.
  1. The same logic can now be applied to the child report when the parameter value is passed down in the dynamic URL

Power BI Paginated Reports & Multi Select URL Parameters