Limitations in Power BI Paginated Reports and Their Workarounds

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.

  1. Searching List Parameters
  2. White spacing breaks in Parameter Names
  3. Column renaming while exporting to CSV

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.

      textbox parameter

    • 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.

      DAX syntax

    • 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.

      textbox parameter value

    • Edit the List Parameter and set its Available Values field to the newly created dataset in the previous step.

      Available Values

    • 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.

      run the report

    • When you press Tab, the List Parameter will be narrowed down to the desired result.

      List Parameter

  2. 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.

    Search Property Owner Name

    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.

      XML file

    • 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.

      Power BI Premium service

  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.

    Property Listings report

    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.

    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.

    Report Builder

    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.

    total textboxes

    After 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.

    Power BI Premium

Leave a Reply

Your email address will not be published. Required fields are marked *