Paginated reports using Power BI datasets & Report Builder

Recently, Power BI team released the Power BI report builder which is a newer version of SSRS reporting tool, the major difference is that you can connect to your Power BI datasets and publish to the Power BI Service.

For those familiar with Power BI desktop, the SSRS tool will feel old fashioned, but actually it’s a very powerful tool and it will allow you to craft paginated reports within your existing Power BI ecosystem.

In this article, I will show different ways to connect to Power BI datasets and how to use parameters.

1- Power BI dataset: have a dataset available on the Power BI service, here a few tips:

• SSRS doesn’t have a power query or M-code; I recommend performing some basic ETL operations on your dataset before hand
• The visuals in SSRS uses only one query; you can either you merge data from different tables into one or, later on, from the report builder you build a more complex query that does the join, both options are valid.

2 -Connecting to the Power BI dataset: if you are already logged in the report builder, when trying to connect to a Power BI dataset it will open your workspaces available

3-Add a dataset from the Power BI connection: in Power BI desktop this is called a ‘Query’, in report builder it is called a Dataset, ask Microsoft why :)

Here there are different ways to connect to your data. The first option is using DAX and MDX code, which can be automatically generated from Power BI desktop, here a short tutorial from Microsoft.

When using DAX code, you need to deactivate the ‘design mode’ and manually add the parameters in FILTER or KEEPFILTERS, looks like this:

// DAX Query
EVALUATE
 CALCULATETABLE(
 SUMMARIZE(
 ‘City’,
 ‘City’[Id],
 ‘City’[Search]
 ),
 KEEPFILTERS(FORMAT(‘City’[Active], “True/False”) = FORMAT(@ActiveCities, “True/False” ))
 )
ORDER BY
 ‘City’[DateActive] DESC

As you can see from the query, there is a variable ‘ActiveCities’, this variable should be declared as the Query Parameters with some Default value:

The second option, if you are not familiar with DAX code, is to use the Design mode. After adding the dataset, click on ‘Query designer’ and you will see the design mode screen, there you can just drag fields from left to right(A -> B) and the tool will build the query for you. Click on OK to finish creating the dataset before moving to the next step.

If using this second option to build your query, you can also add a parameter but first save the query, then open again the Query Designer and use the C section to create filters or parameters.

Let’s say for this example, you want to create a City dropdown parameter, it looks like this:

when you click Ok, the tool automatically performs 2 actions:

• Creates the parameter and tries to map it with available parameters:

• After you click Ok on Dataset Properties, it will also create a ‘Parameter’ with the same name. You could also create your Parameter beforehand and map it in the previous step, just if you leave this field blank, Report Builder will create it for you.

Keep these two last steps in mind if you want to customize parameters.

In this example, the City dropdown can be prefilled with data from a dataset, to setup this option go to the Parameters properties (if it was already created otherwise create a parameter), then use this dataset in the ‘Available values’

Having independent parameters can be very useful if you need to filter different datasets for different parts of your report from one single point.

For more tip how to use the Report Builder, I recommend the Microsoft ‘Power BI Paginated Reports in a Day course’

Happy coding!