Home » Developer & Programmer » Reports & Discoverer » HIDE THE SUMMARY COLUMN (oracle 10g DB,Reports builder 6i)
HIDE THE SUMMARY COLUMN [message #432453] |
Tue, 24 November 2009 05:45 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
Hello all,
In the report am getting the 2 summary columns i mean the column summary.
I wants to hide either of the summary in reports .the attached picture will give you the exact way of how it works.and here with am attaching the code..
Since i had a need of summary column should be only once.
but its coming for region wise total and gross total for the cell fields,.Both have the same output .So i have to show only once.
Here is the image
![/forum/fa/7066/0/](/forum/fa/7066/0/)
Below is my query,Am using Rollup..
select *
from (SELECT sdep.name Depot,DECODE (pf.class_code,
'AB', '200ML',
'AC', '500 ML',
'AA', '1 LITER',
'PA', 'T.PASTE') description
,sreg.name RegionTotal,
SUM (DECODE (GREATEST (sls.docdt, '01-jan-'||to_char(add_months(:pdate,-12),'rrrr')),
LEAST (sls.docdt, LAST_DAY (ADD_MONTHS (:pdate, -12))),decode(slsd.tc,
1,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) ,
2,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) * -1) )) gross2008,
SUM (DECODE (GREATEST (sls.docdt, '01-jan-'||to_char(:pdate,'rrrr')),
LEAST (sls.docdt, LAST_DAY (:pdate)),decode(slsd.tc,
1,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) ,
2,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) * -1) )) gross2009
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno =slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
and sls.depot =sdep.depot
AND slsd.docdt BETWEEN '01-jan-'||to_char(add_months(:pdate,-12),'rrrr')
AND LAST_DAY (:pdate)
AND slsd.prodcd =pf.prodcd
--AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.rgncode =sreg.RGNCODE
and sdep.depot not in (42,82,83,85,99)
AND c.custype = :pno
GROUP BY rollup(
DECODE (pf.class_code,
'AB', '200ML',
'AC', '500 ML',
'AA', '1 LITER',
'PA', 'T.PASTE'),sreg.name,sdep.name))
where description is not null and (depot is not null
or (Depot is null and RegionTotal is null))
union
select * from
(
SELECT sdep.name depname,'Total UHT'
,sreg.name regname,
SUM (DECODE (GREATEST (sls.docdt, '01-jan-'||to_char(add_months(:pdate,-12),'rrrr')),
LEAST (sls.docdt, LAST_DAY (ADD_MONTHS (:pdate, -12))),decode(slsd.tc,
1,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) ,
2,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) * -1) )) gross2008,
SUM (DECODE (GREATEST (sls.docdt, '01-jan-'||to_char(:pdate,'rrrr')),
LEAST (sls.docdt, LAST_DAY (:pdate)),decode(slsd.tc,
1,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) ,
2,(nvl(slsd.amt1,0) + nvl(slsd.qty_dval,0)) * -1) )) gross2009
FROM sls_head sls ,
sls_detail slsd ,
prodfle pf ,
prodref pref ,
prodref_classification prefc,
customer c ,
ctypes ct,
sdepot sdep,
sregion sreg
WHERE sls.docno =slsd.docno
AND sls.depot =slsd.depot
AND sls.docdt =slsd.docdt
AND sls.tc =slsd.tc
and sls.depot =sdep.depot
AND slsd.docdt BETWEEN '01-jan-'||to_char(add_months(:pdate,-12),'rrrr')
AND LAST_DAY (:pdate)
AND slsd.prodcd =pf.prodcd
AND pf.refcode =pref.refcode
AND pf.class_code =prefc.class_code
AND c.depot =sls.depot
AND c.cusno =sls.cusno
AND c.custype =ct.code
and sdep.rgncode =sreg.RGNCODE
and sdep.depot not in (42,82,83,85,99)
and pf.refcode ='A'
AND c.custype = :pno
GROUP BY rollup(sdep.name
,sreg.name)
)
where (regname is not null) or (regname is null and depname is null)
|
|
|
|
|
|
Re: HIDE THE SUMMARY COLUMN [message #432478 is a reply to message #432453] |
Tue, 24 November 2009 07:29 ![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) |
|
similarly if i select the total of group its automatically selecting the group_total fields ..
Since my query has designed with rollup..
![/forum/fa/7070/0/](/forum/fa/7070/0/)
i cant do formatting well here..Any ideas please share..
|
|
|
Re: HIDE THE SUMMARY COLUMN [message #432489 is a reply to message #432453] |
Tue, 24 November 2009 08:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I think you need to change the select statement to not select the duplicate data in the first place.
But since I don't know anything about the underlying table structure or how the data is related I couldn't begin to suggest how you do this.
|
|
|
Goto Forum:
Current Time: Sun Sep 08 19:29:30 CDT 2024
|