A variable filter is a preset filter that is associated with the Interactive Reporting document file by the designer and resolved only when the query is refreshed. At that time, you are prompted to select or enter filter values and complete the constraint. A variable filter prompt is displayed only if one has been set in the underlying Interactive Reporting document file.
Variable filters work particularly well with custom lists. If a custom list has been created, you can respond to the prompt by simply selecting a value from the custom list. For example, you may have an Interactive Reporting document file you use monthly to monitor inventory levels. Each time you use the Interactive Reporting document file, you run it separately for each product line you carry. You can accelerate the process by making the filter variable on the product line item, and create a custom values list. Each time you refresh theInteractive Reporting document file, you can select a new product line without redefining filters.
You can select from three types of filters from which to apply a variable constraint, including:
Show Values – Supplies database values associated with the item.
Custom Values – Supplies an empty text box for entering custom values.
Custom SQL – Supplies an empty text box for entering a Structured Query Language (SQL) clause to be included in the query statement.
If you do not need to select specific variable filters for the query, choose “Set” to accept the predefined filters and refresh the query. If you want to modify or add other values, complete steps 2 and 3 below.
To specify a variable filter.
If you are working with the Show Values or Custom Values options, select Include Nulls to include data where the data item has no value.
Retrieves records where the filtered item has no value; for example, a field in which no data has been entered. A null value is not equal to zero.
If you are working with the Custom SQL option, skip the remaining steps, enter your Custom SQL and click Set.
Select the NOT check box to negates the operator it precedes.
Select a comparison operator to use for filtering value.
For example, if you specify the > Greater Than and then specify a value of 10,000, then values greater than 10,000 are returned.
For a list of valid comparison operators, see Comparison Operators.
The filter is applied in the results set.
To suspend a filter temporarily without deleting it, click Ignore.
To see the result of setting the variable filer, select the Refresh command after the variable has been set.