Paginated Reports 101 – Building your first Paginated Report from 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.

Paginated report guides will often start by setting up the report and body prior to building out the “meat” of the report. If a report’s precise dimensions and orientation are already determined, this is a sensible way to go. Most of the time, however, I find that I have some wiggle room. The structure of the main report body may work better on legal-sized paper rather than letter-sized paper, or it may fit more cleanly into a landscape orientation rather than a portrait orientation. When no pre-existing report sizing requirements are required, I generally leave the report and body sizing to the end so the main section of the report can inform which page size and orientation will work best. Since I generally need to adjust the sizing at the end regardless (since I’ve moved things around and jostled the report space), this also tends to save me time.

In general, when I’m cleaning up a report so it’s ready for use, I’m looking for the following:

• Easily-to-read read formatting, with no random blank pages showing up in-between pages of content when you go to print or PDF.
• Logical repetition of columns and row headers (when appropriate), so I can easily read a given page without having to guess which category of data a column is associated with.
• Repeating header of my choosing and page numbers, so a page that has gotten separated from the pack can still be easily identified.

Of course, since I also want the report to be easy to review online, I will want the column and row headers to scroll with me when I move right or down. If you’re familiar with the “freeze pane” option in Excel, this is a similar user experience.

To accomplish these seemingly straightforward feats, we will do the following:

Setting up the report and body sections for printing

1. Define your report properties. If you’re brand new to paginated reports, the hardest part is probably finding the report properties so you can update them. You can do this by clicking in the gray areas around the report’s Body (the big white box where you build the information that goes into the report itself. If your Properties pane is turned on, you will see the Report property options to the right of the report. If your Properties pane is turned off, simply go to View on the ribbon and click the box next to Properties (see screenshot below).

If you prefer, you can also right-click in the gray area and select Report Properties from the resulting drop-down list. This will give you a user interface reminiscent of other Microsoft products’ print setup options, which you may find simpler to navigate. Both options will allow you to update the page size, margin sizes, and orientation. Power BI Report Builder does not care which Report Properties interface you opt to use for selecting your page and margins size. Whichever interface you update, the other will automatically update to match your selections. However, only the Properties pane will allow you to update the ConsumeContainerWhitespace property, which is why it’s got a big alert sign on the screenshot below.

What is this ConsumeContainerWhitespace Property? Microsoft explains it as “Gets or sets a value that indicates whether all whitespace in containers, such as Body and Rectangle, should be consumed when contents grow instead of preserving the minimum white space between the contents and the container” (Link: Report.ConsumeContainerWhitespace Property (Microsoft.ReportingServices.RdlObjectModel) | Microsoft Learn)

Since I find the above definition more confusing than just using context clues, I generally think of the ConsumeContainerWhitespace Property  as “always set to true unless I have some special reason for wanting to preserve space to the right of the report body and below the report body.”

2. Determine your report body

Report Body Width: Your report’s Body width should be exactly your Report width less your left and right margins. In the screenshot above, the Report width is 17 with 0.5-inch margins. Since 17 – 0.5 – 0.5 = 16, your body width should be exactly 16 inches.

To Update your Report Body width, click on the big white box that is your report body and the relevant properties will populate on the Properties pane. Note that in this instance, right-clickingto get the Body properties pop-up box will do you no good; only the Body properties pane allows you to update your report’s Body width and height. In the screenshot below, I have updated the Body width 16 inches, to match the Report’s width of 17 inches less those two half-inch margins on the left and right.

Updating Body height is a less exacting process, particularly if you’ve set your ConsumeContainerWhitespace property to “True”. To get rid of any unnecessary whitespace at the bottom of my report body, I manually pull the footnote section up to meet the bottom of my Tablix (or list reporting area).

At this point, your Report and Body properties should be in alignment—and in most cases you should not be running into any errant blank pages when you go to print. If you do, check your math, and double-check that you didn’t select any page break options under Tablix Properties.

Getting your Tablix’s printed headers to repeat:
Under Tablix Properties, select “Repeat header rows on each page” and “Repeat header columns on each page”. Voila!

(Note: occasionally, some report structures can gum up the repeating headers, making this more complicated than you would expect. If you’re running into an issue and have a use case that you’re willing to share, please leave a comment and we may be able to use it in a future blog post!)

Getting your headers to clearly scroll when using the online interface – this is a two-step process:
1) Under Tablix Properties, select “Keep header visible while scrolling” for both Row Headers and Column Headers

2) In the body of your Tablix, update the backgrounds of your header textboxes from “No Color” to White. This will not change the way the reports are printed out, but it will keep the online version of the report legible. If you skip this step, the header rows and columns will still scroll, but they’ll look something like the screenshot below. (Yikes!)

Leveraging the Page Header for repeating titles

When you start a new report using Power BI Report Builder’s blank report option, the default starting structure includes a title Text Box that will show up on your report’s first page and a Page Footer with the Built-in Field [&ExecutionTime], which will populate on every page. Depending on your company’s needs and reporting style, you may want that report title to be repeated on every page. Alternatively, you may want a version of the title to show up after the first page, say with a smaller font or with the page number specified.

You can accomplish this by adding a header to your report, which will repeat on each page based on the properties you define. Select Insert -> Header -> Add Header from the ribbon to create that header space.

If you want the same title to repeat on each page, you can then drag the report title from the main Body of your report to the Header that you’ve just added. Alternatively, if you’d like to create a different version of the report title for page two on, you can leave the report title as-is in the body of the report, create a new text box in the Header with your preferred format for the title of those subsequent pages, and set the “PrintOnFirstPage” property to “False” so that you don’t have duplicate titles on that first page.

There’s so much more you can do with titles to make them dynamic—but I’ll save that for a future blog post. 😊

Adding a page number to your footer

I almost always want to include a page number in my reports; thankfully Microsoft makes it easy by providing Page Number as one of several Built-in Fields that are available for any paginated report. You can find page number (and all other Built-in Fields) under “Report Data” pane by expanding the Built-In Fields folder.

To add a page number, simply select the Page Number Built-in Field and drag it into the Footer section. The Page Number field should present itself where you placed it in the footer using the placeholder [&PageNumber]. If you have more complex requirements for your footer, you can also use expressions to add or combine other Built-in fields. For example, you might use the expression  =Globals!PageNumber & ” of ” & Globals!TotalPages & ” pages”, which would output “1 of 10 pages” on the first page of a ten-page report, “2 of 10 pages” on the second page of a ten-page report, and so on.

 

 

I hope this series has helped make your next paginated report a little easier to write! If you find that you’re still having issues with paginated reports—or just have more to develop than time to build them—reach out to us! We’ll be more than glad to help.