Paginated Report - Using URL Parameters and Select ALL

Power BI
1

Hi all,

Our users require the option to download what they are seeing in a Power BI table into Excel. Rather than allowing them to use the Export Data into CSV option, we would prefer to give them a fully formatted Excel download.

Paginated Reports using URL parameters, as described in this Guy In A Cube video: https://youtu.be/KYfhclGrI48, appears to be great for this, but we are struggling to get it to work when we have many values in the slicer.

The issue is that whilst we can pass one or many selected values in the URL, there appears to be a limit and beyond this limit the URL will fail. Please see attached examples:

Paginated Power BI Report.pbix. Mock up report containing one slicer, one table and an Excel download button. Measure Base Export URL creates the URL string to be passed, by concatenating all of the selected values into a URL, which includes the baseURL of the paginated report (pre uploaded into the service)
Paginated Power BI Report.pbix (154.6 KB)

RDL file Paginated Report Test containing a simple table. Parameter ‘Sites’ receives the list of indexes (Store numbers) from the Power BI Report.
Paginated Report Test.rdl (40.4 KB)

How to use:
1, Upload the Paginated Report .rdl file into a Workspace and copy and paste its url from within the service.
2, Open the Power BI report in Desktop and paste the URL into variable __baseURL1 in measure Base Export URL.
3, Select a few sites from the slicer and Ctrl-Click the Excel download button.
4, The paginated report should then open, run and an Excel download will occur.

However if you use ‘Select All’ in the Slicer, the report will fail with the error:
This app.powerbi.com page can’t be found.

If you select all values and then uncheck the last 4 (so that Tesco Teeside is the last selected value), then the report will work. However include Tesco Tetbury and it fails.

I am therefore wanting to find a solution to effectively not pass every value to be checked, but instead being able to pass a ‘Select All’ value.

Any thoughts on how to get around this very much appreciated!

Mark

2

Hi @Mark
I cannot check on the Paginated reports as it requires Premium to publish that I don’t have. Regarding “Select All”, in your PBIX you were trying isFiltered was that not working. I have created below one and it will give individual values if few are selected and Select All in case All or none is selected (that is equivalent to Select All)

Base Export Updated = 

Var x = If (isfiltered('Hierarchy'[Site]),
    CONCATENATEX (
        CALCULATETABLE ( 'Hierarchy', ALLSELECTED ( 'Hierarchy'[Index] ) ),
        'Hierarchy'[Index],
        "&rp:Site="
    ),"Select All")

var baseURL = "https://app.powerbi.com/groups/e29303d3-a2bd-4e1f-b6cb-a85be2cbf72f/rdlreports/6d263e9e-7c2f-4f85-b55b-20febed8a798?rp:Site=@Site"

var baseURL2 = SUBSTITUTE(baseURL,"@Site",X)

RETURN

baseURL2

Let me know if you are looking for something else.

Ankit

3

HI @ankit

Thanks for your input, however unfortunately sending a value of ‘Select All’ does not work. In fact if you look at my measure you can see that I had commented out code which already attempted this.

Hopefully someone with Premium may be able to advise.

Thanks

Mark

4

Hi @Mark. Ok . Will check if can find something related to the issue.

5

Hi @Mark,

I have run into a URL length limit in the past when trying to pass a list of email addresses into a new email window from a web application. I traced the issue to Internet Explorer specifically having a URL length limit of 2,083 characters. There are many posts on this topic, which can be found by doing a Google search using “url length limit”. Your specific issue may be something different given how the cloud-based Power BI app structure works, but this info might point you in the right direction.

For the “Select All” issue, I have experience using SSRS and PBIRS on-prem, and have worked with multi-select report parameters (criteria dropdowns where multiple items can be selected). As a standard with the SSRS reports (which are the paginated reports using RDL format under PBIRS), I support a Select All option by using a blank or 0 default value for each parameter (depending on the data type of the parameter). Then, when the report query receives the parameter, all records are included if the value = blank/0.

I see with your measure that the Hierarchy[Index] is a Decimal Number. It is therefore reasonable to expect that passing a string value of “Select All” will fail because it is the wrong data type. Try passing a 0 instead (specifically, it should be formatted the same as the actual site list, so “&rp:Site=0”). You would then need a way to alter the Sites query in the RDL to handle 0 returning all sites.

Unfortunately I do not have Power BI Premium (or any cloud-based version) so I cannot test this myself. Please let us know if the 0 for all items option works, and how you altered the Sites RDL query to support it if it does work.

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small

1 Like
6

Hi John,

Many thanks for looking into this for me and taking the time to reply.

Regarding the 2,083 URL limit I had also come across this before and did wonder if this was the issue, however we saw the URL fail when it was only 1000.

However, we have actually found a solution thanks to some great support from Microsoft and it turned out to be dead simple!

1, Setup the paginated report up so all parameters are loaded with Default values from the dataset. So if you go to run the standalone rdl report, all the drop down boxes are pre-filled and you will get the entire report.

2, Then for the URL string passed from the calling Power BI report, only reference a parameter if the user has actually selected a value from a slicer! If the user has selected no values from any slicers (eg Select All), then simply submit the default Paginated Report URL string. If a user has selected a value from a slicer, then modify the URL to include a reference to that particular parameter and its selected values.

So in our real world example, where we have 8 slicers, some with 100 values in some of them, we will only add a value to the URL if a user has specifically selected that value/s. This means in 9 times out of 10, when they just want to see all the data, that the URL will be unmodified and just that of the Paginated Report. If they select a few values, we simple include those in the URL.

So much better than having to concatenate ALL possible the values into the URL. I am sure we tried this, but it looks like we missed it. Seems so obvious now!

On a side note, MS did actually confirm that the URL is limited to just 800 chars, beyond that it will fail. However our users would have to really go to town, selecting 30+ values from the slicers to hit this,

Thanks again.

Mark

2 Likes
7

@Mark,

That is great to hear about the default values! Too bad they made the URL even shorter :expressionless:.

Thanks for sharing the results.

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small