Forum Stats

  • 3,768,187 Users
  • 2,252,756 Discussions


Oracle APex calculate profit on rendering

Vinipanda Member Posts: 106 Red Ribbon

I have an interactive grid with fixed rows and need to calculate the formula on pre-rendering.

So the source query is:

select kpi,monthly,yearly from kpi where project_id = :P1_PROJECT_ID;

I need to modify this so that the row where kpi='Gross' is calculated on rendering.

It looks in the grid like:

I am trying to write sql query but it doesn;t work. What am i doing wrong here?

 select kpi,
case when KPI='Gross'
then to_char(case when KPI='Profit' then to_number(replace(nvl(monthly,0),',','')) end /
case when KPI='Loss' then to_number(replace(nvl(monthly,0),',','')) end ,'999,999,999,999') 
else to_char( monthly,'999,999,999,999') end as monthly,
case when KPI='Gross'
then to_char(case when KPI='Profit' then to_number(replace(nvl(yearly,0),',','')) end /
case when KPI='Loss' then to_number(replace(nvl(yearly,0),',','')) end ,'999,999,999,999')  
else to_char( yearly,'999,999,999,999') end as yearly,
from kpi where project_id = :P1_PROJECT_ID;

To_char is used to display values as comma separated. So it would when KPI=Gross, it will divide the columns where kpi=profit by kpi=Loss and dispaly result.

Also the result in the row where KPI=Gros should also have % concatenated.

Apex 20.2

How can this be achieved?