Monthwise break up not show in order [message #401455] |
Tue, 05 May 2009 01:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gozuhair
Messages: 206 Registered: January 2008
|
Senior Member |
|
|
Dear All
Please solve my problem,I have created Group Matrix report that contain Matrix group field =Prod_nm (Product),Matrix Row Field =Gr_desc (Defects),Matrix Column field=Dt (Month) nad Matrix Cell field =TOT.
The problem is that when i execute the query in sql then it will show the result on monthly basis like Jan,Feb,Mar,Apr ,May and so on but when i try to use it in my report then month sequence is out of order (see attached photo for details).when i observe the result then i found then all months that have 0 value will appear in out of order,kindly suggest how can i get rid of this problem.
I am using the followoing query.
select * from
(
Select b.gr_sub_cd,
c.gr_desc,d.prod_nm,
to_char(a.ro_clo_dt,'MMRRRR') dt,
count(chasis_no) Tot,
b.prod_cd,
row_number() over
(partition by to_char(a.ro_clo_dt,'MMRRRR')
order by to_char(a.ro_clo_dt,'MMRRRR' ) desc) aa
from ro.ro_mst a,
sv_pir b,
sv_pirgroupdtl c,
sl_prod d
where a.ro_clo_dt=b.dt_entry(+)
and b.GR_CD =c.GR_CD(+)
and b.prod_cd=d.prod_Cd(+)
and b.dt_entry(+) between '01JAN08' AND '31DEC08'
and a.ro_clo_dt between '01JAN08' AND '31DEC08'
AND B.PROD_CD(+)='12'
group by to_char(a.ro_clo_dt,'MMRRRR'),
b.prod_cd,
b.gr_sub_cd,
c.gr_desc,
d.prod_nm,
to_char(a.ro_clo_dt,'MMRRRR'))
where aa=1
-
Attachment: mat_rep.JPG
(Size: 72.37KB, Downloaded 860 times)
|
|
|
|
|
Re: Monthwise break up not show in order [message #401711 is a reply to message #401697] |
Wed, 06 May 2009 04:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
gozuhair
Messages: 206 Registered: January 2008
|
Senior Member |
|
|
I have tried but its also not working,if you open my attached file then you can find that all months that have 0 values are appear in the bottom of the report and on the top of the report all months sequence is working fine.
|
|
|
Re: Monthwise break up not show in order [message #401764 is a reply to message #401455] |
Wed, 06 May 2009 06:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
gozuhair
Messages: 206 Registered: January 2008
|
Senior Member |
|
|
After a working long time on a report i have found that Mtrix report is displaying 2 information on the top that contain value against product and on the botton that contain no value.
actually i have extract dates from one of my dummy table that contains all dates but no product exist in this dummy table so that it is showing like that.
|
|
|