Paginated Report is a very functional feature of Power BI reporting that does not get talked about enough probably because Power BI desktop is screen-centric and does not require printouts to be utilized. In this article, we would learn about what paginated reports are, why we need them, how to create them, and what benefits they bring to your business.

What are Paginated Reports?

A paginated report is simply a tabular report formatted to fit well on a page(s). Paginated Reports make your reports more print-friendly and pixel-perfect. They are called pixel-perfect because it gives users control over the page layout of their report i.e., what is printed on paper looks exactly like what is seen on the screen. Paginated reports are ideal for creating sales invoices, receipts, purchase orders, and generally, tabular data. Power BI paginated reports are descendants of SQL Server Reporting Service (SSRS), a reporting software hosted on SQL Server, which was first introduced in 2004. The same file extension (.rdl) as SSRS reports are used by paginated reports. This makes it easy to create paginated reports from SSRS reports.

Why do you need them?

In comparison to printing regular Power BI reports that contain tables, only the rows that can be seen on the page will be printed but with a paginated report, you have the entire report printed out into several pages. In the table below, you’ll see that not all rows are visible, and users may have to use the scrollbar to see other rows. When we try to print this, only the visible parts on the screen get printed.

 

Differences between a Paginated Report and Power BI Report

 

Creating a Paginated Report

Prerequisites needed to create paginated reports.

1. Power BI Report Builder Application (RBA) 

The purpose of the RBA is for developing paginated Reports. This can be downloaded and then installed on your PC from the Microsoft Download Centre for free, so you don’t need a license for this. It is used for building fully formatted, paginated reports that can be published to Power BI Service. With a free license, users can publish paginated reports to My Workspace in Power BI Service.

2. Licensing

Paginated Reports can only be created if you make use of any of these licenses:

• Power BI Premium.
• Power BI Embedded.
• Power BI Premium per User.

Therefore, you will not be able to work with Paginated Reports if you only have a Free license nor publish if you have a Pro License.

The Power BI Report Builder Application (RBA).

 

Important Elements of the Power BI Report Builder Application – Data Source vs Data Set

A data source is a connection information to a particular source like SQL Server, Oracle server, or any other database. It can be a whole database or even a data warehouse. A dataset on the other hand refers to saved information of the query against the data and not the data. The data always resides in its original location. A dataset would be specific to your particular report. i.e. only related to the paginated report or visualization you create at a time. One dataset can also be used for multiple reports if the requirements are similar. Most organizations usually have one centralized Data source like a data warehouse for all their datasets.

Create a Paginated Report using RBA.

Creating Your Data Source

1. Open the Report Builder Apps as seen above. Right-click on the data sources icon from the built-in feature pane.
2. You can connect to a Power BI dataset, for the purpose of this article, we would connect to another source. Select “Add Data Source.”

 

3. A data source properties window will appear, Rename the Data source1 to a preferred name and note that spaces are not allowed. Select the connection type in this case, Microsoft SQL Server. Click on Build
4. A connection Properties window will appear, type in the server name or copy and paste from SQL Server and select the database then ensure you test the connection, and then click ok

 

5. The content of the connection string will be filled with the name of the database and the dataset. The connection string connects the RBA to your data source/database.
6. The Data Source will be created.

 

Creating your Datasets

1. Right-click on Datasets and select add dataset.
2. The Dataset Properties Window will appear and type in a name for your dataset.
3. Pick the data source (choose the data source earlier created)
4. Type in the query in the Query box provided.

 

5. Select Ok, your dataset will be added.

Now Let us Create the Report!

1. Navigate to the Insert Tab and click on insert Table.

 

2. A cursor with a table icon will appear on the canvas, click, and drag the cursor to create the table.

 

3. Click on the data row, then right-click on the list icon at the top right corner of the cell to see the list of column content to choose from

 

4. Select the option you want to use and repeat the step for the other two cells.
5. Give your report a title.

 

6. To view the content of your rendered report, navigate to the home tab and click on Run. A new tab will be created, and you can see a preview of your paginated report.

7. To make formatting adjustments, Click on a design to go back and apply the necessary formatting adjustments as you desire.
8. You can explore the print layout and navigate through the pages of your report. To go back, click on design.

Adding charts in Paginated Reports

The Power BI Report Builder contains some chart visuals that could be used for data visualization. These charts can be found in the insert tab.

  

Building a chart in the RBA is quite easy, Follow the steps below.

1. Click on chart, then insert chart.
2. A cursor with a chart icon will appear click on the canvas. A Select Chart Type window will pop up for you to choose from a variety of charts.

 

3. Choose the appropriate chart type and fill in the values.

 

4. To preview, click on run and navigate through the pages to view.
5. To make formatting changes to the chart, right-click on the chart and select from the options.

Row Group and Column Group

 

The Row and Column Group Feature can be found towards the bottom of the page. It can be turned ON/OFF from the view tab as seen below.

 

This feature enables users to create reports like a Matrix or Pivot table in Excel. Users can group their data column-wise or row-wise.

Expressions

In paginated reports, expressions are used to create calculations like sum, average, and if statements just like measures in Power BI Desktop. Let us explore expressions with an example below.

1. Right-click on the cell you want to apply the expression to
2. Turn on the Properties menu in the view tab.
3. Navigate to colour in the font section and click on the dropdown then click on expression to open a window.

 

4. Navigate to common functions in the category segment, then click on program flow and double-click on ‘IIF’ and type in the if statement you’d like. Then click OK.

 

5. Click on Run to view the report.

Parameters

Parameters give users the ability to make their reports a bit more dynamic like a standard Power BI Report. Follow these steps to get started with parameters.

1. Navigate to the report data tab and right-click and parameter to add a parameter.
2. A new window will appear, rename the name and prompt, and choose the appropriate data tab.
3. Next, navigate to available values, for the purpose of this demo, pick specify values and add values as seen below, then click ok.

4.  Next step is to navigate to dataset properties in the report data pane and navigate to filters and add filter, in the expression section choose the column you need, choose the operation and in value, click on fx and the expressions window opens up, click on parameters under category and double click on the parameter you refer to and then click ok.

 

5. Run to view, and you’ll see a filter pane with the parameter you created. 

Headers and Footers

Headers and footers can be applied to all pages in the report just like in MS Word. In RBA the footer is applied by default however, you can remove it if not needed. In addition, you can add report items like a text box, line, or rectangle to design the footer. The header and footer options can be found in the insert tab.

 

When working with headers, by default header appears on all pages of the report but you can change this in the properties pane and select if you just want it on the first page all through the last or not. You can use a text box on the report page but this will only be visible on the first page when printed. Headers also support text boxes, and images like a logo, line, and a rectangle.

Publishing your Report in RBA

You can publish your report to Power BI Service for the purpose of sharing. However, you need a Premium or Premium Per User license workspace to do this. Publishing a report is quite easy as it only requires a few clicks on your mouse. To do this save your report and navigate to the Publish icon on the home tab. 

Click on Publish and choose the workspace you would like to publish your report to and then publish. 

Your published report will be in the appropriate workspace in Power BI service. The icon for the paginated report is quite different from a Power BI report. This report was published to a Premium workspace hence the diamond icon beside the Workspace name.

 

You can now open your report to view, print, or share.  

Wraping Up

In the article, you learned what paginated reports are, how to create them, how they are different from Power BI reports, and when to use them. Paginated reports are print-friendly reports. They are designed using Power BI Report Builder Application. They are not usually as interactive as the normal Power BI reports, However, they are perfect for printing. It’s advisable to understand what would serve better, either a print-ready or an accessible/interactive report.