11 Replies Latest reply: Dec 18, 2012 2:48 AM by Maahjoor RSS

    Matrix Report Problem

    Maahjoor
      Dear All,

      i am using oracle developer suite 10g with oracle database 10g windows 7.
      i want to create Matrix report.

      i have the following table.
      SQL> desc css_survey
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       SURVEY_ID                                 NOT NULL NUMBER(5)
       USER_ID                                   NOT NULL VARCHAR2(15)
       ACADEMIC_SEMESTER                                  VARCHAR2(25)
       USER_TYPE                                          VARCHAR2(7)
       Q1_STAFF                                           NUMBER(1)
       Q2_STAFF                                           NUMBER(1)
      for every question e.g. Q1_STAFF, there are 5 options to select from. it means for every Q_ column user could select one options from 1,2,3,4,5 .
      i have the following query
      SELECT 
      count(USER_ID) total_users, Q1_STAFF, 
      Q2_STAFF
      FROM CSS_SURVEY
      group by grouping sets
      (
      Q1_STAFF, 
      Q2_STAFF
      )
      by this query, i want to know that how much users select which option in each question. for example, how much user select option 1 from q1_staff, how much user select option 2 from q1_staff...upto 5. the same for every question.

      now the problem is this,
      i could select All the Q_columns as Matrix Column Fields,
      i could select the TOTAL_USERS as Matrix Cell Fields.
      but how could i select the option 1,2,3,4,5 as Matrix Row Fields. the five options 1,2,3,4,5 are the actual values in the every Q_COLUMN.

      please tell me if i am not much clear.
      i need your kind help.

      Regards.

      Edited by: Maahjoor on 14/12/2012 10:17 م

      Edited by: Maahjoor on 14/12/2012 10:17 م

      Edited by: Maahjoor on Dec 18, 2012 1:11 AM
        • 1. Re: Matrix Report Problem
          Maahjoor
          Dear all,

          i need your kind help.

          Regards.
          • 2. Re: Matrix Report Problem
            O.Developer
            Dear all,

            i need your kind help.

            Regards.

            ---HI,

            I think either you should short list your question and make it clear for all..

            If understood correction,...q1_staff, q2_staff.....all should be in single column name q_staff, in the same way student as well.. Incase if you just completed your table design then it is better remap it.

            ---

            If it is already running table, then paste here what exactly you need output format
            • 3. Re: Matrix Report Problem
              Maahjoor
              okay, i am going to shorten the things.
              SQL> desc css_survey
               Name                                      Null?    Type
               ----------------------------------------- -------- ----------------------------
               USER_ID                                   NOT NULL VARCHAR2(15)
               Q1_STAFF                                           NUMBER(1)
               Q2_STAFF                                           NUMBER(1)
              INSERT INTO CSS_SURVEY VALUES(1,1,1);
              INSERT INTO CSS_SURVEY VALUES(1,2,2);
              INSERT INTO CSS_SURVEY VALUES(1,3,3);
              INSERT INTO CSS_SURVEY VALUES(1,4,4);
              INSERT INTO CSS_SURVEY VALUES(1,5,5);
              INSERT INTO CSS_SURVEY VALUES(2,1,1);
              INSERT INTO CSS_SURVEY VALUES(2,2,2);
              INSERT INTO CSS_SURVEY VALUES(2,3,3);
              COMMIT;
              for every question e.g. Q1_STAFF, there are 5 options to select from. it means for every Q_ column user could select one options from 1,2,3,4,5 .
              i have the following query
              SELECT count(USER_ID) total_users, Q1_STAFF, Q2_STAFF
              FROM CSS_SURVEY 
              group by Q1_STAFF, Q2_STAFF
              by this query, i want to know that how much users select which option in each question. for example, how much user select option 1 from q1_staff, how much user select option 2 from q1_staff...upto 5. the same for every question.

              i want to create a matrix report like below
              --------------------------------------------------------------------------------------------------------------------
              QUESTIONS                    1             2                 3                4                    5
              --------------------------------------------------------------------------------------------------------------------
              Q1_STAFF                       2             2                2                1                    1
              Q2_STAFF                       2             2                2                1                    1
              --------------------------------------------------------------------------------------------------------------------
              this is a matrix report style i needd in which QUESTIONS is column heading, 1 2 3 4 5 are the option in each Q1_STAFF and Q2_STAFF columns, Q1_STAFF and Q2_STAFF are the rows, and the cell values are the total users who select the specified option.

              i want this matrix report.
              i cant be more clear than that man!!!.

              Edited by: Maahjoor on Dec 16, 2012 2:08 AM
              • 4. Re: Matrix Report Problem
                O.Developer
                HI

                In order to make Matrix report, please read blow article carefully and follow properly...it is easy to do it

                http://docs.oracle.com/html/B10602_01/orbr_nestedmatrix.htm

                ------------------------

                Please wait for some time, i will get my query to run on sql.....

                Edited by: O.Developer on Dec 16, 2012 3:21 PM
                • 5. Re: Matrix Report Problem
                  Maahjoor
                  ok, thank you,
                  i will be waiting for your response.
                  • 6. Re: Matrix Report Problem
                    Maahjoor
                    i read the page you provide, it was helpfull, but not related to my problem.

                    any help from your side is appriciated.
                    you could consider even 2 tables
                    SQL> desc q_options
                     Name                   
                     ---------------------
                     Q_OPT    
                    
                    insert into q_options values(1);              
                    insert into q_options values(2);
                    insert into q_options values(3);
                    insert into q_options values(4);
                    insert into q_options values(5);
                    commit;
                    now by joining we could get the desired results?

                    i m really stucked.
                    kindly help.
                    • 7. Re: Matrix Report Problem
                      O.Developer
                      Hi

                      Sorry yesterday was too late , so not replying.

                      Back to your question, still i am having confustion about data structure.

                      However to understand more , let us consider Scott schema emp table.

                      IN emp table one dept no. have more than one employee.
                      So we chan product such resulst based on below query


                      SELECT deptno,
                      count (decode(job, 'ANALYST', 'ANALYSIST')) ANALYST_CTS,
                      COUNT(DECODE(JOB,'CLERK','CLERK')) CLERK_CTS
                      FROM EMP
                      group by deptno;

                      Here you can keep add the Q1_Staff, Q1_student and try....

                      -- Try this idea if your table is correct..


                      If any issue persists, pleaset le me know
                      Edited by: O.Developer on Dec 17, 2012 10:49 AM

                      Edited by: O.Developer on Dec 17, 2012 11:12 AM
                      • 8. Re: Matrix Report Problem
                        Maahjoor
                        I WILL REPLY TOMOROW , TIME IS FINISH.
                        I AM REALY NEEDING YOUR HELP DEAR.
                        PLEASE KEEP IN TOUCH.

                        REGARDS.
                        • 9. Re: Matrix Report Problem
                          Maahjoor
                          ok,

                          i know this query, and the report, but situation is not the same here.

                          i have a table for questions, every question have 5 option to be selected from.

                          now i want to know which option from which question is selected by how much users?

                          here is my table for question
                          SQL> desc css_survey
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                           USER_ID                                   NOT NULL VARCHAR2(15)
                           Q1                                           NUMBER(1)
                           Q2                                          NUMBER(1)
                          INSERT INTO CSS_SURVEY VALUES(1,1,1);
                          INSERT INTO CSS_SURVEY VALUES(1,2,2);
                          INSERT INTO CSS_SURVEY VALUES(1,3,3);
                          INSERT INTO CSS_SURVEY VALUES(1,4,4);
                          INSERT INTO CSS_SURVEY VALUES(1,5,5);
                          INSERT INTO CSS_SURVEY VALUES(2,1,1);
                          INSERT INTO CSS_SURVEY VALUES(2,2,2);
                          INSERT INTO CSS_SURVEY VALUES(2,3,3);
                          COMMIT;
                          i want the following output
                          --------------------------------------------------------------------------------------------------------------------
                          QUESTIONS                                      Options
                          --------------------------------------------------------------------------------------------------------------------
                                                                1             2                 3                4                    5
                          --------------------------------------------------------------------------------------------------------------------
                          Q1                                 2             2                 2                1                    1
                          Q2                                 2             2                 2                1                    1
                          --------------------------------------------------------------------------------------------------------------------
                          each cell for Q1 and Q2 include the total users who selected the specific option.
                          how to do this? every question has 5 rows including 1,2,3,4,5.

                          Edited by: Maahjoor on Dec 17, 2012 9:09 PM
                          • 10. Re: Matrix Report Problem
                            O.Developer
                            oK fine,

                            i have a table for questions, every question have 5 option to be selected from.

                            now i want to know which option from which question is selected by how much users?

                            --
                            You said every question have 5 options, but in your table where is the options filed, ..you have user_id, q1,q2..

                            Just give data with one question and five options -is the User id is related to what?
                            • 11. Re: Matrix Report Problem
                              Maahjoor
                              SQL> desc css_survey
                               Name                                      Null?    Type
                               ----------------------------------------- -------- ----------------------------
                               USER_ID                                   NOT NULL VARCHAR2(15)
                               Q1                                           NUMBER(1)
                               Q2                                          NUMBER(1)
                              INSERT INTO CSS_SURVEY VALUES(1,1,1);
                              INSERT INTO CSS_SURVEY VALUES(1,2,2);
                              INSERT INTO CSS_SURVEY VALUES(1,3,3);
                              INSERT INTO CSS_SURVEY VALUES(1,4,4);
                              INSERT INTO CSS_SURVEY VALUES(1,5,5);
                              COMMIT;
                              SQL> select * from css_survey;
                              
                              USER_ID                                Q1         Q2
                              ------------------------------ ---------- ----------
                              1                                       1          1
                              1                                       2          2
                              1                                       3          3
                              1                                       4          4
                              1                                       5          5
                              i cant give you with one question, at lease two question is necessory for the type of report i am seeking.
                              now you have 2 questions, user with id 1 has seleted each of the option.
                              now my report should be looking like below
                              --------------------------------------------------------------------------------------------------------------------
                              QUESTIONS                    1             2                 3                4                    5
                              --------------------------------------------------------------------------------------------------------------------
                              Q1                                1             1                1                1                    1
                              Q2                                1             1                1                1                    1
                              --------------------------------------------------------------------------------------------------------------------
                              when i said every quesion have 5 options , it means each question could have values 1 upto 5. either of it. user could insert either of these 5 values.
                              i am sorry friend, i dont know how to become more clear. please let me know.
                              regards.