-
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.
-
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"
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|