Introduction
Power BI has become the de facto standard for reporting in the Microsoft technology stack after announcing the support for hosting Paginated Reports on Power BI Premium. This is a remarkable effort towards embracing legacy reports (SSRS) into the Power BI framework so that the business users can have both Power BI and SSRS reports in one place under the hood of Power BI service.
However, there are some hiccups when merging two vastly different reporting frameworks into one. In this article, we will discuss three limitations of Paginated Reports that are hosted on Power BI Premium and the workarounds to fix them.
- Searching List Parameters
- White spacing breaks in Parameter Names
- Column renaming while exporting to CSV
Read More: Power BI Pricing Model
What You Should Know
Before you start the blog, these are the things that you should be familiar with:
- Power BI and SSRS
- Paginated Reports
- Power BI Report Builder
Limitations
1. Searching List Parameters:
Problem
In SSRS, we can search a List Parameter by clicking on it and typing letters or numbers, and it would take us to the searched value where we can select it.
Example
For example, if we have the names of countries in a List Parameter, and we press the keys ‘A’ + ‘U’ + ‘S’, it will take us to Australia.
However, this functionality is not supported in Power BI Paginated Reports. When we click on a List Parameter, we cannot search it. Therefore, the user may have to scroll through thousands of values in a list to select a single value.
Workaround
To fix this limitation, we provide a text box Parameter with each List Parameter. The user can write the value to be searched in that textbox Parameter, and the List Parameter will be narrowed down accordingly.
For example, if the user writes ‘Aus’ in the text field, the List Parameter will be narrowed down to ‘Australia’ and ‘Austria’.
Process
I have a sample dataset of over a thousand property listings. For each property, we have a property owner. Consequently, there are more than a thousand owners. If we provide the property owner name in the List Parameter, we will have to scroll through all those values to find the one we are looking for.
- To work around this issue, first, create a textbox parameter in which we will enter the name to be searched.
- Create a new dataset that contains all distinct values of the property owner names to be searched. By creating a new dataset just for the distinct values of the names, we will have a performance gain querying this small dataset rather than querying the whole table. I have used DAX syntax to query the data since I am sourcing data from the XMLA endpoint of the Power BI Dataset.
- Use a parameter in this dataset (hostname in this case), which will filter out values for the List Parameters. Set the value of the hostName parameter to the textbox parameter value
- Edit the List Parameter and set its Available Values field to the newly created dataset in the previous step.
- The textbox will now serve as the search box for the List parameter.
Note: When you enter a value in the textbox, use Tab instead of Enter because Tab will refresh the List Parameter whereas Enter will run the report.
- When you press Tab, the List Parameter will be narrowed down to the desired result.
Transform Limitations into Opportunities!
Overcome Power BI Paginated Reports limitations with AlphaBOLD's Power Platform solutions. Let's turn challenges into opportunities – Connect with us for innovative solutions tailored to your reporting needs.
Request a Consultation2. Whitespace Breaking in Parameter Names:
Problem
If you have created parameters in Power BI Paginated Reports that comprise of more than 15 characters, you will notice that the UI starts falling apart.
It squeezes the text and the fields of the parameters by breaking up the whitespace after 15 characters and moving the text on the next line.
Example
In the example below, the parameter Search Property Owner Name exceeds the limit of 15 characters, and it is split up into two lines.
This problem doesn’t exist in Power BI Report Builder. In fact, it arises as soon as we publish a report to the browser, and it results in a User Interface that isn’t aesthetically pleasing, to say the least.
Workaround
To tackle this breaking whitespace issue, we adopted quite an unorthodox approach. There is an ASCII character 0160 that isn’t categorized as whitespace, but it acts like it. It is called a non-breaking space. When we use this ASCII character instead of spaces, the problem magically disappears.
Process
- To solve this problem, open the Paginated Report in a text editor that supports writing ACSII characters. I used Notepad++ for this report. Notice that the Paginated Report is basically an XML file.
- Locate the <Prompt> tag in the XML file that contains the name of the parameter.
- Replace the spaces with ASCII character 0160. To write ASCII in Notepad++, press and hold ‘ALT’, and then press ‘0160’ through the Numpad. If you use the number keys above the QWERTY layout, it will not work. It must be Numpad keys to convert them into ASCII characters.
- Save the file and upload it to the Power BI Premium service. Now when you open the report, it will not show the whitespace breaking issue. You can add additional spaces after the text to match the alignment of the text boxes.
3. Column Renaming While Exporting to CSV:
Problem
When we export a Paginated Report to CSV format, there are certain limitations that are inherent to the CSV format itself. For example, the CSV format can’t show subtotals and totals that are an integral part of Paginated Reports and Excel reports. Also, we can format Column headers and rows, change their colors, font, and weight in Paginated Reports, but we can’t have that functionality in a CSV file.
Example
Below is a screenshot of the Property Listings report that contains sub-totals and totals for properties owned by individuals.
However, when we export this report into CSV format, we can immediately notice that the layout is vastly different from the Paginated Report. There are only five columns in the Paginated Report, but the CSV is showing four additional columns. Also, the names of the columns are not very meaningful. What’s happening is that the column names are being selected from the textbox names of the Paginated Reports
Workaround
The four additional columns are the sub-total and total values against each row. Instead of showing them on separate rows (as in the Paginated Report), they are appended as columns against each row. This is because the CSV format doesn’t support any grouping or aggregation.
As for the column naming, these names are picked from the textbox names of the Paginated Reports. We need to change the textbox names of the report itself so that this change reflects in the exported CSV.
Process
Open the report in Report Builder and check the Properties checkbox in the View tab.
Then, click on the individual textboxes, and their names will be shown in the Properties menu on the right side. Change the names of the textboxes from here. There is a limitation as to what characters can be used for the textbox naming. You can only use alphabets, numbers, and underscores.
Also, click on the sub-total and total textboxes and change their names accordingly.
Elevate Your Reporting Standards!
Ready to go beyond Power BI Paginated Reports limitations? AlphaBOLD's Power Platform Consultancy is your key to elevated reporting standards. Contact us to explore tailored solutions and redefine your reporting experience.
Request a ConsultationAfter changing the textbox names, publish the report on Power BI Premium and export the results again. The CSV will now have the updated column names. Notice that the last four columns have the prefixes Subtotal and Total because they have the aggregated values for Price and Number_Of_Reviews columns.
Conclusion
In conclusion, this blog has shed light on the limitations that users may encounter when working with Power BI Paginated Reports within the Power BI Premium environment. We’ve explored three key challenges and provided comprehensive workarounds for each, ensuring you can harness your reporting endeavors’ full potential.
By addressing issues such as searching list parameters, handling whitespace in parameter names, and managing column renaming when exporting to CSV, we’ve equipped you with practical solutions to enhance your reporting experience. These workarounds empower you to navigate and mitigate the constraints that occasionally arise in the world of data visualization and analytics.
We encourage you to explore these workarounds and transform limitations into opportunities for more robust and effective reporting. However, if you seek further assistance or custom-tailored solutions for your unique reporting needs, our team at AlphaBOLD is here to collaborate with you.
Explore Recent Blog Posts
