For those who are new to Power BI Paginated Reports, this blog should help you hit the ground running and create reports with high productivity.
For those who don’t already know, Power BI Paginated Reports is similar to SSRS with some different features. And so, this blog could also be used to understand SSRS as well.
Minimise Datasets where you can
When you create datasets for a report, you want to minimise the amount of them. Instead of having a dataset per Tablix you want to repeatedly use the same datasets.
There are two main reasons to minimise datasets:
- Simplicity
- Performance
By minimising datasets, you will be reducing duplication. Fixing one dataset will always be easier than fixing multiple datasets. We should aim to achieve the reduction of redundant datasets. The first image shows the price of a product broke down into datasets based on the product type, but instead we should aim for the image on the right, where we have one dataset and where we aren’t applying a filter on the dataset level.
When you want to add a filter to the data, add it on a report level instead of the dataset level. Report level filters will help minimise what could be an endless mess of datasets.
However, you must be sensible with your datasets as well, having one giant dataset with billions of rows and endless measures is not performant or simple, it is preferable to break it down and reduce the granularity.
Don’t use the Visual Designer, write your queries out first
As tempting as it can be to drop and drag fields and parameters into your dataset, I would not recommend it. It makes it harder to repeatedly test, share the logic in the query, and see the results. Instead, 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. If you don’t understand SQL or DAX to a proficient enough level, don’t panic, you could start the query with the visual designer and then convert it to the script.
When you’re running your DAX queries, don’t use the dataset query designer in the report builder, use DAX studio. DAX studio gives you the ability to efficiently query, format, and test DAX. As an experience it’s much faster and easier to show other people. If you are using SQL queries you could use SQL Server Management studio.
As a practice, when you’re trying to test the data, it’s important to get the data right in your queries before you place that data in your report.
Groups are powerful
This tip is the most important for developing reports efficiently. By using groups, you can have a dynamic report that reduces the amount of repeatable development.
As an example, let’s say that you have a report that has the same table repeated, with the same data but each table has a different date associated with it.
Wouldn’t it be great if you could create and format this table once, and then have it repeat? Well, you can, if you place the table inside a Tablix it will repeat based on the date. To do this;
- Create a new table.
- Reduce the table down to one cell.
- Place a rectangle inside that cell.
- Change the row group properties to group on the repeating value
Create a dataset for your parameters
Instead of hard coding your parameters, or having the user type the parameter, it is preferable to have a list that the user can choose from. To do this, create a dataset, have just your field in the dataset, and then link the parameter to the query.
As practice, we should also cascade our parameters. If we have a parameter for a country and then one for a city, when we choose a value for the country parameter, we should only then be able to choose the corresponding city parameters inside that country. To do this, have a dataset for the country parameter, a dataset for the city parameter and then filter the city dataset by the country parameter.
DAX Queries
When using DAX for SSRS, you should use a general outline for your DAX queries. Use the following pattern below with variables, data columns, filters, and measures:
David Stelfox’s blog explains this in more detail and is a great reference for writing DAX for SSRS.
