Parameters
can be populated and validated using various srw pl/sql triggers.
The
following gives examples of:
Validation trigger in parameter spread sheet
Before parameter form trigger
After parameter form trigger
Before report trigger
Examples
of validation triggers on the property sheet for parameter PARAM_SAL.
Query:
select * from emp where sal > :PARAM_SAL
These
functions validate just this one trigger. The validation occurs when
the
user hits next field after inputting a value for the parameter. When the
trigger
is failed it returns to the parameter form.
Example
1:
This
trigger aborts the report execution if no rows match the query criteria
once
the user has entered a value for param_sal.
function
PARAM_SALValidTrigger return boolean is
hold_count
number(4);
hold_sal number(10);
begin
hold_sal := :param_sal;
select count(*) into hold_count from emp
where sal > hold_sal;
if hold_count = 0 then
srw.message(001,'this report returns no
employees');
raise srw.program_abort;
end if;
return(true);
end;
Example
2
In
this trigger the users value for param_sal is compared to the maximum
salary
in the EMP table. If it is greater the report execution is aborted.
example
query for your report: select * from emp where sal >= :parm_sal
function
PARAM_SALValidTrigger return boolean is
hold_max
number(10);
begin
select max(sal) into hold_max from emp;
if :param_sal > hold_max then
srw.message(002,'SAL must be equal to or
less than MAX(SAL)= '||
to_char(hold_max));
raise srw.program_abort;
end if;
return(true);
end;
Example
3
'Before
parameter form' triggers can be used set up the environment for the
report
e.g. create a table. It can also be used to supply default parameter
values.
This
function populates the initial value of the parameter param_sal with the
lowest
salary value from the emp table.
function
BeforePForm return boolean is
min_sal
number(10);
begin
select min(sal) into min_sal from emp;
:param_sal := min_sal;
return(true);
end;
Example
4
'After
parameter form' triggers can be used to validate a combination of
parameters.
Failing results in a return to the PARAMETER FORM.
Query:
select * from emp where job=:jb and deptno=:dt
function AfterPForm return boolean is
begin
if (:dt = 20) and (:jb = 'MANAGER') then
srw.message(003,'cannot report on Managers
in Dept 20');
raise srw.program_abort;
end if;
return(true);
end;
Example
5
'Before
report triggers' can be used to validate a combination of parameters.
The
example below is the same as the after parameter form trigger above
other
than on failure return is passed to the MAIN MENU.
A
'Before Report Trigger' is executed right before formatting the report,
that
is after initializing all internal structures, opening all SQL cursors
etc.
In other words, after 'compiling' the report definition.
A
second use of this trigger may be to launch a number of other reports
using
the SRW.RUN_REPORT procedure.
function
BeforeReport return boolean is
begin
if (:dt = 20) and (:jb = 'MANAGER') then
srw.message(004,'cannot report on Managers
in Dept 20');
raise srw.program_abort;
end if;
return(true);
end;
