Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Monday, 18 April 2016

Formula columns in oracle reports

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.

Ø Performs a user-defined computation
Ø Executes a PL/SQL function
Ø Must return a value
Ø Can be a Character, Number, Date
Ø Returned value must match datatype

Let us make total salary(sal + comm)


In the Data Model view that displays, click the formula column tool in the tool palette then click inside of group and view to display the formula column as shown below


Double click on formula column you can find the properties and give below details Compile the code and close as below

Name                 : CF_TOTAL_SAL
PL/SQL Formula :
FUNCTION cf_total_salformula
   RETURN NUMBER
IS
BEGIN
   RETURN (:sal + NVL (:comm, 0));
END;


Go to Layout Model add Text main frame level and field in repeating frame as shown below


Select F_7 field and right click go to Property Inspector set below properties as shown below

            Name  :CF_TOTAL_SAL
            Source :CF_TOTAL_SAL


Now development part completed you can run the report

Goto >Programs > Run paper Layout (you can find as below report output)



Share this article :

2 comments:

  1. Hi Dayakar,


    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Formula columns in oracle reports

    I need your help please, I am new at PL/SQL

    I need to run a select command with multiple values, if a break down this query into 3 pieces and run them,they works perfectly, but Ive got problems whenever i execute this query because there are multiple values into the where clause and i think its necessary to create lines break so it may work perfectly

    how can I get through this?

    SELECT job_name, command,description,days_of_week,start_mins,machine, J2.CREATE_STAMP, J2.MODIFY_STAMP
    FROM MDBADMIN.UJO_JOBROW JR JOIN MDBADMIN.ujo_job2 J2
    ON (JR.JOID = J2.JOID)
    WHERE job_name IN ('coscl_PM_zip_log','coscl_PM_clear_log','coscl_PM_alarma_fs','coscl_PM_pmapp1_PM_up','coscl_PM_control_recargas_cod_11','coscl_PM_control_recargas_ingreso','coscl_PM_control_rec_error_cc_agrup_v1'.'coscl_PM_control_procesos_diarios','coscl_PM_rep_recar_x_estado','coscl_PM_control_tiempos_recargas',
    'coscl_PM_control_porcentaje','coscl_PM_control_puertos_bloq','coscl_PM_control_errores', 'coscl_PM_control_java','coscl_PM_control_error_cache');

    Thank you very much and will look for more postings from you.

    ,Merci
    Irene Hynes

    ReplyDelete
  2. Hi There,

    I’ve often thought about this Formula columns in oracle reports . Nice to have it laid out so clearly. Great eye opener.

    I'm trying to set up a reminder. It needs to remind users of a reassessment date that occurs every 90 days based upon the first contact (enrollment_date) with a potential client. But I only need to show the closest upcoming date. So, if a client's next closest assessment date is 12-12-15 it would show that date until 12-13-15 when it would be beginning to show 3-11-16. Is that possible?

    Appreciate your effort for making such useful blogs and helping the community.

    Obrigado,
    Preethi.

    ReplyDelete