Creating Parameters for Report
Creating
Parameters for Report
About parameters
A parameter is a variable whose value can be set at runtime
(for example, from the Runtime Parameter Form or the command line). Parameters
are especially useful for modifying
SELECT
statements and setting PL/SQL variables at runtime.
Reports Builder automatically creates a set
of system parameters at runtime, but we can create our own as well. We can
create parameters to replace either single literal values or entire expressions
in any part of a query. We can reference parameters elsewhere in the report,
such as in PL/SQL constructs providing conditional logic for the report.
Note: While
you can delete or rename a user parameter, you cannot delete or rename a system
parameter.
Parameter
values can be specified in these ways:
Accepting the default parameter values
(default values are set in the Parameter properties and you can control whether
the values are displayed at runtime on the Runtime Parameter Form).
Typing the parameter value(s) as arguments on
the command line (where applicable). Parameters specified on the command line
override the equivalent system parameter values set in the report definition.
Choosing from a list or entering the
parameter value(s) in the Runtime Parameter Form.
Oracle Reports providing the
following system parameters. We can change their default values.
System
Parameter
|
Description
|
Is the
number of report copies that should be made when the report is printed.
|
|
Is either:
· The output format for the report (for example,
PDF , HTML , HTMLCSS , RTF , XML , SPREADSHEET , DELIMITED , or DELIMITEDDATA for bitmapped reports).
· The printer definition to use when formatting the report when
DESTYPE=FILE and DESNAME= filename. If MODE=BITMAP , this is
the name of the printer. If MODE=CHARACTER , this is
the character mode printer definition file (.prt file). |
|
Is the name
of the output device (for example, the file name, printer's name, mail user
ID).
|
|
Is the type
of device that will receive the report output (for example,
SCREEN (to format the report
using screen fonts), FILE , PRINTER , MAIL , SYSOUT , CACHE , or LOCALFILE . |
|
Is whether
the report should run in
CHARACTER mode or BITMAP . |
|
Is the print
direction for the report (
LANDSCAPE , PORTRAIT , and
DEFAULT ). |
|
Is whether
the Print Job dialog box should appear before the report is run.
|
User
Parameters
Create a parameter in the Object Navigator.
Use a bind parameter reference in a query,
which causes Reports Builder to automatically create the parameter the first
time it is referenced
Creating a user parameter
Note: Reports Builder automatically creates
a user parameter when you use a bind parameter Reference in a query
1. In the Object Navigator, expand the Data Model node, then click the User Parameters node.
2. Click the Create button in the toolbar.
3. Double-click the properties icon for the new
parameter to display the Property Inspector.
4. Under the General
Information node, replace the
Name property with the desired parameter name.
5. Under the Parameter node, set the Initial Value and List
of Values properties, if required.
6. To validate the parameter's value at runtime,
set the Validation Trigger property by clicking property field to display the
PL/SQL Editor and define the PL/SQL to be triggered at runtime.
LOV for
User Parameters
To create a LOV for a parameter:
1.
In the Object Navigator, expand the Data
Model node, then the User Parameters node.
2.
Double-click the properties icon for the
parameter for which you want to create a LOV to display the Property Inspector.
3.
Under the Parameter node, double-click the List of Values
property field to display the Parameter List of Values dialog box.
4.
Select the type of list that you want to create:
For Static
Values, type a value in the Value text box and click Add. Repeat for each value you
want to add. (Click Remove to delete items from the list)
· For SELECT Statement, type a query
to populate the list of values. You can select more than one column to display
in the LOV, where the first column contains the value to be assigned to the
parameter. The LOV displays columns in the order specified in the query.
5.
If you want the parameter value to be restricted to only those in the LOV,
select the Restrict List to
Predetermined Values check
box. To display a combo box that allows users to edit values or type a
different value in the Runtime Parameter Form, clear the check box.
6.
If you do not want the first column (which contains the parameter value) of
your query displayed in the LOV, select the Hide
First Column check box. If
there is no need to preserve the confidentiality of the first column, clear the
check box.
Caution: If you send the report output to an
HTML file, either from Reports Builder or running it in your Web browser, the
value of the first column will be visible in the HTML source, even if Hide
First Column is selected. If you run the report from a Web browser and the list
of values is unrestricted, the HTML Parameter Form will display a text field
instead of a combo box, and a list of static values that you can copy and paste
into the text field. In this case, the first column will always be shown in the
Parameter Form, even if Hide First Column is selected.
7.
Click OK.
Bind Variables
Bind references (or bind
variables) are used to replace a single value in SQL or PL/SQL, such as a
character string, number, or date. Specifically, bind references may be used to
replace expressions in
SELECT
, WHERE
, GROUP BY
, ORDER BY
, HAVING
, CONNECT BY
, and START WITH
clauses of queries. Bind references may not be
referenced in FROM
clauses or in place of reserved words or clauses.
We can create
a bind reference by typing a colon (:) followed immediately by the column or
parameter name. If you do not create a column or parameter before making a bind
reference to it in a
SELECT
statement, Reports Builder will create a parameter
for you by default.
In the following example, the value of
SELECT CUSTID, NVL (COMMPLAN,: DFLTCOMM) COMMPLAN
DFLTCOMM
replaces null values of COMMPLAN
in the rows selected. SELECT CUSTID, NVL (COMMPLAN,: DFLTCOMM) COMMPLAN
FROM ORD;
The value of
SELECT ORDID, TOTAL
CUST
is used to select a single customer. SELECT ORDID, TOTAL
FROM ORD
WHERE CUSTID =: CUST;
All non-aggregate expressions such as
SELECT NVL (COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL
NVL(COMMPLAN, :DFLTCOMM)
in the SELECT
clause must be replicated in
the GROUP BY
clause. SELECT NVL (COMMPLAN, :DFLTCOMM) COMMPLAN, SUM(TOTAL) TOTAL
FROM ORD
GROUP BY NVL (COMMPLAN, :DFLTCOMM);
The value of
SELECT CUSTID, SUM(TOTAL) TOTAL
MINTOTAL
is used to select customers with a minimum total of
orders. SELECT CUSTID, SUM(TOTAL) TOTAL
FROM ORD
GROUP BY CUSTID HAVING SUM (TOTAL) > :MINTOTAL;
The value of
SORT
is used to select either SHIPDATE
or ORDERDATE
as the sort criterion. Note that this is not the
same as ORDER BY 1
because: SORT
is used as a value rather than to identify the
position of an expression in the SELECT
list.
Note that
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
DECODE
is required in this example. You cannot use a bind
variable in an ORDER BY
clause unless it is with DECODE
. SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
FROM ORD
ORDER BY DECODE (:SORT, 1, SHIPDATE, 2, ORDERDATE);
References in
Example 7: PL/SQL
CONNECT BY
and START WITH
clauses are used in the same way as they are in the WHERE
and HAVING
clauses. Example 7: PL/SQL
Procedure double is
Begin;
:my_param := :my_param*2;
End;
The value ofmyparam
is multiplied by two and assigned tomyparam
. About lexical references
Lexical references are
placeholders for columns or parameters that you embed in a
We cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL, as shown in the example below.
We can create a lexical reference by typing an ampersand (
SELECT
statement. You can use lexical references to
replace the clauses appearing after SELECT
, FROM
, WHERE
, GROUP BY
, ORDER BY
, HAVING
, CONNECT BY
, and START WITH
. Use a lexical reference
when you want the parameter to substitute multiple values at runtime. We cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL, as shown in the example below.
We can create a lexical reference by typing an ampersand (
&
) followed immediately by the column or parameter name. A
default definition is not provided for lexical references. Therefore, you must
do the following:
· Before you create
your query, define a column or parameter in the data model for each lexical
reference in the query. For columns, you must set the Value if Null property,
and, for parameters, you must set the Initial Value property. Reports Builder
uses these values to validate a query with a lexical reference.
Create your query containing lexical
references.
You cannot make lexical references in a PL/SQL statement.
If a column or parameter is used as a lexical reference
in a query, its Datatype must be Character.
If you want to use lexical references in your
SELECT
clause,
you should create a separate lexical reference for each column you will
substitute. In addition, you should assign an alias to each lexical reference.
This enables you to use the same layout field and boilerplate label for
whatever value you enter for the lexical reference on the Runtime Parameter
Form.
If you use lexical references in your
SELECT
clause,
you must specify the same number of items at runtime as were specified in the
report's data model. Each value you
specify for your lexical references at runtime must have the same datatype as
its Initial Value.
If you use lexical references in your
SELECT
clause,
the width of the column is derived from the Initial Value property of the
parameter. Consequently, you should ensure that the Initial Value of the
parameter corresponds to the widest column that you intend to use.
A Reports Builder link should not depend upon a lexical
reference. That is, neither the child column of a link or its table name should
be determined by a lexical reference. To achieve this functionality, you need
to create a link with no columns specified and then enter the SQL clause (for
example,
WHERE
) for
the link directly in the query. For example, your parent and child queries
might be written as follows:
Parent Query:
SELECT DEPTNO FROM EMP
Child Query:
SELECT &PARM_1 COL_1, &PARM2 COL_2
FROM EMP
WHERE &PARM_1 =:DEPTNO
Note how the
WHERE
clause makes a bind reference to DEPTNO
, which was selected in the parent query.
Also, this example assumes that you have created a link between the queries in
the Data Model view with no columns specified.
A lexical reference cannot be used to create
additional bind variables after the After Form trigger fires. For example,
suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference):
SELECT
ENAME, SAL FROM EMP
&where_clause
If the value of the where_clause
parameter contains a reference to a bind variable, you must specify the value in the After Form trigger or earlier. You would get an error if you supplied the following value for the parameter in the Before Report trigger:
WHERE SAL = :new_bind
If you supplied this same value in the After
Form trigger, the report would run.
SELECT &P_ENAME NAME, &P_EMPNO ENO, &P_JOB ROLE
FROM EMP;
P_ENAME
, P_EMPNO
, and P_JOB
can be used to change the columns selected at runtime. For example, you could enter DEPTNO
as the value for P_EMPNO
on the Runtime Parameter Form. Note that in this case, you should use aliases for your columns. Otherwise, if you change the columns selected at runtime, the column names in the SELECT
list will not match the Reports Builder columns and the report will not run.
Example 2: FROM clause
SELECT ORDID, TOTAL
FROM &ATABLE;
A TABLE
can be used to change the table from which columns
are selected at runtime. For example, you could enter ORD
for ATABLE
at runtime. If you dynamically change the table
name in this way, you may also want to use lexical references for the SELECT
clause (look at the previous example) in case the
column names differ between tables. Example 3: WHERE clause
SELECT ORDID, TOTAL
FROM ORD
WHERE &CUST;
CUST
can be used to restrict records retrieved from ORD
. Any form of the WHERE
clause can be specified at runtime.
Example 4: GROUP BY clause
SELECT NVL(COMMPLAN, DFLTCOMM) CPLAN, SUM(TOTAL) TOTAL
FROM ORD
GROUP BY &NEWCOMM;
The value of NEWCOMM
can be used to define the GROUP BY
clause.
Example 5: HAVING clause
SELECT CUSTID, SUM(TOTAL) TOTAL
FROM ORD
GROUP BY CUSTID HAVING &MINTOTAL;
The value of MINTOTAL
could, for example, be used to select customers with a minimum total of orders.
Example 6: ORDER BY clause
SELECT ORDID, SHIPDATE, ORDERDATE, TOTAL
FROM ORD
ORDER BY &SORT;
The value of SORT
can be used to select SHIPDATE
, ORDERDATE
, ORDID
, or any combination as the sort criterion. It could also be used to add on to the query, for example to add a CONNECT BY
and START WITH
clause.
Example 7: CONNECT BY and START WITH clauses
Parameters in
Example 8: Multiple clauses
CONNECT BY
and START WITH
clauses are used in the same way as they are in the WHERE
and HAVING
clauses. Example 8: Multiple clauses
SELECT &COLSTABLE;
COLSTABLE
could be used to change both the SELECT
and FROM
clauses at runtime. For example, you could enter DNAME ENAME, LOC SAL FROM DEPT
for COLSTABLE
at runtime.
SELECT * FROM EMP &WHEREORD;
WHEREORD
could be used to change both the WHERE
and ORDER BY
clauses at runtime. For example, you could enter WHERE SAL > 1000 ORDER BY DEPTNO
for &WHEREORD
at runtime.
Example 9: PL/SQL and SQL
SELECT &BREAK_COL C1, MAX(SAL)
FROM EMP
GROUP BY &BREAK_COL;
BREAK_COL
is used to change both the SELECT
list and the GROUP BY
clause at runtime. The Initial Value of the parameter &BREAK_COL
is JOB
. At runtime, the user of the report can provide a value for a parameter called GROUP_BY_COLUMN
(of data type Character).
In the Validation Trigger for GROUP_BY_COLUMN
, you call the following PL/SQL procedure and pass it the value of GROUP_BY_COLUMN
:
procedure conv_param (in_var IN char) is
begin
if upper(in_var) in ('DEPTNO','EMPNO','HIREDATE') then
:break_col := 'to_char('||in_var||')' ;
else
:break_col := in_var;
end if;
end;
This PL/SQL ensures that, if necessary, aTO_CHAR
is placed around the break column the user chooses. Notice how in SQL, you make a lexical reference toBREAK_COL
. In PL/SQL, you must make a bind reference toBREAK_COL
because lexical references are not allowed in PL/SQL. Differences between bind and lexical references
Bind references are used to
replace a single
value in SQL or PL/SQL. Specifically,
bind references may be used to replace expressions in
An example is:
SELECT ORDID,TOTAL
SELECT
, WHERE
, GROUP BY
, ORDER BY
,HAVING
, CONNECT BY
, and START WITH
clauses of queries. Bind references may not be
referenced in the FROM
clause.An example is:
SELECT ORDID,TOTAL
FROM ORD
WHERE CUSTID =:CUST
Lexical references are placeholders for text that you embed in a SELECT
statement, when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT
, FROM
, WHERE
, GROUP BY
, ORDER BY
, HAVING
, CONNECT BY
, and START WITH
. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter for a column or table, you must have predefined the parameter and given it an initial value.
An example is:
SELECT ORDID, TOTAL
FROM &ATABLE
No comments:
Post a Comment