Formula column,Summary column,Place Holder column

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

Different Columns contained in Report Builder are:

1)      Data Columns

2)      Formula Columns

3)      Summary Columns

4)      Placeholder Columns

Data Columns:

Data Columns contain the data values for a report.

Data Columns are corresponding to the table columns included in the select list of a query.

Each column is placed in the group associated with the query.

Formula Columns:

Formula Columns performs user-defined computations.

Formula Columns executes a PL/SQL function and must return a value.

Formula Columns can be placed at the Report or Query level.
Formula Columns should not be used to set a parameter’s value.

Formula Columns is denoted by CF_

What is a Formula Column

We apply a formula or piece of code to calculate a value. In Reports we calculate a value for every row by considering an input from that row. So we need to apply a particular logic for every row of the data in the report. In other cases we want to calculate a value at report level. So we need to write a pl/sql code in the block format. So we have an option of writing a function to achieve so. It's called formaula column Basically we apply a custom logic and return a value. 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 outide to the data group. We have to prefix ':' to the column name which we consider as an input to the formula column.

Let us understand with the example

Let us consider the followig insurance information. We need to check the total installments payed and check the difference between insured months and installments payed. If the difference is 0 then display the customer is eligible to claim the total Insurance with 40% interest on the total amount and display the total Insurance amount will be claimed.

Customer_Name Insured_amount Insured_months Insurance_frequency Installments_payed Surendra 100000 100 Monthly 100 Nath 200000 100 Monthly 90 Reddy 300000 100 Monthly 80 Tirumala 400000 100 Monthly 70 Venkata 500000 100 Monthly 60

So we write a query to fetch the Insurer name, and if the cutomer is eligible to claim the entire amount diplay that 'Eligible'  and total amount can be claimed. SELECT * FROM insurance_details. We select the formula column from the data model and palce  it in the same group as the above query returns the columns. And write the below code to achieve the desired result.

function CF_ELIGIBLEFormula return Number is l_amount number; begin IF (:Insured_months - :Installments_payed) = 0 THEN l_amount := :Insured_amount*1.4; ELSE l_amount := 0; END IF; return(l_amount); end;

The aboove formula calculates and returns the value with 40% more else 0. We may have to control layout to display the total claim amount only when it is greater than 0. So the result would like below

Customer_Name Eligible? Total_Amount_claim Surendra Eligible 140000 Nath Reddy Tirumala Venkata

The above can be achieved with the help of a select statement also, but we wanted to consider the simple concept to implement Formula columns

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

Function	Purpose
Average	Calculates the average of the column’s values within the reset group.
Count	Counts the number of records within the reset group.
First	Prints the column’s first value fetched for the reset group.
Last	Prints the column’s last value fetched for the reset group.
Maximum	Calculates the column’s highest value within the reset group.
Minimum	Calculates the column’s lowest value within the reset group.
% of Total	Calculates the column’s percent of the total within the reset group.
Std. Deviation	Calculates the column’s positive square root of the variance for the reset group.
Sum	Calculates the total of the column’s values within the reset group.
Variance	Sums the squares of each column value’s 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 datatype column and return number datatype.

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.
Inside or outside data group?
We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.

Example of Usage of PlaceHolder Column

function CF_Cal_MaxPaymentFormula return Number is
begin
  if :Payments=:CS_Max_Pay then
  :CP_MAX_Payer:= :vendor_name;
  end if;
  return (1);
end;

--take CF_CalMaxPaymentFormul as group Level in group
--CP_MAX_Payer is a report level out side the group placeholder column with data type Character 80
--CS_Max_Pay is a report level Summary Column having maxiumn Payments using max function

FUNCTION dept_name IS 
  RETURN VARCHAR2 ( 50 )
BEGIN
  DECLARE
    t_dname VARCHAR2( 50 );
    t_loc   VARCHAR2( 50 );
    CURSOR c_dept IS
      SELECT dname, loc
        FROM dept
       WHERE deptno = :deptno;
  BEGIN
    OPEN c_dept;
    FETCH c_dept INTO t_dname, t_loc;  
    IF c_dept%FOUND THEN
      CLOSE c_dept;
      :place_loc := t_loc;  -- PLACE HOLDER COLUMN
    ELSE   
      CLOSE c_dept;
    END IF;
    RETURN t_dname;
  END;
END; 

What is a Place Holder Column?

In any programming language we do declare local or global variables to store the values and use the values as per needed. In Oracle report tool we use place holder column for the same purpose. Place Holder column can be of Character, Date or Number datatype. We can use this place holder columns inside the data group or outside the data group. Usually we need the place holder column when we want to derive multiple values in a single program unit and return multiple values. We use a formula column to derive few values for a particular record in the data group and want to return multiple values. Since formula column is based on a function and can return only a single value we use multiple place holder columns to store multiple values so that we can display then on the report.