Hi, we’ve developed a proof of concept SSRS report that’s launched from a Power BI report. The SSRS engine is an on-premise server, the regular one that comes with SQL Server 2012, not Power BI Reporting Services.
The Power BI page contains 10 slicers, 2 pie charts visuals and 2 stacked bars visuals and 1 table visual. The idea is that the user will click on a button located at the bottom of the table and launch the SSRS report that will display the full content of the table.
So, we developed a measure that builds the URL passing as parameters the values of all the selected slicers. That measure is then used in the button to launch the URL in a separate browser pane.
The problem is: our 1st slicer is a drop-down list of 800 cost center codes. If the user selects 1 cost center, then the URL will contain only one value for this parameter, like this:
URL = “Path?CostCenter=001”
If the user selects all the cost centers, then all 800 values will be passed, like this:
URL = Path?CostCenter=001&CostCenter=002 … CostCenter=800” plus the other filters
This will give a very long URL of at least 12000 characters, not including the other filters. The other problem is that even if we were able to pass this long URL to the SSRS server, the resulting query will be extremely slow to execute.
The ideal behaviour would be that if the user selects all values in a slicer, then this slicer should be simply ignored.
Do you have any suggestions on how to handle this problem ?
My first idea would be to count the rows in the slicer and compare it with the count of the selected values. If the values are equal, then the filter is ignored. How can we do this in DAX ? The other idea could be that if SelectedValues < TotalValues and SelectedValues > X , then display a warning stating that there's too many values.
Thanks !!
Christian
