Filter equation examples in a paginated report (Report Builder)
Article
10/02/2024
Applies to:
Microsoft Report Builder (SSRS)
Power BI Report Builder
Report Designer in SQL Server Data Tools
To create a filter in a paginated report, 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 paginated report definition (.rdl) files in Microsoft Report Builder, Power BI Report Builder, and in Report Designer in SQL Server Data Tools.
Pattern Options
Built-in pattern matching provides a versatile tool for string comparisons. The pattern-matching features allow you to match each character in string against a specific character, a wildcard character, a character list, or a character range. The following table shows the characters allowed in pattern and what they match.
Characters in pattern
Matches in string
?
Any single character
*
Zero or more characters
#
Any single digit (0–9)
[charlist]
Any single character in charlist
[!charlist]
Any single character not in charlist
Note
To search for the above reserved characters use the *[ ]* escape pattern. For example, to search for # use *[#]*
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
>
2008-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.
Learn how to add a filter to a dataset, data region, or group when you want to include or exclude specific values for calculations in a paginated report.
Learn how to control, organize, and sort paginated report data with expressions based on dataset fields and parameters in the Report Data pane of Report Builder.
Choose report data, connect related reports, and vary the report presentation with the addition of report parameters in a paginated report in Report Builder.
Control the content, design, and interactivity of your paginated report with expressions that enable you to retrieve, display, filter, and format data in Report Builder.
Report filtering is a complex topic because many techniques are available for filtering a Microsoft Power BI report. However, with complexity comes control, allowing you to design reports that meet requirements and expectations.
Microsoft Report Builder (SSRS)