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.

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:**

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

·
Using some standard oracle Pl/SQL
functions to find out some values.

·
When you need to use any If- else
block.

·
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 datatype 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 datatype 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: Average,
Count, Maximum, Minimum, % of Total, Std. Deviation, Sum, and Variance.

**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.

We can use the place holder columns inside the data group or
outside the data group. It can be of Character, Date or Number datatype. Place
Holder columns are generally preceded by CP_ to distinguish from
other columns.

## 0 comments:

## Post a Comment