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)
You May Like:
Hi Dayakar,
ReplyDeleteZoooooooom! 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
Hi There,
ReplyDeleteI’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.