Formulas are PL/SQL functions that populate formula or placeholder columns. You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Inspector (that is, the PL/SQL Formula property).
A column with Datatype
property set to Number can only have a formula that returns a value of datatype
NUMBER
. A column with Datatype property set to Date can only have
a formula that returns a value of datatype DATE
. A column with
Datatype property set to Character can only have a formula that returns a value
of datatype CHARACTER
, VARCHAR
, or VARCHAR2
.
In a formula, you can read and assign values to the column itself, placeholder columns, and parameters of the correct frequency (look at the rule below). For example, you can use the value of a column called COMP in a condition (for example, IF :COMP = 10) and you can directly set its value in an assignment statement (for example, :COMP := 15).
A formula can only make reference to columns that are in the same or a higher group in the group hierarchy. For example, a formula for a report-level column can only reference other report-level columns.
Formulas are calculated such that any column referenced in the formula will be calculated first. To do so, Reports Builder builds a dependency list, to guarantee proper ordering of calculations. Note that circular dependencies, in which a column references another column which in turn references the first column, either directly or indirectly, are not allowed.
When using SRW.DO_SQL, we recommend that you do not read database values that are updated or inserted in the same report. There is no guarantee of the exact time Reports Builder will fetch records from the database for formatting the output. Reports Builder does internal "data look-ahead" to optimize performance. Thus, a particular record might already have been accessed before an update is issued to the same record. Reports Builder builds internal dependency lists which guarantee that events, such as invocation of user exits, calculation of summaries, and so on, happen in the correct order. However, Reports Builder cannot guarantee these events will be synchronized with its internal data access or with the formatting of data.
The following example populates the column with the value of the salary plus the commission.
function salcomm return NUMBER is
begin
return(:sal + :comm);
end;
The following code adds the commission to the salary if the value for the commission is not null.
function calcomm return NUMBER is
temp number;
begin
if :comm IS NOT NULL then
temp := :sal + :comm;
else
temp := :sal;
end if;
return (temp);
end;
Creating or editing a formula column
Copyright © 1984, 2005, Oracle. All rights reserved.