Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 4 June 2013

Differentiate the use of Formula/Summary/Place holder column in Report

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


Post a Comment