Create your first table export report from a Power BI Dataset in Paginated Report Builder

Welcome back!

In last week’s post, I promised to walk you through how to create a simple table report in Power BI Paginated Report Builder from a Power BI dataset.  Why would someone want to do this, you ask?  Well, how many tables in your Power BI Desktop reports look like this?

So while you can interact with it in the browser and scroll to see all the rows, when you export it out to PDF, it looks like this.

Having tables in Power BI reports auto-expand when exporting is a common ask amongst Power BI users.  Unfortunately, the current behavior won’t be changing anytime soon.  With paginated reports, however, your tables can auto-expand across several pages upon export, and they’re designed for just this type of use case.  Let’s walk through how to build a paginated report for this table against the same Power BI dataset.

Make sure you’ve downloaded and installed Power BI Report Builder as a first step.  Once that’s done, create a new blank report as your project.

From there, right-click on the “Data Sources” folder and select “Add Data Source”

You have two options at this point –

1. You choose SQL Server Analysis Services as your source and connect to your Power BI Dataset in the service.  (Currently, this requires your dataset be in a workspace backed by Power BI Premium, but this will work against datasets in non-premium workspaces in the near future.)  You’re doing so using the “XMLA endpoint” that was discussed in a recent announcement.  As Christian states in the post, use the following URL format to address a workspace as though it were an Analysis Services server name –

powerbi://api.powerbi.com/v1.0/myorg/[your workspace name]

myorg can be replaced with your tenant name (e.g. “mycompany.com”).

[your workspace name] is case sensitive and can include spaces.

If you’d prefer, you can easily copy the full URL you need from the dataset settings and paste that into your Connection string.  I’ll do that for my report –

My connection string looks like the following after pasting it in.  Power BI Report Builder will automatically place “Data Source=” in front of the connection string I’ve pasted in to make sure it works properly.

Click “OK” to save this data source in your report.

2.  Alternately, you can use a Power BI Desktop file locally as your SQL Server Analysis Services data source to create this report against by using the diagnostics port Microsoft documented in 2018.  This may help accelerate development in certain scenarios by allowing you to build this out when you’re traveling on a plane, etc, and also allow you to test performance of the paginated report in more advanced scenarios you want to tackle (I confirmed with Adam Wilson there was no issue with letting folks know about this).  Just make sure you change your connection string for your data source after publishing your Power BI Desktop file and prior to publishing the paginated report to your Power BI workspace using the information I just covered.

Now matter which option you’ve chosen, the tutorial I walkthrough proceeds the same way.

Next, right-click on the “Add Dataset” to add a new dataset to your report.

Datasets in paginated reports are a little different than those in Power BI reports you might be used to.  A dataset in a paginated report is just a single query that runs against the selected data source and returns data.  You can have several datasets in your report, but in this example, we just need one.

I’ll name my dataset, select the data source I want to run it against, and click the “Query Designer” to create my query I want to run and return data from.  As I mentioned on Twitter earlier this week, Paginated Report Builder has a visual designer that will craft the DAX query once I drag and drop the fields in I need.  I just need three for this particular query, and get the following result when I execute it.

Looks like I have everything, so I click OK and then save my dataset.

Now let’s build the table for our report.  Select the Insert tab, then click Table and choose to run the Table Wizard

I select the dataset I just created, and drag and drop the fields into the groups accordingly.  I want each row have a “State”, and have each “Year” be a column.  My values are a simple sum of the “DODs” field.  This allows me to have subtotals and grand totals for my groups if I choose to do so.  I have what I need, so I click Next.

I’ll leave the “Show subtotals and grand totals” checked and complete the table wizard.

I’m going to delete the column group on the right of my table that says “Total” at the top by right-clicking on it.  With that change, my table looks like the following.

You don’t see data changing live when you’re designing the report like you do with Power BI Desktop.  Instead, it’s a similar experience to designing a Mail Merge document in Microsoft Word – you’re creating a layout of how you’d like your report to look, then feed the data from the data source to generate the report/document.  To see the report you’ve designed with your actual data, click “Run” from the toolbar under the Home tab.

Here’s what my report looks like.

I’ll export it to PDF to confirm it will auto-expand across multiple pages, and sure enough it does.

If you’ve stayed with me this far through the post, thank you and you’ve finished creating a simple table report against a Power BI dataset.  There’s clearly much more I could do to make this look prettier, but it isn’t necessary in this particular scenario for my users (which is, well, me).  In a follow-up later this month, I’ll have a short final post around publishing this to the service and linking to it from my Power BI report there.

Have a great weekend!