Power BI Paginated Reports Explained
Paginated Reports have been there for many years and If you have used SQL Server Reporting Services (SSRS), you must be familiar with the paginated reports.
Power BI Paginated Reports are no different from SSRS, they are both meant for creating pixel-perfect reports, optimized for printing, or PDF generation, also they both use the same “rdl” format for the reports.
What scenarios drive your need for Paginated Reports?
Scenario -1: Reports with a huge amount of table data that spans multiple pages.
Scenario -2: Migrate existing canned reports to Power BI
Scenario -3: Create highly formatted, pixel-perfect reports that include numerous data fields, images, matrix tables, etc.
Scenario -4: Need to export large amounts of data from a report to the following formats ( pdf, excel, CSV, ppt, doc, XML..etc), In regular Power BI reports, if the report data exceeds the set limit then we get the below error. But in Paginated reports, there is no limit on the export data.
Scenario -5: Convert the detailed drill through pages and grids in Power BI dashboards with huge column and row data.
How to create a Paginated Report in Power BI?
We can’t use the Power BI Desktop to create a paginated report, so let's see the options to create the paginated reports
Download and Install Power BI Report Builder
To design Paginate Reports we need a new tool called the ‘Report Builder’ Click on the below Microsoft link to download and install the tool
Download Microsoft® Power BI Report Builder from Official Microsoft Download Center
Power BI Report Builder provides a productive report-authoring environment for IT professionals and power users of the…
Once it's installed, you can open the Power BI Report Builder from the Start menu (do not get confused with Power BI Desktop and Report Builder)
Create a paginated Report using Power BI Report Builder
We have a few different options to create a paginated report, for this example, let's go with Blank Report and click on it
Click on the Data Sources and create a data source that you want to connect to
At this moment, the below data sources are supported and the list may grow in the future
Paginated Reports can connect to multiple data sources and can directly bring the data onto the reports from the sources. There is no underlying model like in Power BI Reports
For this example, I am connecting to Azure Analysis Service
The next step is to create a dataset, click on the datasets →Add a dataset
Choose the already created data source from the dropdown and click on the Query Designer. The designer shows all the available data elements in the Analysis Service Model. ( if it's a relational database then it shows all the tables, views, etc)
For this example, I created a sample dataset with customer internet sales details data from Adventurework tabular model.
Now the data source and datasets are ready, it's time to design the report. But before we start designing it, let's understand the different parts of the designer.
From the Tool Bar, click on Table and select Table Wizard, and drag and drop the fields to the respective groups based on the requirement and click Next and choose the layout of report.
Once you are ready with the report layout click on OK and save the report. before we publish the report, preview it and make sure the report works as intended by clicking on Home → Run, and then publish
If you notice the above screenshot, the report has around 1373 pages of data ( that's HUGE). Imagine the same report on the Power BI desktop ( you will end up with a data limitation error)
Publish the Paginated Report to Power BI Services
Now its time to Publish the Paginate Report to Power BI Services, Click on the Publish button on the Toolbar and select the workspace that you want to publish the paginated report
Before you Publish make sure the workspace that you selected is in Premium capacity (We need either a Power BI Premium Capacity (P1, P2, P3 for Gen1 and EM1-EM3, P1-P3, A1-A6 for Gen2 ) or Embedded(A4 — A6 SKUs or test/dev) to host the paginated reports onto the Power BI Service) or Premium per user, which is built on Premium Gen2.
Also, check whether the Paginated Report option is enabled under Admin Portal → Capacity Settings →Workloads → Paginated Reports and turn it ON. and specify Max memory provided for the paginated reports to be used on the Power BI Service.
Click Publish and make sure you see the below window once its published
Cool!! the Paginated Report is Published to Power BI services and ready for your team to use print or export to various formats as shown in the below picture.
Embed a Paginated Report into Power BI Report
To embed a Paginated Report, we need to enable the Preview feature as the feature is in preview state at this movement. Open Power BI Desktop → Go to Options → click Preview Features → Check Paginated Reports check box.
then click on the Paginated Report visual and then click on the Connet to Report
This page shows all the available Paginated Reports that were published to the Power BI services. Select the report that you want to embed and click Set Parameters
As we didn't set any parameters to the customer details report I skipped that step and I can now see the paginated report on the Power BI desktop.
Now we can use this report as a drill through a page or in whatever way you want.
I hope you enjoyed reading this article, I will see you in my next article until then stay safe and enjoy the winter.