Email Us : sunilkumark11@gmail.com
Free Training
 SQL
 PL/ SQL
 Forms
 Reports
 


     Reports with parameters

Reports have two types of parameters

1) Bind parameters
2) Lexical Parameters

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