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 Go to next message](/forum/theme/orafaq/images/down.png) |
|
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.
|
|
|
|
Re: Reference matrix cell fields [message #432117 is a reply to message #432115] |
Sat, 21 November 2009 05:59 ![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) |
|
you could also say that ..See am attaching an image ..![/forum/fa/7053/0/](/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...
|
|
|
|
|
Re: Reference matrix cell fields [message #432121 is a reply to message #432120] |
Sat, 21 November 2009 07:13 ![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) |
|
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..
|
|
|
Re: Reference matrix cell fields [message #432122 is a reply to message #432121] |
Sat, 21 November 2009 07:18 ![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) |
![](http://www.orafaq.com/forum/images/custom_avatars/136607.jpg) |
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
seyed456 wrote on Sat, 21 November 2009 07:13not 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
[Updated on: Sat, 21 November 2009 07:18] Report message to a moderator
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 08 19:21:59 CDT 2024
|