Paginated Reports with Power BI Desktop

Welcome to the inaugural post for HoosierBI.com! I get to use Power BI some as part of my day job as a chemist, but I have been also been volunteering with non-profits, schools, etc. to help them with their data on the side. Rob was nice enough to let me write a few articles on PowerPivotPro, and I enjoyed that enough that I decided to start up my own site. The site will focus on Power BI and will hopefully be yet another good resource to newcomers and/or people teaching others. There will also be articles on PowerApps, Flow, Azure Cognitive Services/Machine Learning, and/or Sharepoint too (but I’m still having plenty of fun with M and DAX for now).  Guest posts will be welcomed, especially from those teaching others and/or working in a volunteer capacity.

The Microsoft team recently announced the addition of paginated reports to Power BI, which is great. However, currently it is only available on premium workspaces and for SQL Server data sources. The extension to other data sources is expected soon, and hopefully it will be offered outside of premium too. In any case, this article shows how one can generate paginated reports from any Power BI Desktop model.

Assuming you are already using Power BI, the first thing you’ll need is to download and install SQL Server Report Builder (from here).  Open it up and start a “New Report”, choose which wizard you want to use (e.g., Table or Matrix Wizard), and choose “Create Dataset” and “Next”.  Create a “New” Data Source connection, select “Microsoft SQL Server Analysis Services” as the connection type (Power BI runs SSAS behind the scenes), and use the string “datasource=localhost:xxxxx” as your connection string, where “xxxxx” is the localhost number for your running Power BI Desktop model, as shown below.

Finding your localhost # is easy and there is a good article that shows you four different ways to do it here (add link). I typically use DAX Studio to find it (as there is lots of other stuff you can do with DAX Studio), but searching for the file called “msmdsrv.port.txt” (while you have a pbix file open) is also an easy way.  Remember that your Power BI desktop model needs to be open for this to work, and that your number will change each time you that pbix file.

In this example, I used a Power BI model based on the Northwinds database provided through and Odata feed, which is a relatively simple and small model good for demos/training, but this approach will work with any pbix model.

https://services.odata.org/V2/Northwind/Northwind.svc/

It is an OData2 feed and OData4 is the standard these days, but it is still helpful. I plan to write another article on how to get started with OData feeds and push the work to the server side to speed refresh times ($filter, $select, etc.).

After you set up the datasource, you’ll see the Query Designer. In this window, you select the fields (columns) and measures you’d like to include in your report, to create a Dataset in Report Builder. Once the dataset is created, the rest is report building. You can find some good tutorials on the Microsoft site here (https://docs.microsoft.com/en-us/sql/reporting-services/report-builder-tutorials?view=sql-server-2017), and there are lots of videos on YouTube. Below is a screenshot of just a basic chart and matrix from the Northwinds data.

You can tell I didn’t spend much time on the report (just starting out with Report Builder), but it is easy to use and looks to have many features/options. As you are building the report, you can toggle between “Run” and “Design” modes to see how it looks. When finished, you can print to PDF from “Run” mode. While this report can’t be published to PowerBI.com at this time and therefore isn’t scalable, it is a good way for an individual to make a paginated report that can be shared through email, etc.

Note that the localhost number changes each time you open a given pbix file, so you will need to look up the number again and update your connection string in Report Builder when you want refresh the report. To do that, just right click on your Data Source and choose “Data Source Properties”, and replace the 5-digit number in the connection string.

I hope this post helps at least a few of you.  Thanks for visiting HoosierBI.com.

About the Author: Pat Mahoney

Pat Mahoney is a chemist in Indiana who enjoys teaching others to use Power BI and other tools, and providing pro bono data science help/instruction to nonprofits, schools, etc.

2 Comments

  1. Nice article. I hadn’t tried hooking up to Power BI Desktop directly as data source. And exporting to PDF is a nice hack.

Leave a Reply

Your email address will not be published. Required fields are marked *