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.

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

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.

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.

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.

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

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

In order to apply the parameter, right-click on the Tittle and select 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.

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"

Next, run the report to check.

Finally, test the font sizes in the report.

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.

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

Add a Dataset

First, in the Report Data pane, right-click on Datasets and select 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.

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.

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

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.

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

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

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

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.

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

In addition, in the Report Parameter Properties, select 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.

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.