Paginated Reports 101 – Building your first Paginated Report off of a Power BI dataset

 

Note: this blog post is part of a short series covering how to convert a standard Power BI report (a .pbix file) into a Power BI paginated report (an .rdl file) using the same underlying Power BI dataset. A companion video is available at the bottom of this post —and supporting files are available here on GitHub for those who would like to follow along.

Using a Power BI Dataset as your Power BI Report’s data source? Even the title of this blog post is confusing!

Stay with me—I promise it will start making more sense from here. Let’s start by breaking down how information from a .pbix file becomes a dataset in the Power BI Service, and then we’ll see how it fits into a paginated report in Power BI Report Builder.

Power BI Desktop software is what you use to create (what I refer to as) a “standard” Power BI report. Most people just call these Power BI reports, since their paginated report cousins were tacked on to the Power BI Service several years after Power BI was launched. Power BI Desktop generates a .pbix file. When you publish a standard Power BI report to PowerBI.com (the Power BI Service), you’ll notice that your published .pbix file has been split into two distinct components: 

A Standard Power BI report (the first item listed in the above image in blue) contains the visuals you designed in your Report view in Power BI Desktop. This is the same subset of your .pbix file that is generated when you export a Power BI template from Power BI Desktop, resulting in a .pbit file.

Power BI templates reflect the data in the Power BI data model—but critically, they do not contain the data—something that the standard Power BI Report layer and a Power BI paginated report have in common.

A Power BI Dataset in the Power BI service (the second item listed in the above screenshot with an orange icon) contains the data model, Power Query logic, and—most importantly—the imported data itself. Note that only the dataset has an icon to refresh or to schedule a refresh. This is because any data refreshes or updates ultimately occur within the Power BI dataset, not the Power BI report.

Note that I’m heavily qualifying and color-coding key terms to make distinguishing them a little easier –this will make more sense in a moment.

Relevant Components of Power BI Paginated Reports

The Power BI Report Builder/Paginated Report Builder software creates Power BI paginated reports. Unlike the .pbix files, which include a dataset and a reporting layer, the .rdl files are exclusively a reporting layer.

This is a little easier to remember when you know that .rdl stands for Report Definition Language.

Just as the reporting layer of a Power BI report doesn’t hold the data it uses, the .rdl file reflects the information available from the underlying data source. I like to think of both reporting layers as “recipes” for reports; the reports themselves are put together using the data available according to the recipe stored in either the .rdl or .pbit file.

Paginated report Data Sources: When you initially open the Power BI Report Builder software, you’ll see a Report Data pane on the left side of the window. This is where you can add your Paginated report’s Data Source(s), which will define what fields are available for building your paginated report Datasets.

Paginated Report Data Sources are like Power BI’s “Get Data”—in both instances, you’re telling the software what data to pull and from where. The key difference is that paginated reports have dramatically fewer data source options than standard Power BI reports do. Thankfully, one of the options for connecting to a data source in Power BI Paginated Reports is a Power BI Dataset Connection. By using this option, the paginated report’s Data Source becomes the bridge between the Power BI dataset and the paginated report’s dataset.

 

Using a Power BI Dataset that you’ve already created for a standard Power BI report saves time, but there may also be instances where you choose to create a Power BI dataset for one or more paginated reports without building a Power BI report in the first place. Any instance where you want to use Power Query to transform data or access a wider range of data connections than the handful that Power BI Report Builder offers is a good use case for leveraging a Power BI Dataset as a paginated report’s Data Source. Similarly, any instance where you may want to use an underlying data model and measures for your paginated report(s) calls for building out a Power BI Dataset to support that need.

If you’re already signed in with your Power account, this will show you the same datasets you have access to in the Power BI service. Start by right-clicking on Data Sources, and choose “Add Power BI Dataset Connection” *

 

 

 

After clicking on “Add Power BI Dataset Connection”, a window will open with each of the workspaces you have access to and a list of each dataset you have available for developing your paginated report.

Simply select the one you’d like to use as a source for your report’s data and press OK.

Congratulations! Your Power BI Dataset Connection is now a Paginated Report Data Source! This means that you can now use the data available in your Power BI Dataset to create the table of fields and measures that will comprise your paginated report Dataset. Not sure how to do this? Fear not—we cover this in our next blog post

*Note, you can also add a Power BI Dataset connection by choosing the section option on the Data Source list, “Add Data Source”, then choose “Power BI Dataset” from the list of available connection types, but this requires several more clicks, making it the more annoying of the two options.