DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Report layout Requirement

  1. #1
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Report layout Requirement

    I have the following query which generates a report as follows...
    Code:
       select b.employee_name, a.activity_occured_date, sum(a.timespent)
       from d_ts_entries a, u_hris.d_hris_personal b
       where a.fk_employee_id = b.employee_id and to_char(a.activity_occured_date, 'MM') = '04' AND
       TO_CHAR(a.activity_occured_date, 'YYYY') = '2003'
       group by b.employee_name, a.activity_occured_date;
    PROJECT_NAME ON_DATE TOTAL_TIME_SPENT
    ------------------------- --------- ----------------
    CTG 01-APR-03 534.55
    02-APR-03 524.8
    03-APR-03 528.3
    04-APR-03 525.7
    However, the requirement is to display the report as follows...

    PROJECT_NAME 01-APR-03 02-APR-03 03-APR-04
    CTG 534.55 524.8 528.3

    How can this be achieved?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    You want the o/p in report right...u cud implement a logic in front end report itself...

    Or u cud write a proc to handle dynamic result set to be actually displayed as col, rather than row.
    Last edited by abhaysk; 05-05-2003 at 08:18 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    Thanks for the input. I'm currently doing the this using Crystal Reports. However, the drawback is in getting the report in ASCII/text format. Looking at Crystal Reports Distributor for this.

    However, if I could do the same using PL/SQL, I'd be glad.

    Originally posted by abhaysk
    Or u cud write a proc to handle dynamic result set to be actually displayed as row, rather tahn col.
    Can you eloborate on the above with an example, links please?

    Thanks.

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ggnanaraj

    Can you eloborate on the above with an example, links please?
    Consider a simple example where in I need to convert the 5 level of rows to 5 diff cols....

    Code:
    Select p1.ProductId        ,
           p1.ParentProductId  
    from   am_cd_ProductHierarchy p1
    where   p1.ProductLevel = 'S'
    
    O/P
    
    ProductId ParentProductId
    
    11        111              -- p1 ( Logical )
    111       1111             -- p2     "
    1111      11111            -- p3     "
    11111     111111           -- p4     "
    111111    1111111          -- p5     "
    
    
    
    Now if u want in rows....
    
    
    select  p1.ProductId Sku           ,
            p1.ParentProductId Model   ,
            p2.ParentProductId Product ,
            p3.ParentProductId Family  ,
            p4.ParentProductId Line    ,
            p5.ParentProductId TypeCd  
    from    am_cd_ProductHierarchy p1,
            am_cd_ProductHierarchy p2,
            am_cd_ProductHierarchy p3,
            am_cd_ProductHierarchy p4,
            am_cd_ProductHierarchy p5
    where   p1.ProductLevel = 'S'
    and	p1.ParentProductId = p2.ProductId
    and	p2.ParentProductId = p3.ProductId
    and	p3.ParentProductId = p4.ProductId
    and	p4.ParentProductId = p5.ProductId
    
    
    O/P
    
    Sku   Model  Product  Family  Line    Typecd.
    
    11    111    1111     11111   111111  1111111
    Now u see, the rows actually have been converted to cols.....

    But as i see in ur case...u may have to develop dynamic query builder to obtain this result set U dont know which level of ROWS have to converted to Corresponding Cols....

    say if the query
    Code:
    select b.employee_name, a.activity_occured_date, sum(a.timespent)
       from d_ts_entries a, u_hris.d_hris_personal b
       where a.fk_employee_id = b.employee_id and to_char(a.activity_occured_date, 'MM') = '04' AND
       TO_CHAR(a.activity_occured_date, 'YYYY') = '2003'
       group by b.employee_name, a.activity_occured_date;
    might return 4 rows at one point of time then u have to have 4th level of convertion from rows to cols....

    now if it returns 6 rows then 6th level is required and hence dynamic query builder is necessary in ur case as against my example of having fixed 5th level convertion...

    Its not difficult, but at the same time not easy....

    So rather go for some front end tools which will do this for u (Say VB or VBS or so )...and then save it in some text format...pick this result from Ur Crystal Reports rather than picking from DB directly...( Not sure though u can pick result set from text formats via Crystal reports. )

    Regards
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width