Paginated Reports have been available in Power BI since 2019. They serve an important purpose, but they are not easy for the average business user to learn, plus they require Power BI Premium to use. In my blog and video today, I will show you how you can use Excel as a substitute for Paginated Report Builder to build simple paginated reports from your Power BI Desktop data model.
What is a Paginated Report?
As the name suggests a paginated report is a report that spans across several pages. In Power BI Desktop, if the report spans more than a single page, you get a scroll bar that allows you to see all the data. This is fine, as long as you are interacting with the report live at the time. But you cannot print a Power BI report that has a scroll bar and still see all the data. Enter paginated reports to solve that problem.
In this blog and video I will follow the techniques that I described in my previous blog articles.
Power BI Paginated Reports using Excel
Build a table visual in PBI. The table layout should be the same as you need in your paginated report. You can add column, measures, and implicit measures to build out your table.
Open the Performance Analyzer pane by selecting the option in the top menu. Click on Start Recording in the Performance Analyzer pane.
Refresh the table visual.
Copy the query generated by the table in the Performance Analyzer pane.
Launch Excel with a connection to the Power BI Desktop data model. You can use the third-party tool Analyze with Excel from SQLBI installed as an External tool in Power BI Desktop. It will also work with Analyze in Excel from PowerBI.com
- Build a simple pivot table in Excel from the connected data model.
- Extract a table linked to the data model by double clicking on any value in the pivot table.
Edit the DAX to render the table you need.
- Right-click anywhere in the extracted table.
- Click on Edit DAX.
Replace the query code with the query code that you copied from the Performance Analyzer pane in Power BI Desktop. You need to edit the copied query so that all the rows and not just top 501 rows get generated.
- You may need to edit the column names in the table so that they represent the content that is displayed.
Set the page printing options required for the paginated report.
Print preview the paginated report.
Export the paginated report to PDF.
Check that you got the paginated report as you wanted.
In this video I illustrated all the required steps to generate a paginated report from Power BI Desktop. I have also shown what would be happening under the hood to generate the results.
Renaming Column Names
In the video, I show that you need to rename the column names if you don’t like what you get. Unfortunately it is not currently possible to do this with DAX. The downside of renaming the column names like I showed in this video is that each time you refresh the data, the column names will revert to the default. An alternate is to add a new row above the table and use that as the header row, then hide the row of column names from the report.