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 triggerFires 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 triggerFires 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 triggerFires 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 triggerFires 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.
functionBeforePForm 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 triggerFires 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.
functionAfterPForm 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 triggerFires 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.
functionBeforeReport 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