Purpose of Formula Column

Purpose of Formula column,
Summary column and Place Holder

column in Oracle Reports
4 MAY 2012 ARTICLE BY SHAILENDER THALLAM
In Oracle reports we use Formula column, Summary column and Place Holder column in
different scenarios, this post is to avoid confusion and give clear picture as when and where
we need to use these columns.
Formula column
We use formula column to calculate some information dynamically using information based
on the columns of the data model or from the concurrent program parameters. It is
basically used to apply some custom logic on input data and return some value.
Formula columns can be written using PL/SQL syntax and we can use pl/sql functions for
computation on the data. Formula column will accept data of Character, Number, or Date
type.
Should it be Inside or outside data group?

If we want to calculate a value for every row place the formula column in the group of the
data model , so that it called every time for every record and if we want to derive a value at
report level place the formula column outside to the data group.
Formula columns are generally preceded by CF_ to distinguish from other columns. Column
names or parameters with a prefix : in formula column are considers as input to a formula
column.
Examples:
1.
Comparison of data in two different columns/variables and perform some action.
2.
Using some standard oracle pl/sql functions to find out some values like, finding out
the master org using oracle standard function as shown below
oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id())
3.
When you need to use any If-else block
4.
To execute some sql query to find out email-address of a party using party_id
Note: Formula column should return some or the other value.
Summary column
Summary columns are used for calculating summary information like sum, average etc,. on
specific columns of a data group. This column uses a set of predefined oracle aggregate
functions. Summary columns are generally preceded by CS_ to distinguish them from other
columns.
The data type of a summary column depends on the data type of the source of the
summary. If you change the data type of the source column, the data type of the summary
also changes. In report layout summary columns should be used out of repeating frames.
Summary columns will have only one parameter as input.
Below are the standard functions provided by Oracle for a summary column
Function
Purpose
Average
Calculates the average of the columns values within the reset group.
Count
Counts the number of records within the reset group.
First
Prints the columns first value fetched for the reset group.
Last
Prints the columns last value fetched for the reset group.
Maximum
Calculates the columns highest value within the reset group.
Minimum
Calculates the columns lowest value within the reset group.
% of Total
Calculates the columns percent of the total within the reset group.
Std.
Deviation
Calculates the columns positive square root of the variance for the reset group.
Sum
Calculates the total of the columns values within the reset group.
Variance
Sums the squares of each column values distance from the mean value of the reset group and
divides the total by the number of values minus 1.
All the above functions work only for number data type column and return number data
type.
Place Holder column

Place holder column is an empty container at design time which is used to hold some value
in run time, they are like local/global variables which can be used in the logic of a formula
column or in report triggers. Value in the place holder column can be directly accessible in
the report layout.
Should it be Inside or outside data group?

We can use these place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number data type. Place Holder columns
are generally preceded by CP_ to distinguish from other columns.
Forget about having Oracle R12 suite. In general any Oracle engine will behave like this. Let me
show you some examples.
TABLE A
- COL1 PK1
- COL2 PK1
- COL3
TABLE B
- COL4 PK2
- COL5 PK2
- COL6
TABLE C
- COL7
- COL8 -PK3
- COL9 -PK3
In this model, whenever we join any tables unless you join on their key combinations or based on the
rule that for any given row you retrieve a maximum of one row from the other table.
SELECT A.COL1
FROM TABLEA, TABLEB
WHERE COL1 = COL4
AND COL2 = COL5;
OR in ANSI syntax
SELECT A.COL1
FROM TABLEA JOIN TABLEB ON COL1 = COL4 AND COL2 = COL5;
So this can be extended on the third table.
SELECT A.COL1
FROM TABLEA, TABLEB, TABLEC
WHERE COL1 = COL4
AND COL2 = COL5
AND COL1 = COL8
AND COL2 = COL9;
PS: The assumption is that the combined primary key on both the tables will always provide one row
for the other.
You need to extend your ON clauses to include a condition so that for each entry in TABLE A there is
only one entry in TABLE B that matches the condition and that for each entry in TABLE B there is
only one entry in TABLE C
You can always try to join tables one by one and keep the query building until and unless you done
violate the thumb rule (said above).
If, I change the join condition and any table duplicates while performing the same, then the same will
be propagated to the subsequent joins.

Purpose of Formula Column

Uploaded by

Copyright:

Available Formats

Purpose of Formula Column

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Purpose of Formula Column

Uploaded by

Copyright:

Available Formats

Purpose of Formula column,

Summary column and Place Holder

Should it be Inside or outside data group?

Comparison of data in two different columns/variables and perform some action.

When you need to use any If-else block

Note: Formula column should return some or the other value.

Counts the number of records within the reset group.

Calculates the columns highest value within the reset group.

Calculates the columns lowest value within the reset group.

Place Holder column

Should it be Inside or outside data group?

You might also like