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.
Meet the Team – Catherine Sachdev, Marketing Assistant
Next up we’re introducing you to Catherine Sachdev. Catherine joined us just over a year
Jan
Data Lineage with Azure Purview
I wrote an introductory Purview blog post previously, where I explored what the tool is
Jan
The Next Era of Retail: How Technology is driving change in a COVID-19 World
The retail sector is of great importance and accounts for almost 5% of GDP and
Jan
Meet the Team – Alex Kordbacheh, Junior Consultant
It’s time for another Meet the Team blog! This time we’re introducing you to Alex
Dec
Use cases for Recursive CTEs
Introductions Recursive CTEs are a way to reference a query over and over again, until
Dec
Azure Sentinel is named a ‘Leader’ in the Forrester Research Wave Report
Microsoft have recently announced that they have been named a Leader by Forrester Research in
Dec
Getting Started with Azure Purview
Azure Purview (a.k.a Data Catalog gen 2) has been released into preview and is currently
2 Comments
Dec
An Introduction to ApexSQL Complete – Integration with SSMS
We all know that the idea of add-ins is to make our lives easier. In
Dec