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.
How Artificial Intelligence and Data Add Value to Businesses
Knowledge is power. And the data that you collect in the course of your business
May
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
1 Comment
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar