How to: Add a Filter (Report Designer)

Updated: 5 December 2005

To effectively limit the data used for calculations after retrieving data from a data source, you can set filters on datasets, data regions, and data groupings. To set a filter, open report item's the properties dialog box and select the Filter tab. You can create a simple expression or use the Edit Expression (Report Designer) dialog box to create a complex expression. Each expression can be combined with the expression in the next row through an And or Or operator.

All property pages with a Filter tab present the same choices. You can set Expression, Operator, Value and And/Or values. The following table shows the actions you can take on each column in the Filter list table.

Column Action

Expression

From the dropdown list, choose a valid field for this filter to create a simple expression, or choose <Expression> to use the Edit Expression dialog box to create a complex expression.

Operator

Choose one of the valid operators for this filter expression.

Value

Enter a value directly, or, from the dropdown list, choose <Expression> to use the Edit Expression dialog box to create a complex expression. If Expression contains a field reference, the default type for Value matches the default type for the field. If Expression is not a field reference, the default type is System.String. See the following note about comparing a string type with other data types.

And/Or

Choose the Boolean operator to use to combine this expression with the expression in the next row. This cell activates after you begin to enter an expression in the next row.

ms156270.note(en-US,SQL.90).gifNote:
If you type the character "7" in the Value cell, by default, this evaluates to the string "7". To compare a numeric expression with the number 7, use the expression syntax which begins with an equal sign: =7. When previewing a report, you may see a run-time error from data type mismatches is similar to: "The processing of FilterExpression for the [data set name] cannot be performed. Cannot compare data of types System.Int32 and System.String. Please check the data type returned by the FilterExpression."

To define a filter, open the appropriate properties dialog box, as described in the steps below, and choose the Filter tab. All filters are defined in a similar way.

  1. In Data view, from Dataset, select the dataset to which to apply the filter and click the edit (...) button.

  2. Select the Filters tab and follow the steps below in To set a filter.

  1. In Layout view, if the data region is a table or matrix, click the table or matrix so that column and row handles appear above and next to the table or matrix.

  2. Right-click the corner handle of the table or matrix, or anywhere within the list or chart, and then click Properties.

  3. Select the Filters tab and follow the steps below in To set a filter.

  1. In Layout view, click the table or matrix so that column and row handles appear above and next to the table or matrix.

  2. Right-click the corner handle of the table or matrix and then click Properties.

  3. On the Groups tab, select the group to edit, and then click Edit.

  4. Select the Filters tab and follow the steps below in To set a filter.

  1. In Layout view, right-click the list and then click Properties.

  2. On the General tab, click Edit details group.

  3. Select the Filters tab and follow the steps below in To set a filter.

  1. In Layout view, right-click the chart and then click Properties.

  2. On the Data tab, select a category group or a series group and then click Edit.

  3. Select the Filters tab and follow the steps below in To set a filter.

  1. On the Filters tab, for Filter, do the following:

    • In Expression, type or select the expression for the field that you want the filter to evaluate.
    • In Operator, select the operator that you want the filter to use to compare the evaluated field and the value.
    • In Value, type the expression or value against which you want the filter to evaluate the value in Expression.

Community Additions

ADD
Show: