Create a paginated report based on a Power BI shared dataset
APPLIES TO: 
Power BI Report Builder 
Power BI Desktop
You can use a dataset that you create in Power BI Desktop as a data source for Power BI Report Builder paginated reports. Picture this scenario: You've created a Power BI report in Power BI Desktop. You spent a lot of time designing the data model, then created a beautiful Power BI report with all sorts of great visuals. Your report has a matrix with many rows, so you have to scroll to see them all. Your report readers want a report they can print out, that will show all the rows in that matrix.
A Power BI paginated report can do that: print a table or matrix that runs to multiple pages, with page headers and footers and a perfect page layout that you design. It will complement the Power BI Desktop report. You want them to be based on the exact same data, with no discrepancies, so you use the same dataset.
What you need
Here's a list of what you need and don't need to use a shared dataset in Power BI Report Builder.
- Download and install Power BI Report Builder.
- The dataset can be in any workspace, and you don't need to be a member of that workspace.
- To access a Power BI semantic model, you need to have Build permission for the dataset. Read about Build permission.
- You need a Power BI Pro license or Premium Per User (PPU) license to publish your paginated report.
- You don't need a Power BI Pro or PPU license to create a paginated report in Power BI Report Builder.
- You can publish to My Workspace. For any other workspace, you need at least a Contributor role.
If you want to follow along with this article, download the Retail Analysis sample .pbix file, open it in Power BI Desktop, and add a table with many columns. In the Format pane, slide the toggle under Totals to the off position.
Then publish the report to a workspace in the Power BI service.
Connect to the Power BI semantic model
Open Power BI Report Builder.
Select Sign in in the upper-right corner of Report Builder to sign in to your Power BI account.
In the Report Data pane, select New > Power BI Semantic Model Connection.
Note
You can't create the data source or dataset for a Power BI Semantic Model by using the Report Builder Table, Matrix, or Chart wizards. After you've created them, you can use the wizards to create tables, matrixes, or charts based on them.
Search or browse for the dataset, or for the workspace where the dataset resides, then choose Select. Report Builder fills in the dataset name.
The dataset is listed under Data Sources in the Report Data pane.
Remember, you can connect to multiple Power BI semantic models and other data sources in the same paginated report.
Get the DAX query for the dataset
When you want the data in your Power BI report and in your paginated report to be the same, it's not enough to connect to the dataset. You also need the Data Analysis Expressions (DAX) query that's built on that dataset.
Steps to get the DAX query
Follow these steps to get the query.
Open the Power BI report (.pbix) in Power BI Desktop.
Make sure you have a table in your report that contains all the data you want in your paginated report. The table needs to meet these two requirements:
It needs to be a flat table, not a matrix or other visual. If it's not a table, convert it to a table now, go through the Performance Analyzer steps that follow, then convert it back to the visual you want.
For your numeric fields, you need to use predefined measures. They have a calculator symbol next to them. Read about creating measures.
On the View ribbon, select Performance Analyzer.
In the Performance Analyzer pane, select Start recording, then select Refresh visuals.
Expand the plus sign (+) next to the table name, and select Copy query. The query is the DAX formula you need for the dataset in Power BI Report Builder.
Create the dataset with the query
Go back to Power BI Report Builder.
Right-click the dataset under Data Sources and select Add Dataset.
In Dataset Properties, give it a name, and select Query Designer.
Make sure DAX is selected, and deselect the Design Mode icon.
In the upper box, paste the query you copied from Power BI Desktop.
Note
If your query includes the TOPN function, delete it from your query.
Select Execute Query (the red exclamation mark, !) to be sure your query works.
You see the results of the query in the lower box.
Select OK.
You see your query in the Query window of the Dataset Properties dialog box.
Select OK.
Now you see your new dataset with a list of its fields in the Report Data pane.
Create a table in the report
One quick way to create a table is to use the Table Wizard.
On the Insert ribbon, select Table > Table Wizard.
Choose the dataset you created with the DAX query > Next.
To make a flat table, select the fields you want in Available fields. You can select multiple fields at a time by selecting the first one you want, holding the Shift key, and selecting the last one.
Drag the fields to the Values box > Next.
Choose the layout options you want > Next.
Select Finish. You see your table in Design View.
Select Click to add title and add a title.
Select Run to preview your report.
Select Print Layout to see how your report will look printed.
This report layout needs some work. It has 54 pages because the columns and margins make the table two pages wide.
Format the report
You have several formatting options to make your table fit on one page.
You can narrow the page margins in the Properties pane. If you don't see the Properties pane, on the View ribbon, select the Properties check box.
Select the report, not the table or title.
In the Report Properties pane, under Page, expand Margins and change each one to 0.75in.
You can also make columns narrower. Select the column border and drag the right side to the left.
Another option is to make sure the number values are formatted well. Select a cell with a number value.
Tip
You can format more than cell at a time by holding down the Shift key while you select the other cells.
On the Home ribbon, in the Number section, change the Default format to a numeric format such as Currency.
Change the Placeholder style to Sample Values so you can see the formatting in the cell.
If appropriate, decrease the decimals to save more space in the Number section.
Getting rid of blank pages
Even if you've made the margins and the table columns narrower, you may still end up with every other page being blank. Why? Because of the math.
When you add up the page margins you set, plus the width of the body of the report, it has to be less than the width of the report format.
For example, say your report has an 8.5" X 11" format and you've set the side margins to 0.75 each. The two margins together make 1.5", so the body has to be less than 7" wide.
Select the right edge of the report design surface, and drag it so it's less than the desired number on the ruler.
Tip
You can set it more accurately in the Body properties. Under Size, set the Width property.
Select Run to preview your report and make sure you've gotten rid of the blank pages. This report now has only 26 pages, instead of the original 54. Success!
Considerations and limitations
- For datasets that use Live Connection to Analysis Services, you can connect directly by using the underlying Analysis Services connection instead of a shared dataset.
- If you want to use a Power BI semantic model that uses DirectQuery in Power BI Report Builder, the dataset has to include a measure, even if you don't plan to surface the measure in your report. Without the measure, the dataset won't return proper data results.
- Datasets with Promoted or Certified endorsements appear in the list of available datasets, but they aren't marked as such.