Data used in the report may be filtered by various criteria. The criteria may be set for the report by the developer ( non-Ask parameters) or may be supplied by the user when the report is executed ( Ask parameters).
To set a filter for the report click on the Modify Data Source button to display the Select or Modify Data Objects dialog. Click on the Filter tab to display:
To create a filter parameter, click on the Add a Parameter button to display the Parameter Details dialog.
A parameter takes the form of an equation similar to:
Label is Compared to Value
where label represents a column name, compared to represents a comparison operator, and value represents a threshold.
The available comparison operators are:
- Equal to
- Not equal to
- Less than
- Greater than
- Less than or equal to
- Greater than or equal to
- Starts with 1
- Does not start with 1
- Ends with 1
- Does not end with 1
- Contains 1
- Does not contain 1
- Is null
- Is not null
- Not between
- In list
- Not in list
- In cascading list
These operators are only available for data of type String or Text.
The operators available are dependent upon the column's data type. For example, a numeric data type would not include operators such as true/false.
To set a data parameter to a Specific Value or Pre-defined Date:
- From the Column drop-down menu, select the desired column.
- From the Operator drop-down menu, select the desired comparison operator.
- From the Value type drop-down menu, select either Specific Value or Pre-defined Date.
- Specify a threshold value(s) in the Value field. Click the icon to view a list of valid values from the database and then populate the Value field from the value(s) selected.
- Click OK to add the parameter to the report.
- Click the icon adjacent to any parameter to delete that parameter from the report. Click the icon to modify a parameter.
- Add more parameters by clicking the Add a Parameter button and repeating the steps above.
If the 'In list' or 'Not in list' operator is selected, then more than one value may be specified. If manually typing in each value, then follow each entry by the [ENTER] key.
If the main data column is of date type, an additional choice of values called Pre-defined Date. Pre-defined dates are dates in the form of a token, such as Today, This Year End, Last Fiscal Quarter Start, etc. Pre-defined dates get evaluated at the time the report runs. If the pre-defined date token is evaluated to a column containing time, then no records will be returned at runtime.
If the value is a number, the Value field must contain a valid number to build the report. If the 'In cascading list' or 'Not in cascading list' operator is selected, then a user must choose a cascading filter and as a result do not have the option to specify a value. See the 'Running Reports' chapter for more information about cascading filters.
The Filter functionality does not support conditions against data types of type Time. Date/Time data types are supported but their time portion will be ignored.
Specifying a Session Parameter as a value in report parameters is permissible. There are implications related to scheduling and archiving. In addition, modification of the session parameter after the report is built may break the report which may break schedules. Subscribed users may be automatically notified when a schedule is broken .
Hint: When adding multiple parameters, a logical operator (And or Or) becomes available for selection at the beginning of the next parameter. Use this operator to set the cumulative conditions for the parameters.
If the report contains two or more parameters, the icon appears for each additional parameter. The directional pad gives users the ability to create levels for each parameter. Control the order of evaluation for multiple parameters using the directional pad.
Enable the Ask in Report option to offer the report viewer the option of changing the threshold value before the report is rendered. Instead of limiting the report to predefined parameters, the values can be modified while browsing the report.
Marking an operator as an "ask" parameter presents a few other options in the Parameter Details dialog.
The parameter caption will be automatically generated if left blank.
Control Type values depend on both the operator picked and the selected column's data type. For example, a text type column with Equal To operator presents the following choices:
- List (single select)
The In list operator produces the following choices:
- List (multi-select)
- Text (multi line)
A date type column with Equal To operator produces the following choices:
- List (single select)
A Boolean type column with Equal To operator produces the following choices:
- List (single select)
If more than one parameter is presented to the user, the option to display each parameter adjacent the previous one or on a new line is available. Choose by marking the desired radio button for Display this parameter.
With the control type of List (multi select) selected, the initial display of the report will appear with the Ask parameter's list populated with all possible values from the database and with the default values highlighted/pre-selected.
With the control type of Text (multi line) selected, the initial display of the report will appear with the Ask parameter's list populated with only the default values. The values will not be highlighted/pre-selected.
With the control type of Dropdown selected, Offer All Option checkbox is made available. Checking this checkbox will set the first row of the dropdown list to All , allowing the report viewer to use that option as the report parameter.
Parameters based on comparing two columns cannot be asked from the user.