About dynamic filters

A dynamic filter restricts the data returned by a query based on an information object. Modifications to the WHERE or HAVING clause occur at report run time, based on the values that a report user provides for the dynamic filter. A dynamic filter accepts an operator and zero, one, two, or more operands.

Table 2-3 lists operators available for a dynamic filter and the number of operands required by each operator.

Table 2-3

Operators and number of operands required for a dynamic filter

Operators

Number of operands required

Is Not Null, Is Null

Equal to, Greater than, Greater than or Equal, Less than, Less than or Equal, Like, Match, Not Equal to, Not Like, Not Match

Between, Not Between

In, Not In

1 or more

For example, you create a dynamic filter on the customerName column in CustomersOrdersDetails.iob. Responding to a report prompt, a report user selects the LIKE operator and the Atelier graphique operand. The query that returns the report data set is modified to include the following clause:

WHERE CustomerOrdersDetails.customerName LIKE 'Atelier graphique%'

Figure 2-24 shows a preview of the filter in Parameters.

Figure 2-24

Selecting a customer name to filter a set of customer information

Prompt properties control how the dynamic filter prompt appears to a report user on the Parameters page. Prompt properties include the display control type, for example text box or combo box and a list of values. If a dynamic filter is auto-generated from a predefined filter, the dynamic filter inherits the prompt properties of the predefined filter. By default, a report user is not required to provide a value for a dynamic filter that is automatically created from a predefined filter.

Unless the display control type for a dynamic filter is text box, you must create a list of values for the dynamic filter. You create a list of values by typing the values or by creating an Actuate SQL query that retrieves the values.

The query must meet the following requirements:

The query must retrieve one or two columns from an information object or map, for example:

SELECT DISTINCT cust ID, CAST(customerName AS VARCHAR)

FROM "IO Designs/MyProject/InformationObjects/MyInformationObject.iob" ORDER BY 2

The first column, for example custID, contains the filter values. The second column, for example customerName, contains the values displayed to the report user. Use a relative path to reference the information object or map. The path must be relative to the resource root.

The query must not contain a WITH clause.