In the earlier topic, you have created a simple IView. In this section, you’ll be creating an IView with parameters.
- An IView can have parameters. These parameters are defined as part of the SQL statement. The values for the parameters will be accepted at run time before displaying the IView result.
Consider the below SQL in an IView :
Select name, age, gender, grade, department from employees where department = :departmentname
“departmentname” is a parameter. When the IView is run, a parameter window is displayed with a text box to enter the department name. User can enter the department name in this text box. Based on this value, the IView is displayed with records filtered for the given department.
Parameters in SQL – Points to be noted
- A parameter in the SQL statement is used to retrieve data based on a specific criteria.
- At run time,the parameter values will be accepted from user and Iview report will be generated based on these values.
- A parameter is prefixed with a colon ( : ) in the SQL statement.
- The parameter name should not be the same as the column name used in the SQL.
- An SQL statement can have multiple parameters.
Creating an IView with parameters
- Modify the SQL written in the earlier topic Steps to create an IView by passing Supplier Name as the parameter. The SQL can be rewritten as : Select VendorsId, SupplierName, Address, Taxno from vendors where SupplierName = :name
- Click the Execute button to execute the SQL. The Define Parameters window appears.
- In the Define Parameters window choose the datatype of the parameter based on the requirement. The datatype of parameter should be same as the datatype of the column which is used as a parameter.In the current example, the parameter name is of datatype Character.
- A default value can be set by entering some value into the Value field.
- Click Ok .
- Save the changes. Click Params tab to view the parameter details.
- Change the Caption to Supplier Name.
- Select the ModeofEntry as select and write a SQL to fetch the Supplier Name from the vendors table – Select SupplierName from vendors order by SupplierName.
- Click Apply.
- Click Options->Properties to open Iview Properties , select the option Display Param Window and click Ok. This is required to display the parameters at runtime.
- Save the IView.
- Click Run and select the Supplier Name from the Parameters window.
- Click Ok and the IView with the records that match the selected parameter is displayed.