Formula Column:
A
formula column is a user-created column that gets its data from a PL/SQL
function or expression, a SQL statement, or a combination of these.
A
formula column performs a user-defined computation on the data of one or more
column(s), including placeholder columns. For example, :ITEMTOT *.07 is a
formula that performs a computation on one column, while :SAL + :COMM performs
a computation using two columns in a record. You create formulas in PL/SQL
using the PL/SQL Editor.
Note:
Formula columns should not be used to set values for parameter.
Placeholder Column:
A
placeholder is a column for which you set the datatype and value in PL/SQL that
you define.
Placeholder
columns are useful when you want to selectively set the value of a column (for
example, each time the nth record is fetched, or each time a record containing
a specific value is fetched, and so on). You can set the value of a placeholder
column in the following places: the Before Report Trigger, if the placeholder
is a report-level column a report-level formula column, if the placeholder is a
report-level column a formula in the placeholder's group or a group below it
(the value is set once for each record of the group).
Ex: Develop a report to calculate the tax of each
employee based on the salary
If
salary < 10000 then no tax
If
salary is between 10000 and 20000 then tax = 2% of salary
If
salary is between 20000 and 30000 then tax = 3% of salary
Otherwise
tax is 4% of salary
Employee report
Eno Ename Salary Tax
Xxxxx xxxxx xxxxx xxxxxx
Xxxxx xxxxx xxxxx xxxxxx
Xxxxx xxxxx xxxxx xxxxxx
Xxxxx xxxxx xxxxx xxxxxx
Step
1: Create the SQL query in data model
window
Step2: Add the formula column and place holder
column in side the group
Step
3: write the function in (CF_1) to calculate the tax for the salary, this will
be called for every record
Step 4:
Write the trigger
Attach
the source to the repeating frame and
all the fields (Attach the CF_1 to the tax field i.e., f_4)
Compile
and run the report
In
the above example we also create the summary column.
TASK :
Show the organization_id, organization_name and item_id and item_name
Get
the organization name using formula column.
Report Triggers
Report
triggers execute PL/SQL functions at specific times during the execution and
formatting of your report. Using the conditional processing capabilities of
PL/SQL for these triggers, you can do things such as customize the formatting
of your report, perform initialization tasks, and access the database. To
create or modify a report trigger, you use the Report Triggers node in the
Object Navigator. Report triggers must explicitly return TRUE or FALSE.
Oracle
Reports has five global report triggers. You cannot create new global report
triggers. The trigger names indicate at what point the trigger fires:
Before Report
trigger
Fires before the report runs but after queries are parsed.
After Report
trigger Fires
after you exit the Paper Design view, or after report output is sent to a
specified destination, such as a file, a printer, or an e-mail ID. This trigger
can be used to clean up any initial processing that was done, such as deleting
tables. Note, however, that this trigger always fires, whether or not your
report completed successfully.
Between Pages
trigger Fires
before each page of the report is formatted, except the very first page. This
trigger can be used for customized page formatting. In the Paper Design view,
this trigger only fires the first time that you go to a page. If you
subsequently return to the page, the trigger does not fire again.
Before Parameter
Form trigger
Fires before the Runtime Parameter Form is displayed. From this trigger, you
can access and change the values of parameters, PL/SQL global variables, and
report-level columns. If the Runtime Parameter Form is suppressed, this trigger
still fires. Consequently, you can use this trigger for validation of command
line parameters.
After Parameter
Form trigger
Fires after the Runtime Parameter Form is displayed. From this trigger, you can
access parameters and check their values. This trigger can also be used to
change parameter values or, if an error occurs, return to the Runtime Parameter
Form. Columns from the data model are not accessible from this trigger. If the
Runtime Parameter Form is suppressed, the After Parameter Form trigger still
fires. Consequently, you can use this trigger for validation of command line
parameters or other data.
SRW.MESSAGE built-in procedure (SRW = Service request window)
This
procedure displays a message with the message number and text that you specify.
The message is displayed in the format below. After the message is raised and
you accept it, the report execution will continue.
Syntax
SRW.MESSAGE(msg_number
NUMBER, msg_text CHAR);
Parameters
msg_number is a number from
one to ten digits, to be displayed on the message line. Numbers less than five
digits will be padded with zeros out to five digits. For example, if you
specify 123, it will be displayed as SRW-00123.
msg_text is at most 190
minus the msg_number alphanumeric characters to be displayed on the message
line
Before Parameter
Form trigger
Fires before the Runtime Parameter Form is displayed. From this trigger, you
can access and change the values of parameters, PL/SQL global variables, and
report-level columns. If the Runtime Parameter Form is suppressed, this trigger
still fires. Consequently, you can use this trigger for validation of command
line parameters.
function
BeforePForm return boolean is
ln_count number:=0;
begin
select count(*) into ln_count from emp; -- Check the data is exist or not
if ln_count>=1 then
srw.message(1, 'Before parameter form trigger, Total records in the emp
table ='||ln_count);
return (TRUE);
else
srw.message(2,'Before
parameter form trigger, No data is
available');
return(FALSE);
end if;
end;
Above will check whether the data is exist or not in emp table,
if data exists then return true and open the parameter form other returns
false.
Compile and run the report with below scenarious
1.
Have some data in the
table
2.
Remove the data from the table
After Parameter
Form trigger
Fires after the Runtime Parameter Form is displayed. From this trigger, you can
access parameters and check their values. This trigger can also be used to
change parameter values or, if an error occurs, return to the Runtime Parameter
Form. Columns from the data model are not accessible from this trigger. If the
Runtime Parameter Form is suppressed, the After Parameter Form trigger still
fires. Consequently, you can use this trigger for validation of command line
parameters or other data.
function
AfterPForm return boolean is
ln_deptno number:=null;
begin
if :deptno=null then
srw.message(111,'Please enter
the deptno');
return(false);
end if;
select count(*) into ln_deptno from emp where deptno=:deptno;
if ln_deptno<1 then
srw.message(200,'Deptno is not
exist');
return(false); -- Output will not display
else
return (TRUE); --
Will generate and show the output
end if;
end;
Above
function will check whether the value given in the parameter is valid or not
Scenario
1: Enter the valid deptno which is exist
in the table and see
Scenarion
2: Enter the invalid deptno which is not
exist in the table and see
Before Report
trigger
Fires before the report runs but after queries are parsed.
Ex:
Create the trigger to check whether the data is exist or not in the table
before the output of the report is displayed.
function
BeforeReport return boolean is
ln_count number:=0;
begin
select count(*) into ln_count from emp;
if ln_count>=1 then
srw.message(1, 'Total records in the emp table ='||ln_count);
return (TRUE);
else
srw.message(2,'No data is
available');
return(FALSE);
end if;
end;
Compil and run the report.
Scenario 1: Have some
data and run the report. It will show no
of records and report output
Scenario 2: Remove the
data from emp table and run the report.
It will show the message as no records and return false.
No comments:
Post a Comment