SQLServerCentral Article

Power BI Report Builder Parameters Tutorial

,

Power BI Report Builder Parameters allow the creation of dynamic reports. In this article, we will show how to add parameters to the report.

First, we will show how to change dynamically the font size of the Title’s report.

Power BI Report Builder Parameters tutorial - Dynamic font size

Secondly, we will add a filter by selecting specific first names in the parameters.

Power BI Report Builder Parameters tutorial - dynamic filters

Requirements to learn how to use Power BI Builder Parameters

First, you need to create a report similar to this one. I assume that you already have some experience creating reports. If not, check my introduction to Power BI Report Builder article and create a report like this one.

Power BI Report Builder Parameters tutorial - Report created previously

Secondly, you need SQL Server installed and the Adventureworks database. In this example, I am using the person.person table for the report. If you do not want to install the Adventureworks database, you can use a similar table and modify a little bit the code.

Power BI Report Builder Parameter – Dynamic font example

First, we will create a parameter to select the font size of the title dynamically. In the Report Data pane, right-click on Parameters and select Add Parameter.

Power BI Report Builder Parameters tutorial - Add Parameter

Secondly, add a name for the parameter. The Prompt is the message displayed to select the value. In this example, the size is text. This is because we send text to define the font size.

Report Parameter Properties

Thirdly, go to Available Values and add font sizes. In this example we added 3 values. Labels are the values displayed.

Add values

Also, go to Default Values and add the default value of 20.

Default values

In order to apply the parameter, right-click on the Tittle and select Text Box Properties.

Text Box Properties

Also, press the fx button to create an expression for the font size. This option creates the expression to have a dynamic font size.

Power BI Report Builder Parameters tutorial - font size

In addition, add the following expression to have a dynamic font. Basically, we are concatenating the font size number with pt.

=Parameters!fontsize.Value+"pt"

Expression

Next, run the report to check.

Finally, test the font sizes in the report.

Power BI Report Builder Parameters tutorial - Testing the parameter

Create a stored procedure to fill the parameter values

In the previous example, we learn how to have dynamic properties using expressions. You can select colors, sizes, and other properties of your report. In this new example, you will learn how to select multiple names and the report will show all the rows that belong to the names selected.

select multiple values

First, we will try to filter the data with one single name and after that, we will show how to select and filter multiple values. For this example, we use this stored procedure that list the distinct first names of the table person.person order by first names.

CREATE PROCEDURE dbo.listNames
AS
SELECT DISTINCT [FirstName]
 FROM [Person].[Person]
 ORDER BY FirstName

Secondly, to test the values, we will execute the procedure.

exec dbo.listNames

Power BI Report Builder Parameters tutorial - store procedure data

Add a Dataset

First, in the Report Data pane, right-click on Datasets and select Add Dataset.

Add Dataset

Secondly, add a name for the dataset, select the data source where the stored procedure is created. Thirdly, in query type select Stored Procedure and select the stored procedure listNames previously created.

Invoke stored procedure

Create a parameter

First, we created a stored procedure that lists the person names. Secondly, we filled a dataset with the information of the stored procedure. In the Report Data pane, right-click on Parameters and select Add Parameter.

Create a new parameter

Now, we will create a new parameter named listnames. We will add some values to be displayed in the prompt textbox.

Power BI Report Builder Parameters tutorial - Report parameter properties

Also, go to Available Values and select Get values from a query and select the dataset previously created. In this example, the value field and label field are the same, but usually, you should use the row ids for the value fields and the column for label fields.

Filter the data

First, we created a parameter with the first names. Secondly, we will create a filter based on the values selected in the parameter. Right-click the Dataset1 and select DataSet Properties.

Dataset properties

Secondly, go to filters, and in the Expression select the first name equal to and press the fx button for values.

Filters

Thirdly, add the listnames parameter. With this option, the report will only show the row that is equal to the selected Parameter.

Expression to list parameter values

Finally, run the report and check that you can only see the rows that belong to the First name selected.

Power BI Report Builder Parameters tutorial - select a single value

Working with multiple values

First, we showed how to select a single first name and show all the rows that match the selected first name. In this section, we will show how to select multiple values. Right-click the DataSet1 dataset and select Dataset Properties.

Instead of using the equal operator, we will use the in operator. This allows the selection of multiple values. With this option, we can select multiple last names.

IN to select multiple values

Also, right-click on the parameter listnames and select Parameter Properties.

Change parameter properties

In addition, in the Report Parameter Properties, select Allow multiple values.

Allow multiple values

Finally, you can run the report and test selecting multiple values and check that only the rows with the First names selected are displayed.

Select multiple values

Conclusion

Working with parameters in Power BI report builder allows to have interactive filters for the rows and have dynamic properties. We can change the font, size, style of the values of the report.

Rate

5 (1)

Share

Share

Rate

5 (1)