Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkSL
Helper V
Helper V

Paginated Report - Using URL Parameters and Select ALL

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 2, 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 examples uploaded to Dropbox:

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 Report Test.rdl 

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


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.

 

If you run the report with one or a few sites selected, then the Excel will generate.

If you use ‘Select All’ in the Slicer, the Excel 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

1 ACCEPTED SOLUTION

Ok, after much investigation and great support from Microsoft, we found the solution and it was dead simple!

Set the paginated report up so all parameters are loaded with Default values from the dataset. And 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.

View solution in original post

11 REPLIES 11
audministrator
Frequent Visitor

To build a multi select drop down box in a paginated report. Takes quite a few steps which are not so easy to figure out... 

 

Therefore I created a step by step guide on how to :

https://audministrator.wordpress.com/2019/12/20/power-bi-report-builder-using-parameters/

 

Hopefully this can help !

Hi @audministrator 

Thanks for the input and the guide.  However my Paginated Report does already have this functionality - please download the example rdl file I supplied.  When you run the report you will see there is a dropdown called Site.  Here I can successfully select one, a few or Select All and the report works.

 

The query is, here can I select all values when calling the Paginated Report from my Power BI report, using the URL Parameters.  The Guy in a Cube video, demonstrates with just a single value being supplied.  However you can concatenate the values together, but there is a limit before the URL fails.  I am hoping there is a way, using URL Parameters, to 'Select All' and not have to pass seventy, eighty individual values in the URL string.

 

Thanks

 

Mark

Hi @MarkSL, I see that you can select multiple values in your paginated report. In my paginated report I have a parameter (text but with no available or default values) which can have multiple values. The problem is a parameter with multiple values in a paginated report is written with a line break between the values, but when I import the values of the parameter from my dashboard in Power BI, the parameters are written in a list (Y,N for example), so the values are not detected and I have a blank report. Also, if I add some available values so that I can have a dropdown, none of the selected values are detected in my paginated report. I hope you can help me with this. Thank you in advance!

Hi,

From remote it's hard to figure out what is going wrong ?

 

But did you get all the settings an correct in your report to build the proper parameter settings?

I definitely can recommend this video (if you have not seen it yet) on how to get started to make sure all settings are correct.

https://www.youtube.com/watch?v=XA5twJ2wLd0

Once you have the parameter dataset correct in your report, you can run a query on the HIDDEN PARAMETER DATASET to further debug the isseu.

 

 

 

 

Actually I have a problem when passing the URL parameters from my dashboard in power bi desktop to my paginated report in Power BI service

@MarkSL  

 

Is this is not a working solution where you test on whether the parameter value is NULL (Empty).

And next create a DAX condition that if it is NULL you will take 'ALL' all a default value.

 

Example : 

https://prologika.com/ssrs-multivalue-parameters-in-dax/

 

EVALUATE
SUMMARIZECOLUMNS (
‘Date'[Fiscal Year],
FILTER (
VALUES ( ‘Date'[Fiscal Year] ),
(
            OR (
                ( @DateFiscalYear = “All” ),
                PATHCONTAINS ( @DateFiscalYear, ‘Date'[Fiscal Year] )
            )
)
),
“Internet Total Sales”, [Internet Total Sales],
“Reseller Total Sales”, [Reseller Total Sales]
)

 

Ok, after much investigation and great support from Microsoft, we found the solution and it was dead simple!

Set the paginated report up so all parameters are loaded with Default values from the dataset. And 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.

Anonymous
Not applicable

Hi,

Even I am trying to create a paginated report and trying to pass url parameters from my power BI report. In my case i have about 6 slicers in my Power BI which needs to be passed to my paginated export. Slicers selection could be more than 1 value based upon user selection criteria.

I see that you have already implemented this logic. Can you please share with me your sample pbix and rdl.

Thanks.

Hi @MarkSL  do you still work with that "workaround" by setting default values to parameters? I have the same challenge but in my scenario (cascading parameters) setting default is no option because the number of vlaues in the lowest level parameters are then to high.. really sh*t that there is no option to simulate the "all selected" via URL 😞

@MarkSL 

 

Good to hear it is working after 'ALL' 😉

 

So we where looking a bridge too far to make it work ...

 

Thanks for posting back the final solution ! 🤗

Haha, I like what you did there!  Thanks for your help though 🙂

 

It has been a big reflief to get it sorted as we really wanted the ability to offer our users fully formatted Excel and PDF extracts of their filtered data.   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 any 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 every sli er value 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.

 

Mark

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors