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
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
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/

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)

Report message to a moderator

Re: HIDE THE SUMMARY COLUMN [message #432457 is a reply to message #432453] Tue, 24 November 2009 05:56 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Use conditional formating.... Smile Or just remove that field...

Why cant you show that with a sample data...

Sriram Smile

Report message to a moderator

Re: HIDE THE SUMMARY COLUMN [message #432468 is a reply to message #432453] Tue, 24 November 2009 06:18 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you have to hide one of these (red or yellow) fields, either remove that particular (let's say yellow) field from the report (delete it in Paper Layout Editor) or - if you plan to use it some time later - just hide it: open its property palette window, open Format Trigger and put RETURN (FALSE) into it.

Report message to a moderator

Re: HIDE THE SUMMARY COLUMN [message #432475 is a reply to message #432468] Tue, 24 November 2009 07:25 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
Thanks for the response..

If i select the first column summary its selecting all the gross per description.



I cant do conditional formatting in that case.if i does everywhere it will be hide./forum/fa/7069/0/

[Updated on: Tue, 24 November 2009 07:27]

Report message to a moderator

Re: HIDE THE SUMMARY COLUMN [message #432478 is a reply to message #432453] Tue, 24 November 2009 07:29 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
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/

i cant do formatting well here..Any ideas please share..

Report message to a moderator

Re: HIDE THE SUMMARY COLUMN [message #432489 is a reply to message #432453] Tue, 24 November 2009 08:18 Go to previous message
cookiemonster
Messages: 13952
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.

Report message to a moderator

Previous Topic: Reference matrix cell fields
Next Topic: Problem connecting with sys schema in Report Builder 6i
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 09 22:30:29 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.