In this post, I’ll discuss the concept of paginated reports, how they typically differ from the dashboard reporting delivered by Power BI, and how Microsoft anticipates and answers the need for generating paginated reports with the Power BI Paginated Report Builder. Moreover, I’ll discuss prerequisites for using the Paginated Report Builder, as well as explore some general considerations for using it to design reports.
So, What are Paginated Reports, Anyway?
Power BI, consisting of cloud-based BI services (“Power BI Services”), together with a desktop-based “studio” interface (“Power BI Desktop”), was released to the general public on July 24, 2015, and has evolved at an accelerated rate ever since. Power BI offers capabilities that enable straightforward data preparation and data discovery, as well as the assembly of interactive reports and dashboards for collaboration. Because of its dashboard orientation, Power BI is designed primarily for the presentation of aggregations / summary data, typically via graphic visualizations.
Organizations still need to generate and serve transactional, as well as other, reports that turn out to be large / multi-paged. These reports don’t lend themselves to the dashboard environment – although I’ve often come across clients attempting to generate them with Power BI (particularly with the standard table and matrix visualizations). Furthermore, many simply continue to generate such reports in Excel, or traditional report writers, which can complicate, and add disparity to, information delivery.
These transactional reports, as well as other many-rowed reports containing summary information (say a printout of customer balances at year end), are often referred to as paginated reports – “paginated” because they’re generated in a format that straddles multiple pages. These reports often need to be printed or shared, and their page layouts typically need to be controlled with precision (hence the term “pixel perfect”), while displaying a large amount of data in a source we designate.
Enter Power BI Paginated Report Builder
Microsoft announced support for paginated reports in the second quarter of 2019, and has since released Power BI Paginated Report Builder, based upon the RDL (Report Definition Language) technology in SQL Server Reporting Services (“SSRS”).
For those of us who have had occasion to work with Report Builder in SSRS, the look and feel will be quite familiar. Power BI Paginated Report Builder is the complementary, standalone tool for authoring paginated reports in the Power BI environment. These reports are generated and saved in an .rdl file format, and are most optimally used for operational reporting. They are the kinds of reports organizations often deliver as PDFs to consumers on a regular schedule. Such detail reports can also serve as ideal drill-through targets from Power BI reports.
It’s important to keep in mind that, like Power BI Desktop, no Power BI license is required to use Paginated Report Builder. We can render and view locally reports we have authored, just as we can view them in the Power BI Service. Moreover, we can view our reports’ layouts, and print them directly, from Paginated Report Builder, if desired.
Along with supporting the more common need to generate multi-page, transactional / detail reports, other benefits accrue when we add paginated reports within a new or pre-existing Power BI implementation. As one example, we can connect to, and source, hosted (both Premium and non-Premium) Power BI shared data models for reports we author in Paginated Report Builder. (I walk through this process in another blog entry.)
While it is often more of a best practice, within the Power BI environment, to use Paginated Report builder against a direct source of transactional (“detail”) data than as a reporting mechanism for aggregated / summary data, shared Power BI datasets can be useful sources for straightforward, practical needs like exporting large tables of data from the model, or for creating “pixel perfect” reports based upon tables / matrices that would be impractical for display in the Power BI service itself. I have come across needs where Paginated Report Builder fits the bill in this way very efficiently.
Rebirth of SSRS Report Builder
As I’ve already noted, working with Power BI Paginated Report Builder will be instantly familiar to those of us who have designed or edited reports with SSRS Report Builder. It’s the same tool, after all, just tweaked to help fill out the Power BI offering with a specialized report writer for paginated reports.
Paginated reports don’t have built-in data models like Power BI reports (although we can certainly build a paginated report based upon a Power BI shared model as I have mentioned. But a paginated report can draw upon multiple sources, including, as of this writing, on-prem SQL Server and SQL Server Analysis Services, Azure SQL Database and Data Warehouse, Azure Analysis Services, and Oracle and Teradata.
Report Builder is our “paginated reports studio” on the local PC, from which we deploy to the Power BI Service (similarly to the way we use the Power BI Desktop to create and publish Power BI reports). Also, like working with Power BI, when we upload a report with an on-premises data source, we create a gateway through which we re-channel the data connection.
What We Need to Get Started with Paginated Reports
There are a few basic requirements to get started with Power BI Report Builder:
- Download and install Power BI Report Builder, typically from one of two places (where we can be assured of the most recent version):
- Click the Download button from within the Power BI Service, Home page, once signed in
- Go to the Microsoft Download Center location for Power BI Report Builder, and click Download
To deploy a paginated report to the Power BI service we’ll need:
- A Power BI Pro license
- A destination workspace on the Power BI service with Power BI Premium capacity, or an on-prem Report Server
Designing the Paginated Report
When we execute a paginated report we have created within Report Builder, the report processer generates the report by marrying the report definition we’ve built (the .rdl file, containing data source, directions for which data to pull, and the layout in which to present it) with the data it retrieves.
The typically transactional, column-based nature of paginated reports lends itself to tables. Additionally, we can obtain a “cross-tab” effect with a matrix data region (which also offers the option of drill-down, in addition to optional summarization of various levels). Free-form list, chart reports, and other visual summaries, too, are available, although the selection is more limited than what we find in Power BI. Basic table, matrix and chart wizards are included to help with creating embedded datasets and their connections.
The standard features from the SSRS Report Builder are available as well, including:
- Expression and formula design
- Image referencing / embedding
Practically every object within a paginated report can be controlled and maintained via properties settings.
It is important to remain aware that, while charts can add perspective to paginated reports, their primary focus is typically to support multi-page “detail” reports.
Working with Published Paginated Reports
Once we’re satisfied with the appearance and operation of the paginated report within Report Builder, we are ready to deploy the .rdl file to the Power BI service, and to the workspace we choose, just like we do with our .pbix files from Power BI. Having deployed, we can view the new paginated report in the Power BI Service or via the Power BI mobile apps.
In addition to viewing the deployed report, we can share it with others. We can set up PDF subscriptions (together with mailing periodicity) via the Power BI Service, just as we can with Power BI dashboards and reports. We can also export Paginated Reports in various formats, including (as of this writing):
- Excel / CSV
- HTML and MHTML
We highlight and educate developers and business users on many Microsoft technologies that we use in our services and projects. Power BI is one of the most sought after educational events at the moment, and while we cannot host in-person workshops as normal, our team has pivoted to deliver virtual events as if the internet has historically been our go-to venue. Feel free to head over to Tallan Events to take a look at what we have scheduled and sign-up or pass the page on if you see something of interest.