This post is meant to give you some important tips to take into consideration when trying to create Paginated Reports easily and with no failure. Power BI Paginated Reports are similar to SSRS or SAP BEx (from SAP BW), only with some different features.

“Power BI paginated reports are optimized for printing, or PDF generation. They also provide you with the ability to produce highly formatted, pixel-perfect layouts. So, paginated reports are ideal for operational reports, like sales invoices.[..]

We recommend you consider using a Power BI paginated report when: You know the report must be printed, or output as a PDF document.“

(https://docs.microsoft.com/en-us/power-bi/guidance/report-paginated-or-power-bi)

I will start from this issue:

The Power BI Dataset Relationships are not supported when accessed from Paginated Report Builder

(https://community.powerbi.com/t5/Desktop/Paginated-Report-Build-using-PowerBI-Dataset/m-p/845311)

So, the relationships created in a Power BI dataset are not supported in paginated reports when accessed from Report Builder. And now the question is: “How can we manage to create a simple Tablix using a dataset from 2 or more tables? ”

The solution in our case was to work in the Power BI dataset and add measures or calculated fields using Related or Lookupvalue and, finally, bring in one table all the columns we need for the Paginated Report. 

Write your SQL or DAX queries first, instead of using Visual Designer

Although it is much easier to drag and drop fields into your Report Builder dataset, I would not recommend it. It makes it harder to repeatedly test, share the logic in the query, and see the results. Write the script, whether it be in DAX or SQL. Writing the queries is a faster process that gives you a lot more control over your queries. There is a simple way to create a DAX  query for your report instead of writing the code from the scratch:

• Use a Power BI report based on the dataset you’ll use to create a table visual. Make sure this visual is a table (not a matrix) and contains all the data you need for the paginated report. Remove the totals.

• On the View ribbon, select Performance Analyzer.
• Select Start recording and then select Refresh visuals.
• Expand the table name (+) and select Copy query.

• Now we will create our dataset using this query. In Power BI Report Builder Right-click the dataset under Data Sources and select Add Dataset.
• In Dataset Properties, add a name, and select Query Designer. Make sure DAX is selected and deselect the Design Mode icon.
• In the upper box, add/paste the query created and copied from Power BI Desktop.
• Check the query and remove the TOPN function, if it exists, or replace the default 501 value with a higher one (more than the total count of rows you are expecting).

• Test the query – select Execute Query to make sure it’s working. The result should appear in the lower box.
• Select OK – the changes will be saved, and the new dataset will be available in the Report Data pane.

Minimize Datasets where you can

Reasons why it’s better to minimize datasets:

• Simplicity
• Performance

Having one big dataset with a lot of measures and unused columns is not performant, so it’s better to only keep the columns and measures you need for the report.

Filter vs parameter

Report parameters are commonly used as a way to filter the reported data. As a paginated report designer, you have two ways to achieve report filtering. To use Filters or parameters  you need:

• A dataset filter, in which case the report restricts the data already retrieved by the dataset.
• A dataset parameter, in which case the report parameter value(s) are injected into the native query sent to the data source.

Unused datasets

When a report is run, all datasets are evaluated – even if they’re not bound to report objects. For this reason, remove any test or development datasets before you publish a report.

You can use Power Automate to create flows that automate exporting and distributing Power BI paginated reports to different formats and locations.

---