Home » Developer & Programmer » Reports & Discoverer » Get unique values with formula column
Get unique values with formula column [message #589387] Fri, 05 July 2013 03:00 Go to next message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

I am facing a problem in leave_form report!

want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP'

repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'

problem is report is showing 22 records of leavetype 'CL' of same leavedate!

i want that report should show the actual leavedate and leavetype records.

using 9i database 6i developer server2003

For leavetype

FUNCTION Cf_1formula 
RETURN CHAR 
IS 
  v_leavetype CHAR(40); 
BEGIN 
    SELECT LT.description 
    INTO   v_leavetype 
    FROM   hrm_attendance L, 
           hrm_leavetype LT, 
           hrm_employees E 
    WHERE  E.employeecode = L.employeecode 
           AND L.employeecode = :EMPLOYEECODE 
           AND L.leavetype = LT.leavetype 
           AND L.leavetype NOT IN ( 'PPP' ); 
 
    RETURN v_leavetype; 
EXCEPTION 
  WHEN OTHERS THEN 
             RETURN NULL; 
END;


For Leavedate
FUNCTION Cf_2formula 
RETURN DATE 
IS 
  v_leavedate DATE; 
BEGIN 
    SELECT L.leavedate 
    INTO   v_leavedate 
    FROM   hrm_attendance L, 
           hrm_leavetype LT, 
           hrm_employees E 
    WHERE  E.employeecode = L.employeecode 
           AND L.employeecode = :EMPLOYEECODE 
           AND L.leavetype = LT.leavetype 
           AND L.leavetype NOT IN ( 'PPP' ); 
 
    RETURN v_leavedate; 
EXCEPTION 
  WHEN OTHERS THEN 
             RETURN NULL; 
END;

/forum/fa/10955/0/
  • Attachment: report1.JPG
    (Size: 84.15KB, Downloaded 1936 times)
Re: Get unique values with formula column [message #589404 is a reply to message #589387] Fri, 05 July 2013 05:03 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Formula columns belong to a repeating frame.
Repeating frame's source is some query.
That query returns 22 (or so) records.
That's why you see the same number of records in the output.

Do you really need formula columns? Can't you compose that information within the report's query itself?
Re: Get unique values with formula column [message #589446 is a reply to message #589404] Fri, 05 July 2013 23:38 Go to previous message
rana-mudassar
Messages: 15
Registered: May 2013
Location: Pakistan
Junior Member

SELECT e.employeecode,
       e.name,
       e.fathersname,
       des.designation,
       dep.department,
       r.cl,
       r.sl,
       Nvl(r.el, '0')                              "EL",
       r.cpl,
       r.leavedate                                 "FROM",
       r.todate,
       r.days,
       l.leavedate,
       Nvl(Initcap(lt.description), 'No Leave Yet')"LEAVETYPE"
FROM   hrm_employees e,
       hrm_leavereg r,
       hrm_attendance l,
       hrm_designation des,
       hrm_department dep,
       hrm_leavetype lt
WHERE  e.employeecode = r.employeecode
       AND e.employeecode = l.employeecode
       AND e.designationcode = des.designationcode
       AND e.departmentcode = dep.departmentcode
       AND l.leavetype = lt.leavetype(+)
       AND lt.leavetype NOT IN ( 'PPP' )
ORDER  BY l.leavedate;  


when i use it in here it shows only when leave is available other wise show blank?
Previous Topic: Count Records with a not null column
Next Topic: How to make a query in data model
Goto Forum:
  


Current Time: Tue Mar 19 01:03:23 CDT 2024