Home » Developer & Programmer » Reports & Discoverer » Monthwise break up not show in order (Report,6i,Xp)
Monthwise break up not show in order [message #401455] Tue, 05 May 2009 01:53 Go to next message
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 127 times)
Re: Monthwise break up not show in order [message #401665 is a reply to message #401455] Wed, 06 May 2009 01:06 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
waiting for your favourable response.
Re: Monthwise break up not show in order [message #401697 is a reply to message #401455] Wed, 06 May 2009 03:27 Go to previous messageGo to next message
Littlefoot
Messages: 20892
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
month sequence is out of order

Did you try to use ORDER BY clause? Without it, there's absolutely no guarantee that the result will be sorted in a consistent manner (i.e output sort might change with every SELECT execution).
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 messageGo to next message
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
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.
Previous Topic: discoverer administrator
Next Topic: Reports Generation I/O Error
Goto Forum:
  


Current Time: Mon Dec 05 02:52:46 CST 2016

Total time taken to generate the page: 0.04383 seconds