For SQL used in a Query Object, users can specify Filter options.
To access Filter function:
Navigate > Studio Report > SQL Report Wizard
Select SQL Source > Select Query Object > Select Query > Click Filters and Sorts > Select Filter tab
At report run time, a user may wish to receive the data that makes sense to him/her at that time. On this tab, you can set filters so that user can provide filter values at report run time.
To add filters:
Select a field from Available Fields. Click > button to move the fields to Selected Fields list. At report run time, Ad hoc tab of Input Parameter Form will have 3 rows having all the selected fields as dropdown. Use >> to move all the fields to Selected Fields list.
To un-select a field, click the field from Selected Fields list and click < button. Click << button to empty Selected Fields list.
- Qualifier: When SQL is typed in Edit tab, fields may not be prefixed with table name and so, Available Fields list also do not have table names prefixed. Select a field and specify table name to associate it with a table. This is especially useful when you get the same field name from two tables and wish to differentiate one from another.
- Max Rows: The maximum number of records to be fetched from the database. Number specified here would appear by default on input parameter form at run time. User can change it if required at run time.
- Suppress Duplicates: Select this check box to get only one record if record has multiple records having completely duplicate values.
To get a row at the top:
Click the in the leftmost cell of column header. This inserts a row below the column header.
To get a row below current row:
Click in the row below which you want to add a row for condition. A row will be inserted in the row below the respective row.
To remove a condition:
Click button in respective condition row.
Specify / select values the selected field from other columns. Details are provided after these steps.
- FieldName: The field name for which default value(s) is being set.
- Type: Select Ad hoc to use the fieldName for accepting the value. Select UserParameter get value using a pre-set user parameter.
- Data Type: Specify data type of the filter parameter.
- Parameter: If Type is UserParameter, specify the parameter name.
- FetchData: Select this check box for the field if filter type is Ad hoc and you want data should be fetched from the database. In this case value box for this field will appear as a drop-down box.
- Criteria: Select criteria for the field.
- Value1, Value2: Based on selected criteria, specify Value1 or Value1 and Value2. For example, for “Between” as criteria, you need to provide two values.
- Mandatory: Select this checkbox if it is mandatory for user to provide value for this field at runtime.