Home » Developer & Programmer » Reports & Discoverer » dense_rank() in formula column (6i, oracle 10g)
dense_rank() in formula column [message #690198] Fri, 29 November 2024 04:47 Go to next message
shahzad-ul-hasan
Messages: 639
Registered: August 2002
Senior Member
i have report with percentage. i want to calculate dense_rank on formula column . My formula column value is

function CF_7Formula return Number is   --calculate percentage.
begin
  return((nvl(:sumpresent,0)/nvl(:sumwday,0))*100);
end;

Data:
stuid     Name        Percentage     Rank
1         ABC         87.50          want to calculate????
2         FAC         84.20          want to calculate????
3         DAC         85.78          want to calculate????
4         2AC         93.10          want to calculate????
5         HBC         94.58          want to calculate????
6         JA2         79.45          want to calculate????

want to use dense_rank on percentage.

Re: dense_rank() in formula column [message #690201 is a reply to message #690198] Fri, 29 November 2024 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

What about Oracle DENSE_RANK built-in function?

Re: dense_rank() in formula column [message #690202 is a reply to message #690201] Fri, 29 November 2024 14:58 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 639
Registered: August 2002
Senior Member
But how I can use this dense_rank in non database formula column.
Re: dense_rank() in formula column [message #690205 is a reply to message #690202] Mon, 02 December 2024 06:21 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 639
Registered: August 2002
Senior Member
i want to use dense rank in formula column in reports.
Re: dense_rank() in formula column [message #690206 is a reply to message #690205] Mon, 02 December 2024 08:43 Go to previous messageGo to next message
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can't use analytic functions in formula columns, just like you can't use aggregate functions there. You can't because the analytic needs to work across multiple rows and formula columns can only work on one row at a time. To use analytics in reports you would put the analytic in the main report query.
Re: dense_rank() in formula column [message #690215 is a reply to message #690206] Mon, 09 December 2024 10:07 Go to previous message
shahzad-ul-hasan
Messages: 639
Registered: August 2002
Senior Member
please see the attached file.
/forum/fa/14833/0/

Query-1

SELECT  sno,to_char(mn,'MONTH-YYYY')  mm,stmn.class|| '  ' || stmn.section clss,
        ' '||stmn.name name,
        stmn.stuid,
        count(astuid) Absents
       ,substr(last_day(mn),1,2) Totaldays,studentid,wdays,DA
    from (select student.class, student.section,
              ' '||student.name name,
             student.stuid,studentid, da,mn 
from  (select trunc((last_day(dt) - trunc(dt,'iw') + 1) / 7) * 6 +
          least(mod(last_day(dt) - trunc(dt,'iw') + 1,7),6) -
least(dt - trunc(dt,'iw'),6)  da,add_months(TRUNC(to_Date(to_char(:yea,'DD-MON-YYYY'), 'DD/MM/YYYY')), rownum-1 ) mn
 from  (SELECT add_months(to_date(TO_CHAR(:yea,'DD-MON-YY'),'DD/MM/YYYY'),level - 1) dt
                 FROM dual CONNECT BY LEVEL < 13)),STUDENT
      where status='PRESENT') stmn,absent1,months
WHERE absent1.astuid(+)=stmn.stuid
and trunc(absent1.abdate(+), 'MON')=stmn.mn
and class=:cls
and section=:sec
and absent1.abdate(+)>=:yea
and to_char(mon,'MON')=to_char(mn,'MON')
and mon>=:yea
group by
sno,stmn.CLASS|| '  ' ||
stmn.SECTION, stmn.NAME, substr(last_day(mn),1,2) ,stmn.stuid,to_char(mn,'MONTH-YYYY'),to_char(mn,'MM'),studentid,wdays,da
order by sno,to_char(mn,'MM')

Query-2

SELECT t.*, rownum FROM
(select pc,dense_rank() over (partition by stuid order by pc desc nulls last) as pos,stuid,class,section
from (select round((nvl(:sumpresent,0)/nvl(:sumwday,0))*100) As pc,stuid,class,section
       from student,absent1
       where student.stuid=absent1.astuid
      and student.stuid=:stuid
       and student.status='PRESENT'
       group by stuid,class,section)) t

Rep: 1302   cross product has two or more child group.

[Updated on: Mon, 09 December 2024 10:08]

Report message to a moderator

Previous Topic: Oracle Reports
Goto Forum:
  


Current Time: Thu Dec 12 21:38:21 CST 2024