At report run time, you may wish to receive the data that makes sense at that time. On this tab, you can set filters so that you can provide filter values at report run time.
Figure 13: Applying filters on SQL
To add filters
Select a field by clicking it (Press and hold Ctrl key and click fields to select multiple fields) 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 of the selected fields as dropdown.
Use >> to move all the fields to Selected Fields list.
To deselect 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: Check this check box to get only one record if record has multiple records having completely duplicate values.
To set up default value for filter
- Select the field name from the table.
- 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 Adhoc 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. For more information on Report Parameters, refer to Desktop Studio – Report Parameters document.
- FetchData: Select this check box for the field if filter type is Adhoc 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 check box if it is mandatory for user to provide value for this field at runtime.