Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Wednesday, April 17, 2013

Summary column vs. Formula Column vs. Placeholder Column - Oracle Reports


We use these columns in our oracle reports, but always have few doubts about which column to use for what purposes. Here are few brief differences among these columns and the purpose of their use in oracle reports.

Summary column:

It summarizes another column and can recalculate for each record in a specified group. The following properties apply specifically to summary columns:
  • Function: The calculation to be performed on the values of the column specified in Source.
  • Source: The name of the column whose values are to be summarized.
  • Reset At: The group at which the summary column value resets to zero.
  • Compute At: The group for which a % of Total summary column is computed.
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. The Report Wizard does not support page summaries. If you select a page summary in the Field tab of the Report Wizard, an error message appears.

Formula Column:

A formula column performs a user-defined computation on the data of one or more other columns. A formula column executes a PL/SQL function and must return a value. The value can be Character, Number, or Date and returned value must match data type.

Placeholder Column:

A placeholder column is an empty container at design time. The placeholder can hold a value at run time that has been calculated and placed into it by PL/SQL code from another object.Using placeholder columns, you can:
  • Populate multiple columns from one piece of code. You can calculate several values in one block of PL/SQL code in a formula column and assign each value to a different placeholder column. Thus, you create and maintain only one program unit instead of many.
  • Store a temporary value for future reference. For example, store the current maximum salary as records are retrieved.

A Scenario:

The goal is to design a salary report of all employees. The aim of the report is to:
  • Calculate and temporarily store the name of the employee who earns the highest salary in the company.
  • Display the highest earner and the maximum salary once at the beginning of the report.
For this report, you need to create the following columns:
  • A summary to show the maximum salary for the company.
  • A placeholder to contain the highest earner’s name at run time.
  • A formula to:
    • Compare each employee salary with the maximum salary.
    • Populate the placeholder with the employee name if salary equals maximum salary.

No comments:

Post a Comment