Using Paginated Reports in Power BI
On November 14th, Microsoft announced that paginated reports will no longer be limited to premium workspaces, but that any modern workspace can store paginated reports. That’s why in this post I’ll show you how to create a paginated report and add them to your “interactive” Power BI report.
What are Paginated Reports?
According to the Microsoft documentation “Paginated reports are well suited for viewing reports as pages, printing as PDF, exporting to Excel, or generating documents. A sales detail report where you want to see row-level detail data for a specific region/industry/customer and perhaps export it to Excel or print as accessible PDF would be best served by a paginated report.”
In short, a table and a matrix can show you detailed information, but you cannot print them correctly. This is where Paginated Reports show up. First I’m going to create a Power BI dataset based on Adventure Works, then use that as my source for two paginated reports. The paginated reports can work on their own, but also be used inside a Power BI report.
Let’s go!
So first I’ll import the data from Adventure Works, a standard database from Microsoft:
After that, I created two measures: # Orders and $ Sales. Inside paginated reports (as well as Analyze in Excel) you can only work with explicit measures.
$ Sales = sum(FactInternetSales[SalesAmount])
# Orders = DISTINCTCOUNT(FactInternetSales[SalesOrderNumber])
The next part is creating a simple report. Not too fancy, but that’s not the point of this report:
Now it’s time to publish this report to a workspace and use the dataset in our paginated report!
To create paginated reports we need a tool called Power BI Report builder, which can be found in the Microsoft Store or find it here. When opening Power BI Report Builder, you can (1) right-click the Data Source folder and then (2) click on Add Power BI Dataset Connection:
After that, you’ll see a screen with all your workspaces and you can select your dataset:
Next step: creating a dataset. You can do this by right-clicking the Datasets folder and selecting Add dataset.
Then you select the Query Designer and select the fields you want to use in your dataset. My result looks like this:
In the top part, I added the SalesOrderNumber and selected the Parameter checkmark. This way I can select the details of the Sales Order numbers I want to view. When all is done, it’s time to create the report itself! And I’m going to be honest: This took me longer than writing this whole blog, but definitely a goal for next year. My result looks like this:
On the top left, I have an expression that shows the Order Number, below that the address, on the right the order date, and then the products that got ordered. By pressing “Run” on the top left, you can view the actual report. First I have to select a Sales Order Number and select View Report, but then I’ll my result:
I know the report doesn't look as nice as it should, but it’s functional for now. So, it’s time to publish it to the Power BI Service! To do this: go to (1) File, (2) Publish, and (3) Power BI Service.
Then you’ll see your workspaces again and you can choose where you want to publish your report to. Unfortunately while writing this blog, I still need a premium workspace or I’ll get an error that the report isn’t a Formatted Table Report. Before the end of the year, this won’t be the case anymore, but for now, I’ll publish to a premium workspace.
In the Power BI workspace, you can easily distinguish the two types of reports by their own icon:
In the Power BI service, you can also watch the report, but you cannot edit the report anymore as you can do with “normal” Power BI reports. Now we’re almost done: It’s time to add the report to our interactive Power BI report!
As you could see in one of the first pictures, I have a large open space in my report, which will be used for my paginated report. To do this, select the Paginated report visual, and don’t forget to add the fields you want to use as a parameter in the field bucket.
If you click on the “Connect to report” button, you’ll see all the paginated reports available to you. Select the report you want to use and select the “Set Parameters” button. In this step you can match your columns to the parameters of the paginated report so that you can use your own slicers and apply cross-highlighting to the paginated report:
And here’s the final result:
Now you can view the details of all the open invoices, and for example, send a reminder to those people. You can also automate that, but that might be interesting for another blog!
Another example might be a list for the Salescountry managers, who want to see how many products they sold:
When you select the export option and select the PDF one, this will be the result:
I got 20 pages, but as you can see on the left, all the even numbers are just blank pages, so apparently, I did something wrong with my margins in this report.
Power BI paginated reports and Power BI interactive reports serve two different types of users and business needs and making the paginated reports available for Pro users might be a great step for your company as well. To get you even more up to speed with paginated reports, Guy in a cube has a playlist of seven videos about the subject!
Take Care.
