Parameters as drop down
The IView parameters can be made into a drop down box instead of text box. This is done by changing the Mode of entry of the parameter to Select and assigning an SQL. The SQL result will be displayed as options in the drop down.
Calculated parameters
Consider an example which list all the employees who have joined before a given date. The date should be 3 days before today by default. It can be changed as per need.
Select name, age, gender, grade, department from employees where department = :department and joindate <= :JoinedBefore
In the Params tab, set an expression to JoinedBefore parameter as addtodate(date(),0-3)
The parameters are displayed as below
Department
Joined before
The value for Joined before will be three days before the current day as per the given formula.
Validating parameters
Parameters can be validated by providing a validate expression using an iif function. If the result of the expression is not “T” then the resultant text will be displayed as an error message.
To set a validation as “Joined before should not be greater than today”, set a validate expression in the Params tab as below
iif(JoinedBefore > date(), {Date cannot be greater than today}, {T})
Selecting multiple values for a parameter
A multi select parameter is achieved by setting the type of the parameter as Multi select. If the multi selection field is of character datatype, the SQL should be written to relate to a table named axpselectionc.This table has a field name selection in which the record id of the selected records is stored.In case the selection field is numeric then the selection table name should be axpselectionn. In case of date fields, the selection table name should be axpselectiond.
Select name, age, gender, grade, department from employees a, axpselectionc b where
a.department = b.selection and department = :department and joindate <= :JoinedBefore
Add a parameter named “Departments”, set type as “Multi select” and assign an SQL that will list all the department names from the departments table.
The selected departments will be stored in the selections field of axpselectionc table . This table is joined with the SQL to get the employees of selected criteria only.
At run time the parameter window will look as below
Finance
Sales
Production
Admin
Purchase
Sales
The selected departments will be stored in axpselectionc table in the selections field. This table is joined with the SQL to get the employees of selected criteria only.
Hidden parameters
There can be some parameters that are introduced only for calculations & should not be visible to the end user. This can be achieved by adding parameters and setting its Hidden property to true.
Add parameters directly
Parameters need not be part of the SQL. They can be added directly to the IView using the Add parameter button in the Params tab. The parameter values can be used in column expressions.
Note : Parameter names and column names should not be the same.