Power BI paginated reports are reports that a user needs to print or share in a printable format. This is a technology that’s been present since the days of SQL Server Reporting Services. These reports are also called “pixel perfect reports”. Why? That’s easy: they look perfect when you print them.
Why paginated reports matter
Power BI paginated reports are a key part of the Power BI ecosystem. They provide the ability to create printable reports with dynamic pagination. Let’s imagine you need to build one report to display all transactions that occur during a week. This report is going to be reviewed by management, used by the accounting team, and sent to clients from time to time. The number of records shown varies; one week it could be 50, another week it could be 150.
The reports need to look perfect when printed and when exporting to a file format like PDF because they are going to be used by the accounting team and possibly sent to clients. Building an interactive report (regular Power BI report) poses several challenges, the main ones being the height of the visual used to display the records and how it looks when exported to PDF, PPT, etc.
This is where paginated reports come into picture.
With paginated reports, we don’t specify the length of the report, meaning we don’t establish how long the report is going to be or how many pages are needed each time we run the report. Instead, the length is dynamic, based on the number of records we have when the report renders. The report will decide how many pages are needed and automatically display them for you.
Another great feature of paginated reports is the Properties pane. This pane provides a deep level of configuration and personalization. The author can configure anything, such as the left border of a cell, the background color, and the font. This level of customization is not found on interactive reports.
What do you need to start building Power BI paginated reports?
There is a free tool to build paginated reports called Power BI Report Builder. It can be downloaded from Download Microsoft® Power BI Report Builder from Official Microsoft Download Center.
Paginated reports are a premium feature and need to be deployed to workspaces having the following licensing options:
- Premium Per Capacity: the workspace is backed by a premium capacity. All users will have access to the reports.
- Premium Per User: each user that wants to access the reports on this kind of workspace needs to have a Premium Per User license.
How to create a Power BI paginated report
Power BI paginated reports don’t contain any data, meaning they do not store any data within the report file. The extension of the file is .rdl. This file contains the report layout, metadata, the data source connection string, datasets, parameters, page break rules, and other configurations. The moment a user opens the report, queries for the necessary datasets run and get the data to source the visuals.
To create a paginated report, let’s start with the Report Data pane. In this pane, authors can add Data Sources, Datasets, Parameters, etc.
The first step is to add a data source which contains the type of data source you will use and the connection string with the details necessary to connect to the data source. Some of the supported data sources for paginated reports are:
- Azure SQL Database
- Azure Synapse Analytics
- Power BI Datasets
- Azure Analysis Services
- Enter Data
Let’s create a sample data source using SQL Server. Go to the Report Data pane, right click on Data Sources, and click on Add New Data Source.
A dataset uses a data source connection to connect to a data source. It only contains metadata: the query that establishes what data is going to be retrieved when the report is run, the columns, the parameters, and so on.
Let’s create a sample dataset. This is how the data looks in our SQL DB table:
To retrieve this data, the dataset will use the following query, which pulls all the records from a table called Sales_By_Country:
To create the dataset, go to the Report Data pane, right click on Datasets, and click on Add New Dataset.
If we use this dataset with one table visual and render the report, this is the result:
What if the report contains hundreds of countries and the user is only interested in one or two? This is where parameters become useful. Parameters are used to filter the data and are commonly used within the queries of the datasets to filter the data returned by the source.
Using our previous example for a dataset, the requirement is that users want to filter the report by country. In our example, to create a parameter we first need a dataset with the values that are going to be available on the parameter/filter. Create a query that will only return the list of available countries and use that in a dataset, like so:
Result of the query:
Let’s create a dataset that will use the above query.
Now that we have the dataset, we can create a parameter and use the values. Go to the Report Data pane, right click on Parameters, and click on Add New Parameter:
Then the trick is to indicate to our main dataset that it needs to consider the new parameter. To do this, we need to modify the original query and add a WHERE clause to filter the data that is equal to the parameter value.
WHERE Country = @Country
The next time the report is opened, the user will need to select a country before it gets rendered. There are multiple ways to handle this, like establishing a default value for one country or selecting all the available values.
One of the best ways to distribute a paginated report is to create a subscription, which is a configuration that allows the user to receive a report periodically via email. Subscriptions can be tailored to send reports at different time periods (daily, weekly, monthly), at any time during the day, and in the format you want (Excel, PDF, PPT).
Open your paginated report on the Power BI Service.
That’s it! We have created our first paginated report with a subscription.
Paginated reports are a key part of the Power BI ecosystem, providing completely different functionality from regular Power BI interactive reports. If you’d like to know more about how paginated reports could work for your own use cases, feel free to contact us.