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.“
I will start from this issue:
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.
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:
Reasons why it’s better to minimize datasets:
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.
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:
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.