Apart from columns which are selected from the database, there are other types of columns which can be created within the report itself.
They are SUMMARY columns, FORMULA columns, and PLACEHOLDER columns.
In brief: A SUMMARY column allows you to perform aggregate functions such as SUM, AVG, COUNT, MAX, MIN etc..
A FORMULA column allows you to perform any pl/sql function that will return a value of type NUMBER, VARCHAR2, DATE or BOOLEAN. However columns referenced in the formula must be in the same group or at a higher level, due to frequency constraints
A PLACEHOLDER column is merely a container, or a “place” to “hold” a value. A formula column is used to populate a placeholder column.
Where do these columns go? In which group do they belong? The answers are simple
If you want to get the total number of employees in a department, the sum must reset at department level,
so the SUMMARY column must be created in the department group. If you want to calculate the commission in dollars for each employee, that is salary times commission percentage,
the FORMULA column must be created in the same group as the salary and commission percentage columns, that is the employee group.
If you want to create a PLACEHOLDER column, or container into which values are assigned, place it in the same group as the formula column which is performing the assignment, or place it at a higher level.
When creating these types of columns, always use the default naming convention. This can save precious hours later if and when the report needs to be modified. For example: Summary column names are prefixed by CS_ Formula column names are prefixed by CF_ Placeholder columns names are prefixed by CP_
If possible try to calculate summary functions and formulae directly in the database, this will be more efficient compared to querying all the records from the server to the client and performing the calculations on the client side.
