Add a filter to a Report Builder dataset
Add a filter to a dataset to limit the data in a report after the data is retrieved from an external data source. When you add a filter to a dataset, all report parts or data regions use only data that matches the filter conditions.
Note
Report parts are deprecated for all releases of SQL Server Reporting Services starting with SQL Server Reporting Services 2019 and all releases of Power BI Report Server starting with Power BI Report Server September 2022.
For a shared dataset, a filter that applies to all dependent items must be part of the shared dataset definition on the report server. A report or report part that contains an instance of a shared dataset can create another filter that applies only to the instance.
To add a filter, you must specify one or more conditions that are filter equations. A filter equation consists of an expression that identifies the data that you want to filter, an operator, and the value to compare to. The data types of the filtered data and the value must match. Filtering on aggregate values for a dataset isn't supported.
Note
You can create and modify paginated report definition (.rdl) files in Microsoft Report Builder, Power BI Report Builder, and in Report Designer in SQL Server Data Tools.
Add a filter to a shared dataset
Open a shared dataset in Report Builder.
On the Home tab, select Set Options. The Dataset Properties dialog box opens.
Select the Filters tab. This option displays the current list of filter equations. By default, the list is empty.
Select Add. A new blank filter equation appears.
In the Expression field, enter or select the expression for the field to filter. To edit the expression, select the expression (fx) button.
From the dropdown list, select the data type that matches the type of data in the expression you created in the previous step.
In the Operator dropdown list, select the operator that you want the filter to use to compare the values in the Expression and Value field. The operator you choose determines the number of values that are used from the next step.
In the Value field, enter the expression or value against which you want the filter to evaluate the value in the Expression field. To edit the value, select the expression (fx) button.
For examples of filter equations, see Filter equation examples in a paginated report (Report Builder).
Select OK.
Add a filter to an embedded dataset or a shared dataset instance
In your report, right-click a dataset in the Report Data pane and then select Dataset Properties. The Dataset Properties dialog box opens.
Select the Filters tab. This option displays the current list of filter equations. By default, the list is empty.
Select Add. A new blank filter equation appears.
In the Expression field, enter or select the expression for the field to filter. To edit the expression, select the expression (fx) button.
From the dropdown list, select the data type that matches the type of data in the expression you created in the previous step.
In the Operator dropdown list, select the operator that you want the filter to use to compare the values in the Expression and Value field. The operator you choose determines the number of values used in the next step.
In the Value field, type the expression or value against which you want the filter to evaluate the value in the Expression field. To edit the value, select the expression (fx) button.
For examples of filter equations, see Filter equation examples in a paginated report (Report Builder).
Select OK.