Integrating Power BI report with Paginated report

Let us consider a scenario where we need to export more than 150K records from a detailed page of the power bi report. In such cases, we can’t directly export that many records from the power bi report. We can export unlimited records from the paginated report also Power BI report can be integrated with paginated reports by passing Power BI slicer values into the paginated report parameter. Let’s see how to integrate the Power BI report with the Paginated report.

Prerequisite:

We should have a premium capacity workspace to publish the paginated report into the Power BI service.

Creating a Paginated Report:

Once you created the Power BI report, publish it into Power BI Service (No need to publish in the premium capacity workspace), This published Power BI data model will be used as a data source for the paginated report. No need to create the dataset for paginated report parameters because it will get the input values from Power BI Slicer values. Once you developed the paginated report, save it as on Power BI Service (Need to publish in the premium capacity workspace). Then we can get the Paginated report URL. (Refer figure 1.1)

                                                                                           Figure 1.1

URL Access Syntax:

We need to prefix the parameter name that is used in the paginated report with rp: to recognize it in the URL. Refer to the below syntax to specify the parameters within the paginated report URL.

• Single Parameter:

     rp:parameter_name=value

• Multiple Parameter

     rp:parameter_name=value&rp:parameter_name=value

Refer to the below syntax to specify the Export format.

     rdl:format=CSV

Example: Paginated report Link?rp:Region=South&rp:Region=West& rdl:format=CSV

Steps:

1. Go to the Power BI report. (Refer figure 1.2)

                                                                                         Figure 1.2

2. Creating a measure with the following DAX to append parameters and values to the paginated report URL. It will be the dynamic paginated report URL.

If we select South and West from Region Slicer, this measure will return the below value                                                    https://app.powerbi.com/groups/me/rdlreports/xxxxxxx-abc7-40f0-b456-febzf9chya4d?rp:Region=South&rp:Region=West&rp:FromDate=1/3/2014&rp:ToDate=12/30/2017&rdl:format=CSV

3. Once the measure created, make it a web URL. Go to Measure Tools > Data category > Web URL

4. Create a button with the text “CSV Export”. Then, go to Action, select “Web URL” in the Type field then click fx (Conditional formatting). This will open the menu asking which field or measure we want to use to drive the button. Select the measure that we created for accessing the Dynamic Paginated report URL. (Refer figure 1.3)

                                                                                         Figure 1.3

5. If we click this button, it will hit the dynamic Paginated report URL also it will export the data automatically in CSV format (Refer figure 1.4)

                                                                                           Figure 1.4

Limitation:

Each web browser has its own maximum URL length limit. If the length of the dynamic paginated report URL is exceeding the maximum URL length of the browser, the Dynamic paginated report URL can’t be accessed.

This blog helps to integrate the Power BI report with the Paginated report. Please feel free to reach Cittabase for more information. Visit our blogs for more topics on Power BI.