DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Matrix report problem

Hybrid View

  1. #1
    Join Date
    Jun 2003
    Location
    Saudi Arabia
    Posts
    5

    Lightbulb Matrix report problem

    Hi !!

    I am using Oracle reports 6i to make a report (matrix report as far as i get it). I have following fields in the view which is coming from couple of tables and have some calculations to get these fields
    (group by clause include section_code,group_code,machine_code,jobdate,organization_id)

    Name Null? Type
    ----------------------------------------- -------- ---------------
    SECTION_CODE NOT NULL VARCHAR2(5)
    GROUP_CODE NOT NULL VARCHAR2(5)
    MACHINE_CODE NOT NULL VARCHAR2(20)
    JOBDATE NOT NULL DATE
    ORGANIZATION_ID NOT NULL NUMBER(15)
    MATPROC NUMBER
    WASTAGE NUMBER
    WASTPER NUMBER
    REGRIND NUMBER
    COSTKG NUMBER
    COSTPC NUMBER
    OUTMAN NUMBER
    REJECTION NUMBER
    UTILI NUMBER
    EFFICIENCY NUMBER

    Now they want the report in below mentioned format (group by section_code,group_code,machine_code)

    ------------01-Dec 02-Dec 03-Dec.......... 15-Dec-->(Jobdate field)
    Matproc 150 220 170 200
    Wastage 8 9 8 8
    wastper .77 .99 .87 .98
    regrind 1822 2288 2222 5544
    costkg .033 .98 .76 .022
    costpc .044 .77 .88 .83
    outman 8.56 7.56 7.66 4.53
    rejection 0 0 0 0
    utili 12 12 15 14
    effifciency 100 99 99 98


    how i am suppose to do this in Oracle reports ..if anybody ahas any type of suggestion please send it as soon as possible..because its like a show stopper for me

    regards

    MNA
    Last edited by mnakhtar; 12-22-2003 at 12:03 PM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    If jobdate contains million values , would you like to print across them?

    Tamil

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If there are an unknown/variable number of columns, then I would dump the data into Excel and use a Pivot Table. In this case you could "page" by Month.Year and have a reasonable 31 (30, 29, 28) column-wide report for each month.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try this:
    select SECTION_CODE, GROUP_CODE, MACHINE_CODE ,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'15', MATPROC , NULL )) DEC15,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'16', MATPROC , NULL )) DEC16,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'17', MATPROC , NULL )) DEC17,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'18', MATPROC , NULL )) DEC18,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'19', MATPROC , NULL )) DEC19,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'20', MATPROC , NULL )) DEC20,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'21', MATPROC , NULL )) DEC21,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'22', MATPROC , NULL )) DEC22,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'23', MATPROC , NULL )) DEC23,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'24', MATPROC , NULL )) DEC24,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'25', MATPROC , NULL )) DEC25,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'26', MATPROC , NULL )) DEC26,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'27', MATPROC , NULL )) DEC27,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'28', MATPROC , NULL )) DEC28,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'29', MATPROC , NULL )) DEC29,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'30', MATPROC , NULL )) DEC30,
    SUM( decode( TO_CHAR(JOBDATE,'DD'),'31', MATPROC , NULL )) DEC31
    from t10 WHERE TO_CHAR(JOBDATE, 'YYYYMM') = '200312'
    group by SECTION_CODE, GROUP_CODE, MACHINE_CODE

    You get the idea how cross tab report works.

    Then modify it according to your need .

    Tamil

  5. #5
    Join Date
    Jun 2003
    Location
    Saudi Arabia
    Posts
    5
    Dear Tamilselvan !!

    thanx for ur encouraging reply ... it really give me a move forward to think.. but stilll it didnt solve my problem.. the case is they want to select the date range at report runtime so u cant hardcode the months and dates and secondly .. they want to group by following feilds and date range selected at runtime ... but the thing is these are the column names and in a matrix report we need atleast Three columns to make matrix ...in my case there are only two columns that the date and the below mentioned fields

    MATPROC NUMBER
    WASTAGE NUMBER
    WASTPER NUMBER
    REGRIND NUMBER
    COSTKG NUMBER
    COSTPC NUMBER
    OUTMAN NUMBER
    REJECTION NUMBER
    UTILI NUMBER
    EFFICIENCY NUMBER

    soo plz plz ... send any any comments ...which may help me
    MNA

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I gave a sample code for column MATPROC.

    You can use union for other columns such as WASTAGE, WASTPER....

    There are some restrictions when you use "cross tab report".

    What will happen if the user gives a date range of 365 days?
    Are you going to print across all the values in one line?

    You can try CUBE / ROLLUP analytic functions.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width