Power BI Desktop is a wonderful business intelligence tool that has an expansive list of reporting capabilities, but there is one thing that you aren’t able to do with it – create a paginated report, which is a specific style of report that is designed to be exported or printed.
Enter Power BI Report Builder, a tool that was released by Microsoft in 2019 that enables users to design and create paginated reports and then share them via Power BI service.
In this blog, we will provide an introduction to Power BI Report Builder, explore its purpose, interface, and available data connections, and discuss certain limitations.
What Is Power BI Report Builder?
Before we answer this question, it is important to understand what a paginated report is. It is typically a tabular (table-based) report designed to fit well on a page that follows the exact format that the developer defines. Power BI Report Builder is the tool that enables users to develop paginated reports in Power BI.
Without Report Builder, you are not able to create paginated reports or edit existing ones within the Power Platform ecosystem. The only file type that Report Builder can open is .rdl (report definition language).
Power BI Report Builder is free to install and use on your local machine, and you are able to publish paginated reports to My Workspace (your own personal workspace) as long as you have a Microsoft account. However, you will need a Power BI Pro or Premium Per User (PPU) license in order to publish paginated reports to other workspaces.
Go to your “My Workspace” and log into your account (or create one if you don’t have one yet). Along the left side of the page, select Workspaces, and then click My Workspace.
You can download Power BI Report Builder here.
Getting Familiar With Power BI Report Builder
When you open Power BI Report Builder for the first time, this is the screen that you will be greeted with. You can open an existing report or start a new one.
The section with the red box around it is the design surface. This is where you will design your paginated report. Whatever you design here will essentially work as the report’s template, and real data will not appear here until you run the report. For example, the “[&ExecutionTime]” shown in the image below will be populated with a date and time when the report is run.
Above the design surface is the toolbar, where you have different tabs to change the formatting/setup of your report. To the left of the design surface is the Report Data pane, where you will be able to see all of the built-in fields (automatically available in any paginated report), parameters, images, data sources, and datasets that are in your report.
Below the design surface is where you are able to see any Row Groups or Column Groups that have been defined for the report.
These groups work in a similar fashion to hierarchies where one field acts as a parent of another. For example, imagine you’ve got a list of products, and each of those rolls up into a certain category. In this example, the category would be the parent, and the product would be the child in the group/hierarchy.
This is a closer look at the Report Data pane. The built-in fields are available for any report opened in Report Builder and are very useful for formatting and exporting purposes. The other folders are empty right now since we don’t have any data in the report yet.
The Home tab is where you will run your report. When you click the “Run” button, this will populate your report template with data from your dataset(s). The Home tab is also where most of your formatting options are available. Lastly, this tab is where you would go to publish your paginated report to the Power BI service.
If I were to click the “Run” button right now, the report would still run even without any datasets existing in the file. It would just show the date and time that the report was run.
The image below is what shows when I run the report. I changed the background to light blue so you could see the size of the default design surface. Take note of the toolbar here. You can zoom in and out, navigate through the pages (paginated reports often span multiple pages), refresh the report, edit the page setup and print layout, and export it out of Report Builder (PDF, Excel, CSV, etc.). To switch back to the Design mode, simply click the Design button in the toolbar.
You are able to show/hide the different panes around your design surface. The image below shows what your canvas would look like with all of the boxes checked in the View tab. By default, the Parameters and Properties boxes are unchecked. Notice the addition of the Parameters and Properties panes after checking those boxes in the view tab.
Available Data Source Connections
At the time this blog post was written, Power BI Report Builder was somewhat limited in its data source connection options. They are listed below. The data sources with a “*” indicate that they require a Power BI gateway in order to access and share reports on the Power BI service.
- Power BI Dataset
- Microsoft Dataverse
- Microsoft Azure SQL Database
- Azure Analysis Services
- Azure Synapse Analytics
- Microsoft SQL Server Database*
- Microsoft SQL Server Analysis Services*
- Oracle Database*
- Teradata*
- ODBC*
- Enter Data (manually enter your own data)
One thing to note is that a Power BI Dataset enables you to connect to any data source that Power BI Desktop has a native connection to. When you connect to a Power BI dataset in Report Builder, the query language will be in DAX syntax.
For example, let’s say there is a Power BI dataset that uses an Excel file as a data source. Report Builder doesn’t have a native connection to Excel, but you could connect to that Power BI dataset that uses Excel as a data source. A Power BI dataset can essentially work as a bridge to allow connections to other data sources.
Power BI Report Builder Quick Facts
- Free to install and use on your local machine, but will require a Pro or PPU license in order to publish to workspaces other than My Workspace.
- Can be used to open and edit any file with the .rdl file extension, even if it was authored using another software, like SQL Server Reporting Services (SSRS), for example.
- Can be used to migrate reports from SSRS to Power BI. See Microsoft’s documentation on the process here.
Power BI Report Builder Limitations
- Limited documentation and resources (this has been growing since the start of 2023).
- Few native data source connections.
- Requires data gateways for on-premises data sources.
- Users cannot publish paginated reports to workspaces other than My Workspace without a Power BI Pro or Premium Per User license.
- Using report parameters becomes increasingly more difficult with non-native data source connections.
Conclusion
If you are interested in creating paginated reports in Power BI, then Report Builder is the tool for you. It can be difficult to learn at first, but once you do, it is a valuable skill to have. There is a small list of native data source connections available, but you are able to push the boundaries on that with Power BI datasets or ODBC drivers.
Author’s side-note
After using Power BI Report Builder over the course of several months for a project, I found that the tool became less intuitive as the report(s) became more complex, and the documentation was somewhat limited. However, it appears that more documentation has become available over the past couple of months, which signals that the tool is continuing to evolve. For any other style of report, I would recommend using Power BI Desktop, as it is a more robust reporting tool.
Want to learn more about Power BI Report Builder?
FAQs
You should use Power BI Report Builder when you want to create a paginated report. Because Report Builder was designed specifically for designing paginated reports, it has different functionality than Power BI Desktop. If you are looking to create any other style of report, I would recommend Power BI Desktop, as it is a much more robust tool.
The sole purpose of Power BI Report Builder is to create paginated reports, while Power BI Desktop is used to develop robust, interactive visualizations, reports, and dashboards. In general, Power BI Desktop has a much wider array of capabilities than Report Builder.
