Oracle Reports: Bind Vs Lexical, Summary, PlaceHolder, Formula Columns

Bind Vs Lexicals, Summary, PlaceHolder, Formula Columns

Bind-Lexical Parameters in Report:
-----------------------------
bind parameters: input dept number at runtime: bind parameters. prefix with colon :
lexical parameters: input conditions at run time., prefixed with (ampersand) &

summary columns and formula columns in Report:
----------------------------------------------
Each of below can be used two ways: inside a group, or outside a group
                                Inside the group: repeated for each row
                                Outside Group: Once for report

Summary Columns: [CS_] are used for calculating aggregates: sum, min, max [these are predefined]
Formula Columns: [CF_] If you want to sum salary and commision , or any calculated value based on formula:
PlaceHolder Column: [CP_]: Placeholder Columns acts like a variable. 



Ex: find the name of the employee earning the maximum salary. 
---------------------------------------------------------------
Add a summary col outside group: Maximum salary can be stored in CS_MaxSal (Summary col),
Add a placeholder column outside group to hold employee name. CP_1
Add a formula column inside Group to find while query runs for each record: CF_1

CODE in Formula Column:

IF :SALARY = :CS_MaxSal THEN
    :CP_1 := :EMPLOYEE_NAME
END IF;
RETURN (:CP_1)   



Ex2: find the Total Salary , where salary > 10000. Only salary > 10000 we should add
-----------------------------------------------------------------------------------
Add a placeholder column outside group to hold Total Salary. CP_1
Add a formula column inside Group to find while query runs for each record: CF_1

CODE in Formula Column:

IF :SALARY > 10000 THEN
    :CP_1 := NVL(:CP_1,0) + :SALARY
END IF;
RETURN (:CP_1)