OraFAQ Forum: Reports & Discoverer » If possible refernce matrix cell in report builder 6i

Home » Developer & Programmer » Reports & Discoverer » If possible refernce matrix cell in report builder 6i (oracle 10g DB,Reports builder 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

If possible refernce matrix cell in report builder 6i [message #434987]

Sun, 13 December 2009 00:47

seyed456
Messages: 220
Registered: May 2008
Location: south india .

Senior Member

Am using matrix report in 6i report builder,

In that i needs to reference a matrix cell values.
certainly my columns are dynamic ,for ex if i have given
date as 03072009(ddmmyyyy) i have to get the columns like

jan08 jan09 feb08 feb09 mar08 mar09 apr08 apr09 may08 may09 jun08 jun09 jul08 jul09

the above shown are headings for the columns.
i have got the figures correct with use of the following query.
What i trying to do is i can combinly use a formula column and conditional formatting if i reference a matrix cell field value that is sales08 sales09

SELECT   decode(pc.refcode,'K','IC','M','IC',pc.refcode) code,
         TO_CHAR (fgd.docdt, 'MON') MONTH,
             sum(CASE
                 WHEN fgd.tc = 2 and TO_CHAR (fgd.docdt, 'YY') = '08'
                    THEN fgd.amount1 + fgd.amount2 + fgd.amount3
                 WHEN fgd.tc = 3 and TO_CHAR (fgd.docdt, 'YY') = '08'
                    THEN fgd.amount1 + fgd.amount2 + fgd.amount3
                 WHEN fgd.tc = 4 and TO_CHAR (fgd.docdt, 'YY') = '08'
                    THEN -fgd.amount1 - fgd.amount2 - fgd.amount3
                 ELSE 0
              END
             ) sales08,
             sum(CASE
                 WHEN fgd.tc = 2 and TO_CHAR (fgd.docdt, 'YY') = '09'
                    THEN fgd.amount1 + fgd.amount2 + fgd.amount3
                 WHEN fgd.tc = 3 and TO_CHAR (fgd.docdt, 'YY') = '09'
                    THEN fgd.amount1 + fgd.amount2 + fgd.amount3
                 WHEN fgd.tc = 4 and TO_CHAR (fgd.docdt, 'YY') = '09'
                    THEN -fgd.amount1 - fgd.amount2 - fgd.amount3
                 ELSE 0
              END
             ) sales09
 
    FROM  fg_detail fgd INNER JOIN  sdepot d ON d.depot =fgd.depot
         INNER JOIN  prodfle p ON p.prodcd = fgd.prodcd
         INNER JOIN  prodref_classification pc
         ON p.class_code = pc.class_code
         INNER JOIN  prodref pr ON pc.refcode = pr.refcode
   WHERE           
          ((fgd.docdt between '01-jan-'||to_char(add_months(:pdate,-12),'rrrr')
           and LAST_DAY (ADD_MONTHS (:pdate, -12)) )
           or 
          (fgd.docdt between '01-jan-'||to_char(:pdate,'rrrr') and 
          LAST_DAY (:pdate)))
     --AND d.rgncode = 30
     and fgd.depot = 43
     AND (   pc.refcode = 'K'
          OR pc.refcode = 'M'
          OR pc.refcode = 'D'
          OR pc.refcode = 'Q'
          OR pc.refcode = 'B'
          )
GROUP BY decode(pc.refcode,'K','IC','M','IC',pc.refcode),
         TO_CHAR (fgd.docdt, 'MON')

Report message to a moderator

Re: If possible refernce matrix cell in report builder 6i [message #434988 is a reply to message #434987]

Sun, 13 December 2009 00:49

seyed456
Messages: 220
Registered: May 2008
Location: south india .

Senior Member

Any help would be appreciated. .
Thanks

Report message to a moderator

Re: If possible refernce matrix cell in report builder 6i [message #435071 is a reply to message #434988]

Mon, 14 December 2009 03:05

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

For me its not clear that what exactly your requirement?!

So let me know.... is this the below your requirement.

1) you have one matrix report.
2) You are getting correct values from the code whatever you have.
3) The headings of your report are sales08 and sales09.
4) Now you dont want them as sales08.That should be like jan08jan09feb08feb09....

Is this correct?if not

Let me know if that is your requirement.And if it possible provide the test case with ddl/dml or any other sample data example(scott.emp or scott.dept etc)

sriram Smile

Report message to a moderator

Previous Topic:	Is it possible to Save Oracle Reports Output as PDF
Next Topic:	how to overcome this error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 06:36:09 CST 2024