Home » Developer & Programmer » Reports & Discoverer » Reference matrix cell fields (oracle 10g DB,Reports builder 6i)
Reference matrix cell fields [message #432106] Sat, 21 November 2009 05:04 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
Hello all,

Is there anyway i can reference the matrix cell fields in the matrix report which am making in report builder ?..

Thanks in advance
Syed.

Report message to a moderator

Re: Reference matrix cell fields [message #432115 is a reply to message #432106] Sat, 21 November 2009 05:44 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
seyed456 wrote on Sat, 21 November 2009 05:04
Hello all,

Is there anyway i can reference the matrix cell fields in the matrix report which am making in report builder ?..

Thanks in advance
Syed.


Can make it clear.... for what...Is this for the previous thred summary column?


sriram

Report message to a moderator

Re: Reference matrix cell fields [message #432117 is a reply to message #432115] Sat, 21 November 2009 05:59 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
you could also say that ..See am attaching an image ../forum/fa/7053/0/


Either i have to write a plsql code in editor or create a summary column .Since i couldnot able to find a solutiion.I just think is there any way to reference the matrix cells and then i can write a formula of cell1+cell2+cell3 +etc...

Report message to a moderator

Re: Reference matrix cell fields [message #432118 is a reply to message #432117] Sat, 21 November 2009 06:01 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
or you can also add the unmarked ellipses in the above figure which is the group totals and sum of group total will get the same figure.

Report message to a moderator

Re: Reference matrix cell fields [message #432120 is a reply to message #432118] Sat, 21 November 2009 07:03 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I have sent an rdf to you...Named orafaq_seyed.rdf

check it out....
And let me know...your feedback.

And Please do not multi post with differnt names...

I thought All your previous 4 thread are for one purpose...


right?

Sriram. Smile

Report message to a moderator

Re: Reference matrix cell fields [message #432121 is a reply to message #432120] Sat, 21 November 2009 07:13 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
not all only this and previous. .
And see this is my original code ..

select *
from (SELECT sdep.name  "Depot",DECODE (pf.class_code,
                 'AA', '1.)1 LITER',
                 'AB', '2.)200ML',
                 'AC', '3.)1/2 LITER',
                 'PA', '4.)T.PASTE') description
                 ,sreg.name "Region Total",
                 SUM (DECODE (GREATEST (sls.docdt,'01-jan-'||to_char(add_months(:pdate,-12),'rrrr')),
                      LEAST (sls.docdt, LAST_DAY (ADD_MONTHS (:pdate, -12))),NVL (slsd.amt1,0),0)) gross2008,
                 SUM (DECODE (GREATEST (sls.docdt, '01-jan-'||to_char(:pdate,'rrrr')),
                      LEAST (sls.docdt, LAST_DAY (:pdate)),NVL (slsd.amt1,0),0)) 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 (82,83,85)
 AND c.custype             = '05'
 GROUP BY sdep.name,
 DECODE (pf.class_code,
                 'AA', '1.)1 LITER',
                 'AB', '2.)200ML',
                 'AC', '3.)1/2 LITER',
                 'PA', '4.)T.PASTE'),sreg.name)
                 where description is not null
                 
union

SELECT sdep.name ,'Total UHT'
                   ,sreg.name,
                 SUM (DECODE (GREATEST (sls.docdt,'01-jan-'||to_char(add_months(:pdate,-12),'rrrr')),
                      LEAST (sls.docdt, LAST_DAY (ADD_MONTHS (:pdate, -12))),NVL (slsd.amt1,0),0)) gross2008,
                 SUM (DECODE (GREATEST (sls.docdt, '01-jan-'||to_char(:pdate,'rrrr')),
                      LEAST (sls.docdt, LAST_DAY (:pdate)),NVL (slsd.amt1,0),0)) 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 (82,83,85)
 and pf.refcode ='A'
 AND c.custype             = '05'
 GROUP BY sdep.name
,sreg.name


Am using the matrix with group since you using the matrix in the rdf which you sent to me. .


can you modify in this.since i post whats for matrix rows columns and groups ..Am very pleased to ask you..Thanks..

Report message to a moderator

Re: Reference matrix cell fields [message #432122 is a reply to message #432121] Sat, 21 November 2009 07:18 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
seyed456 wrote on Sat, 21 November 2009 07:13
not all only this and previous. .
And see this is my original code ..




No You previous posts like "i want summary column","location and total down" these are all releted to this only right ?

And come to your query...I dont have your tables...
i dont have your data...to do the work for you ....

And see no one in this forum do the home work....This forum has to give hints only not the complete solution to motivate the users(OP) If you still want that mail me with details what i asked just above...


Sriram Smile

[Updated on: Sat, 21 November 2009 07:18]

Report message to a moderator

Re: Reference matrix cell fields [message #432136 is a reply to message #432122] Sat, 21 November 2009 14:03 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member
Ram,
oracle 9i has wm_concat function ?..
Since its saying invalid column name in the wm_concat function.

Report message to a moderator

Re: Reference matrix cell fields [message #432234 is a reply to message #432136] Mon, 23 November 2009 03:03 Go to previous message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
no need to use Wm_concat...there.Without using that function also that is posible read the firest rdf..without any function.

Read your inbox..And repl..
And for you wm_concat not available in 9i.
Sriram Smile

Report message to a moderator

Previous Topic: Need summary column in the attached 10g RDF file.
Next Topic: HIDE THE SUMMARY COLUMN
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 07:35:41 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.