You can use complex expressions to filter the data in a report by creating an advanced filter. Advanced filters support parenthetical precedence, operators such as IN and NOT IN, and nesting using AND/OR syntax between filters. You can use the Expression Builder to help you create the advanced filter. For example, you could define a filter as follows:
([Categories.CategoryName] IN ('Beverages','Dairy')) AND ([OrderDate: Sum: UnitsInStock] < 100 OR [OrderDate: Sum: UnitsOnOrder] = 0)
Tip: If you want to create a filter with a simple filter condition, see Defining Filters for information on creating a regular filter.
To define an advanced filter for a report
| 1. | Open a report in Classic Designer. |
| 2. | Click on the header of a column and select Filter from the menu. (If the Filter Column dialog box opens, click Cancel to show the Filters dialog box.) |
| 3. | Click the Advanced Filter button on the Filters dialog
box to open the Advanced Filter dialog box as shown below. |
| 4. | Enter a name that will be displayed at the top of the report to indicate the filters that are applied. |
| 5. | Select the filter type to use: |
Data -- (default) The filter operation is carried out at the database level and returns only results that meet the criteria.
Display -- The filter operation is applied after the database returns the result set for the report query; only the selected values are displayed in the report.
| 6. | Enter an expression for the filter in the text box
or click the Expression Builder icon  to use
the Expression Builder to create the expression. |
| 7. | When you are finished, click Save in the Advanced Filter dialog box. You will see the new filter you have created in the Filters dialog box. |
| 8. | Click Save in the Filters dialog box. |
The result set in the report will now be filtered as specified by the filter expression. The filters being applied to the report will be displayed at the top of the page for reference.
Tip: To edit or delete a filter, click the filter at the top of the page to bring up the Filters dialog box.
See Also
Defining Filters
Setting up Dashboard Prompts
Filtering a Report in Designer