How To Create A Parameter In Report Builder

In this tutorial, you’ll learn how to create a parameter in Report Builder. Parameters allow end users to interact with a paginated report.

Parameters are similar to filters but they are functional only when you’re in the run view of Report Builder. Adding in this feature offers great assistance to end users as it allows them to filter data corresponding to their needs.

This tutorial will cover a comprehensive discussion on everything related to parameters where you’ll learn how to add and remove parameters. The tutorial will also show you how to remove blank or null values from your report, and handle errors in Report Builder.

Table of Contents

Add A Parameter In Report Builder

Right-click on the data set you want as a parameter in Report Builder and then click Query. This opens the Query Designer.

At the top part of the Query Designer, you can see a label called Parameters with two boxes for each Dimension.

Click on the first box of the Regions dimension and then select Click to execute the query.

When you run it, you’ll see that you need to select a city or cities before viewing the report.

After you’ve selected the cities you want to view, click View report. You’ll then see that the report only shows data based on the cities you’ve selected.

Removing Blanks From A Parameter In Report Builder

In the resulting report, you’ll notice that even with the city selection, it’s still returning blank values. To remove blank values from your parameter, go back to the design view and open the Report Parameter Properties window.

You can do so by right-clicking on the parameter you want to edit and then clicking Parameter Properties.

In the General tab, uncheck the Allow blank value and Allow null value options. Then click OK.

The next step is crucial. If you don’t do this, you’ll get an error.

Go back to the Query Designer. If you want to remove blank or null values, you need to set the Operator to Equal and remove any filter expression.

Once done, click OK and run your report. Choose select all for the City parameter and click View report.

You’ll then see that all the blank and null values from the table have been removed.

Error Handling For Parameters

This is an explanation as to why editing the Query Designer when removing blanks is important. Let’s look at a scenario where you skip going back to Query Designer and instead run the report after only unchecking the Allow blank value and Allow null value options.

If you do this, you’ll be faced with an error message.

The error is saying that the AllowBlank property of the City parameter is false. However, the default value contains a value that violates the AllowBlank property condition. This means that there’s a contradiction in the City parameter’s properties.

Remember that in the Parameter Properties, you’ve already set it to not allow blank or null values. However, in the Query Designer, the current expression already sets the City to not equal blank values. Therefore, there’s a redundancy in the formatting.

Moreover, because you’re using City as a parameter, adding a filter expression is no longer needed. Error messages in Report Builder are built intuitively as they specify what’s happening.

They allow you to fix the mistake first before continuing with your work. So it’s always a best practice to routinely Run your report.

This ensures that errors get detected early on. Instead of revising everything when you’ve almost finished, you can approach errors one at a time.

Adding Two Or More Parameters

If you want to add another parameter in your report, open Query Designer. Then, drag the item from the measure group to the dimension tab.

Then, check the box to enable this item as a parameter and then select Click to execute query. In this case, apart from the City, Dates is now included as a new parameter.

After you click OK, you’ll notice in the Design view that Year has been added to the Parameters pane.

Before you run the report, you first need to check the new parameter’s properties. Edit any properties if needed.

When you run the report, you need to set the two properties.

Once you’ve selected, click View report. You’ll then see that the report only shows data based on the parameters you’ve selected.

You can also continue adjusting the parameters as you view the report.

Remove Parameters In Report Builder

To delete Parameters, open the Query Designer. Click on the parameter you want to remove and then click the X button.

Afterwards, select Click to execute the query and OK. Then, click on the parameter you deleted in the Report Data pane and press the Delete button on your keyboard. Click OK.

The parameter has now been deleted from the report. When you run the query, you’ll only be filtering by Year. If, for example, you select 2015, the report will then only show values with 2015 as the year.

Dealing With Blanks

To efficiently remove blanks and null values from your report, you can use a Boolean expression.

Right-click on your dataset and go to Dataset Properties. In the Filters pane, click the Add button to create a new filter.

For this example, you need to create a Boolean expression so that you can keep the rows with non-blank values.

In selecting the field you want to filter, you need to use an expression. So, click the fx button beside the Expression textbox. Type in the common function, IsNothing. Then, add in the field you want to filter.

This will return True if a row value is blank, and False if otherwise.

Then, instead of Text, choose Boolean. For the operator, use the equal sign ( = ). In the Value textbox, write false.

So behind the scenes, this filter first evaluates if a value is blank (true) or not (false). Then, it filters out values that return true.

If you run your report, you’ll see that it doesn’t anymore contain blank or null values.

You can use this both at a data set and Tablix level.

Conclusion

This tutorial offers a comprehensive discussion on parameters in Report Builder. This feature is especially beneficial for end users. It allows them to see specific details in a paginated report.

And as you’ve learned, adding and removing parameters is easy. The trick is to make sure the parameter properties are set correctly so that blank or null values will be excluded.