Filter Equation Examples (Report Builder and SSRS)


Applies To: SQL Server 2016

To create a filter, you must specify one or more filter equations. A filter equation includes an expression, a data type, an operator, and a value. This topic provides examples of commonly used filters.

System_CAPS_ICON_note.jpg Note

You can create and modify paginated report definition (.rdl) files in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items.

The following table shows examples of filter equations that use different data types and different operators. The scope for the comparison is determined by report item for which a filter is defined. For example, for a filter defined on a dataset, TOP % 10 is the top 10 percent of values in the dataset; for a filter defined on a group, TOP % 10 is the top 10 percent of values in the group.

Simple ExpressionData TypeOperatorValueDescription
[SUM(Quantity)]Integer>7Includes data values that are greater than 7.
[SUM(Quantity)]IntegerTOP N10Includes the top 10 data values.
[SUM(Quantity)]IntegerTOP %20Includes the top 20% of data values.
[Sales]Text>=CDec(100)Includes all values of type System.Decimal (SQL "money" data types) greater than $100.
[OrderDate]DateTime>2008-01-01Includes all dates from January 1, 2008 to the present date.

Includes dates from January 1, 2008 up to and including February 1, 2008.
[Territory]TextLIKE*eastAll territory names that end in "east".
[Territory]TextLIKE%o%th*All territory names that include North and South at the beginning of the name.
=LEFT(Fields!Subcat.Value,1)TextINB, C, TAll subcategory values that begin with the letters B, C, or T.

Report Parameters (Report Builder and Report Designer)
Add Dataset Filters, Data Region Filters, and Group Filters (Report Builder and SSRS)
Data Types in Expressions (Report Builder and SSRS)
Expression Uses in Reports (Report Builder and SSRS)
Expression Examples (Report Builder and SSRS)

Community Additions