IView Parameters

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.