Share via


Commmonly Used Filters (Report Builder and SSRS)

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.

Note

You can create and modify report definitions (.rdl) 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. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com.

Filter Examples

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 Expression

Data Type

Operator

Value

Description

[SUM(Quantity)]

Integer

>

7

Includes data values that are greater than 7.

[SUM(Quantity)]

Integer

TOP N

10

Includes the top 10 data values.

[SUM(Quantity)]

Integer

TOP %

20

Includes 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

>

2088-01-01

Includes all dates from January 1, 2008 to the present date.

[OrderDate]

DateTime

BETWEEN

2008-01-01

2008-02-01

Includes dates from January 1, 2008 up to and including February 1, 2008.

[Territory]

Text

LIKE

*east

All territory names that end in "east".

[Territory]

Text

LIKE

%o%th*

All territory names that include North and South at the beginning of the name.

=LEFT(Fields!Subcat.Value,1)

Text

IN

B, C, T

All subcategory values that begin with the letters B, C, or T.

Examples with Report Parameters

The following table provides examples of filter expression that includes a single-value or multivalue parameter reference.

Parameter type

(Filter) Expression

Operator

Value

Data Type

Single value

[EmployeeID]

=

[@EmployeeID]

Integer

Multivalue

[EmployeeID]

IN

[@EmployeeID]

Integer

See Also

Reference

Expression Examples (Report Builder and SSRS)

Concepts

Report Parameters (Report Builder and SSRS)

Add Dataset Filters, Data Region Filters, and Group Filters (Report Builder and SSRS)

Expression Uses in Reports (Report Builder and SSRS)

Data Types in Expressions (Report Builder and SSRS)