Bind Parameters
Accepts value at run time.
Bind parameter is created by using colon ( : ) in SQL Query.
Ex:
We want to create a report which accepts deptno and display employees working for that deptno.
Open Report Builder tool Use the report wizard OK Next Create both Web and Paper Layout Next Tabular Next SQL Query
Data Source Definition -- Select * from emp where deptno = :dno Next Provide username , password and database details Connect
We get a message - The Query Q_1 has created bind parameter DNO
( As we know, bind parameter is prefixed with colon (: ) , in the SQL Query, we have used colon, So report builder identified DNO as bind parameter )
OK Move all the fields from Available Fields to Displayed Fields Next Next Next Select predefinied template Green Next Finish.
We get run time parameter form.
Enter the value - 10 Press Enter Key
In the output, we get only the employees working for deptno 10.
Let's run the report one more time.
Click on Program in Menu bar Run Paper Layout
As Usual, We get Runtime parameter form
Enter the Value - 20 Press Enter Key
In the output, we get only the employees working for deptno 20.
Go to File Save as EMP1.rdf
The bind parameter information is also available in Object Navigator ( Press F5 )
Under Object Navigator Data Model User Parameters DNO
So, we have achieved what we want.
Our report accepts deptno as parameter and display employees working for that deptno.
Providing list of values to the parameter
We can provide the list of values to the bind parameter DNO.
List of values, can be created in two ways
1) Static Values
2) Select Statement
Let us look at both the ways practically.
Creating list of values - static method
Under Object Navigator Data Model User Parameters DNO , Right click select property Inspector
Click on List of values
We get parameter list of values window.
Select radio button Static values.
Enter Value - 10 Click on Add button.
Enter Value - 20 Click on Add button.
Enter Value - 30 Click on Add button.
Enter Value - 40 Click on Add button.
Enter Value - 50 Click on Add button.
Enter Value - 60 Click on Add button.
Click on OK
Observer, we have entered six static values ( 10, 20, 30, 40, 50, 60 )
( Note: We do not have employees working in deptno 40, 50 and 60 )
Save the report.
Run the report.
In runtime parameter form, we get list of values to the bind parameter.
Select the value 10 and Run the report.
We get employees working in deptno 10.
Now, let us learn creating list of values using select statement.
Under Object Navigator Data Model User Parameters DNO , Right click select property Inspector
Change the datatype - Number
Click on List of values
We get parameter list of values window.
Select the radio button SELECT Statement
SQL Query statement - select distinct deptno from emp;
Click on OK
Save the report.
Run the report.
In runtime parameter form, we get list of values to the bind parameter.
Observe, we got only three values 10, 20 and 30.
Because the select statement used in creating list of values is returning only thee values.
Now, we know what is bind parameter? and how to creating list of values to the bind parameter.
Assignment 1
Create report based on the following query
select * from emp where deptno = :deptno and job = :designation
Assignment 2
select * from emp where to_char( hiredate, 'YYYY') = :year;
Lexical parameter
Lexical parameter accepts string at run.
The string gets embedded into the query at run time.
Bind parameter is created by using ampersand ( & ) in select statement.
Ex:
We want to create a report to display employee details based on the condition.
Condition is passed as a parameter.
Open the report builder tool Use the report wizard OK Next Create both Web and paper layout Next Tabular Next SQL Query Data source definition - select * from emp &a
Next provide username , password and database Connect We get a message - The query Q_1 has created the lexical parameter(s) a
OK Move all the fields from Available Fields to Displayed Fields Next Next Next Select predefined template -Peach Next Finish
We get Run time parameter form
Enter the value -- where sal > 2000 -- press Enter Key
In the output, we get only the employees who's salary is greater than 2000
Let's run the report one more time.
Click on Program in Menu bar Run Paper Layout
As Usual, We get Runtime parameter form
Enter the Value - where job ='CLERK' Press Enter Key
In the output, we get only the employees working as Clerks.
Go to File Save as EMP2.rdf
Observe, We have execute the report two times.
For the 1st time, we have passed the value -- where sal > 2000
For the 2nd time, we have passed the value -- where job = 'CLERK'
We got the appropriate output as per the condition for every execution.
So, our report is displaying the employee details based on the condition passes as parameter.
Lexical parameter accepts string ( where clause ) at runtime and giving the desired results.
Observe, the query used in developing the report.
select * from emp &a
In the query, a - is lexical parameter.
And we know that lexical parameter is prefixed with ampersand ( &) operator.
The lexical parameter information is also available in Object Navigator ( Press F5 )
Under Object Navigator Data Model User Parameters A
Use the report wizard 
