6 Replies Latest reply on Jun 13, 2014 1:13 PM by jwellsnh

    Line chart display value order issue.

    Vedant

      Dear Friend,

      I have made line with two series .

      One series is for my actual query performance

      second series is for my target  values of month.

      Both series display data based on month.

      Query is based on 13 months performance.

      from current months to last 12 months performance it displays.

      But right now i have 2 months data for actual performance & i have data for 13 months of target line.

      it does not show data in actual order means which 2 month have  data ,it displays on starting of the graph.

       

      My graph labels are

      jun-13, jul-13----dec13 jan-14,feb-14 ---jun-14.

      But i only have may-14 data and jun-14 data

      it should display month data at end of the graph not to starting of the graph means in my label may-14  and jun-14 are at end of all the labels .

       

      Hope i am able to clear you.

       

      How can i display data in order.

        • 1. Re: Line chart display value order issue.
          jwellsnh

          Hi Vedant,

           

          It isn't totally clear what your specific issue is.  You said:

          But right now i have 2 months data for actual performance & i have data for 13 months of target line.

          Does that translate that you only have 2 months worth of data from one of your chart series?  If true, how have accounted for the NULL values or NULL months?

           

          Specifically, what order is your graph dating showing up as?  What have you done to order your data?

           

          One more request, please share your code that is behind each series.

           

          Jeff

          • 2. Re: Line chart display value order issue.
            Vedant

            Dear jeff,

            my actual graph query is---

             

            SELECT NULL LINK, MTH || '-' || YR AS LABEL,RESULT FROM (

            SELECT  

            MTH,YR,MTH1,

                     --  MTH || '-' || YR AS LABEL,

                       ROUND ( (SUM (PERFORMANCE) / COUNT ( * )), 2) AS RESULT

                FROM   ( SELECT   SERVICE_TYPE,

                                   SUM (RESULT),

                                   COUNT ( * ),

                                   ROUND ( (SUM (RESULT) / COUNT ( * )), 2) AS PERFORMANCE,

                                   MTH,

                                   YR,

                                   MTH1

                            FROM   (  SELECT   PORT_ID,

                                               COUNTRY,

                                               REGION,

                                               SERVICE_TYPE,

                                               SUM (SCORE),

                                               SUM (OUT_OF),

                                               ROUND ( ( (SUM (SCORE) / SUM (OUT_OF)) * 10), 2)

                                                  AS RESULT,

                                               MTH,

                                               YR,

                                               MTH1

                                        FROM   (SELECT   S.PORT_ID,

                                                         S.REGION,

                                                         S.COUNTRY,

                                                         S.SERVICE_TYPE,

                                                         SCORE,

                                                         OUT_OF,

                                                         TO_CHAR (start_date, 'MON') AS MTH,

                                                         TO_CHAR (start_date, 'YYYY') AS YR,

                                                         TO_CHAR (start_date, 'MM') AS MTH1

                                                  FROM   EBA_SB_SURVEYS S,

                                                         EBA_SB_USERS_PORT_MAP UPM

                                                 WHERE       S.PORT_ID = UPM.PORT_ID

                                                           

                                                         AND UPM.USER_ID = :APP_USER

                                                         AND SERVICE_TYPE = 'TRP'

                                                         AND SCORE IS NOT NULL

                                                         AND TO_CHAR(S.START_DATE,'MM')<=TO_CHAR(SYSDATE,'MM')

                                                          AND TO_CHAR(S.START_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')

                                                     

                                                              )

                                    GROUP BY   PORT_ID,

                                               SERVICE_TYPE,

                                               MTH,

                                               MTH1,

                                               YR,

                                               COUNTRY,

                                               REGION)

                        GROUP BY   SERVICE_TYPE,

                                   MTH,

                                   MTH1,

                                   YR

                                  

                  UNION                 

                                  

                   SELECT   SERVICE_TYPE,

                                   SUM (RESULT),

                                   COUNT ( * ),

                                   ROUND ( (SUM (RESULT) / COUNT ( * )), 2) AS PERFORMANCE,

                                   MTH,

                                   YR,

                                   MTH1

                            FROM   (  SELECT   PORT_ID,

                                               COUNTRY,

                                               REGION,

                                               SERVICE_TYPE,

                                               SUM (SCORE),

                                               SUM (OUT_OF),

                                               ROUND ( ( (SUM (SCORE) / SUM (OUT_OF)) * 10), 2)

                                                  AS RESULT,

                                               MTH,

                                               YR,

                                               MTH1

                                        FROM   (SELECT   S.PORT_ID,

                                                         S.REGION,

                                                         S.COUNTRY,

                                                         S.SERVICE_TYPE,

                                                         SCORE,

                                                         OUT_OF,

                                                         TO_CHAR (start_date, 'MON') AS MTH,

                                                         TO_CHAR (start_date, 'YYYY') AS YR,

                                                         TO_CHAR (start_date, 'MM') AS MTH1

                                                  FROM   EBA_SB_SURVEYS S,

                                                         EBA_SB_USERS_PORT_MAP UPM

                                                 WHERE       S.PORT_ID = UPM.PORT_ID

                                                         AND UPM.USER_ID = :APP_USER

                                                         AND SERVICE_TYPE = 'TRP'

                                                         AND SCORE IS NOT NULL

                                                         AND TO_CHAR(S.START_DATE,'MM')>=TO_CHAR(SYSDATE,'MM')

                                                         AND TO_CHAR(S.START_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY')-1

                                                 

                                                              )

                                    GROUP BY   PORT_ID,

                                               SERVICE_TYPE,

                                               MTH,

                                               MTH1,

                                               YR,

                                               COUNTRY,

                                               REGION)

                        GROUP BY   SERVICE_TYPE,

                                   MTH,

                                   MTH1,

                                   YR

                                   )               

                           GROUP BY MTH,YR,MTH1 ORDER BY YR ASC,MTH1 ASC ) WHERE ROWNUM<=13

                         --GROUP BY   MTH || '-' || YR

             

             

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

            My Target line code  is----------

            SELECT   NULL LINK, TAR_MONTH || '-' || YEAR LABEL, RESULT

              FROM   (  SELECT   YEAR,

                                 TAR_MONTH,

                                 MTH1,

                                 SERVICE_TYPE,

                                 RESULT

                          FROM   (  SELECT   YEAR,

                                             TAR_MONTH,

                                             TO_CHAR (TO_DATE (TAR_MONTH, 'MON'), 'MM') MTH1,

                                             SERVICE_TYPE,

                                             COUNT ( * ),

                                             SUM (PERC),

                                             SUM (PERC) / COUNT ( * ) AS RESULT

                                      FROM   (  SELECT   year,

                                                         TAR_MONTH,

                                                         REGION,

                                                         COUNTRY,

                                                         PORT_ID,

                                                         SERVICE_TYPE,

                                                         SUM (TARGET_PERC),

                                                         COUNT ( * ),

                                                         ROUND (

                                                            (SUM (TARGET_PERC) / COUNT ( * )),

                                                            2

                                                         )

                                                            AS PERC

                                                  FROM   eba_sb_template_target

                                                 WHERE       YEAR = TO_CHAR (SYSDATE, 'YYYY')

                                                         AND service_type = 'TRP'

                                                         AND TO_CHAR (

                                                               TO_DATE (TAR_MONTH, 'MON'),

                                                               'MM'

                                                            ) <= TO_CHAR (SYSDATE, 'MM')

                                              GROUP BY   year,

                                                         TAR_MONTH,

                                                         REGION,

                                                         COUNTRY,

                                                         PORT_ID,

                                                         SERVICE_TYPE)

                                  GROUP BY   YEAR, TAR_MONTH, SERVICE_TYPE

                                  UNION ALL

                                    SELECT   YEAR,

                                             TAR_MONTH,

                                             TO_CHAR (TO_DATE (TAR_MONTH, 'MON'), 'MM') MTH1,

                                             SERVICE_TYPE,

                                             COUNT ( * ),

                                             SUM (PERC),

                                             SUM (PERC) / COUNT ( * ) AS RESULT

                                      FROM   (  SELECT   YEAR,

                                                         TAR_MONTH,

                                                         REGION,

                                                         COUNTRY,

                                                         PORT_ID,

                                                         SERVICE_TYPE,

                                                         SUM (TARGET_PERC),

                                                         COUNT ( * ),

                                                         ROUND (

                                                            (SUM (TARGET_PERC) / COUNT ( * )),

                                                            2

                                                         )

                                                            AS PERC

                                                  FROM   eba_sb_template_target

                                                 WHERE   YEAR = TO_CHAR (SYSDATE, 'YYYY') - 1

                                                         AND service_type = 'TRP'

                                                         AND TO_CHAR (

                                                               TO_DATE (TAR_MONTH, 'MON'),

                                                               'MM'

                                                            ) >= TO_CHAR (SYSDATE, 'MM')

                                              GROUP BY   year,

                                                         TAR_MONTH,

                                                         REGION,

                                                         COUNTRY,

                                                         PORT_ID,

                                                         SERVICE_TYPE)

                                  GROUP BY   YEAR, TAR_MONTH, SERVICE_TYPE)

                      ORDER BY   YEAR ASC, MTH1 ASC)

            WHERE   ROWNUM <= 13

            • 3. Re: Line chart display value order issue.
              jwellsnh

              Hi Vedant,

               

              I asked:

              Does that translate that you only have 2 months worth of data from one of your chart series?  If true, how have accounted for the NULL values or NULL months?

               

              Specifically, what order is your graph dating showing up as?  What have you done to order your data?

              Since my questions went unanswered, I will have make some assumptions.

               

              You said:

              I have made line with two series .

              One series is for my actual query performance

              second series is for my target  values of month.

              Both series display data based on month.

              Query is based on 13 months performance.

              from current months to last 12 months performance it displays.

              But right now i have 2 months data for actual performance & i have data for 13 months of target line.

              it does not show data in actual order means which 2 month have  data ,it displays on starting of the graph.

              My assumption is that the performance query is the issue and this section of code could be where your issue lies:

              SELECT s.port_id,
               s.region,
               s.country,
               s.service_type,
               score,
               out_of,
               TO_CHAR (start_date, 'MON') AS mth,
               TO_CHAR (start_date, 'YYYY') AS yr,
               TO_CHAR (start_date, 'MM') AS mth1
               FROM eba_sb_surveys s, eba_sb_users_port_map upm
               WHERE s.port_id = upm.port_id
               AND upm.user_id = :app_user
               AND service_type = 'TRP'
               AND score IS NOT NULL
               AND TO_CHAR (s.start_date, 'MM') <= TO_CHAR (SYSDATE, 'MM')
               AND TO_CHAR (s.start_date, 'YYYY') = TO_CHAR (SYSDATE, 'YYYY')
              

               

              Questions:

              1. Does this query return a row for each and every month?
              2. Are you limiting the query to a specific year?

              Jeff

              • 4. Re: Line chart display value order issue.
                Vedant

                Hi jeff,

                Answer of first question is :-

                it does not return row for each & every month. It only return data of may-2014 and june-2014.I have not handled null value here.

                Answer of Second question is :-

                I have to display this query on two page. First page display only current year data. And second page works based on filters. Which year will he select data will be display of that year?

                 

                Thanks & regards

                Vedant.

                • 5. Re: Line chart display value order issue.
                  jwellsnh

                  Hi Vedant,

                   

                  I have some code at my office and will share this later on.  Essentially you need create another query that generates a row for each month and a value of zero (0).  This query is then applied as a union to your other query set and months are then grouped and values are summed.  In your case, you will end up with 2 rows with a value greater than zero and 10 rows with a value of zero.

                   

                  Your query is somewhat complex and this is going to increase its complexity.  If you can do anything to tidy your code or lessen its complexity, those that might debug this code some day you will appreciate it.

                   

                  Jeff

                  • 6. Re: Line chart display value order issue.
                    jwellsnh

                    Vedant,

                     

                    I have a similar situation where some divisions may have earned overtime within a month and some have not.  Here is the code that I use in one of my line charts.  The union query as I stated before returns one for row each of our fiscal months.  The final result set if grouped by link and label with the value amount summed.  You will need to do the same with your query.

                      SELECT link, label, SUM (cio) "CIO"
                        FROM (SELECT 'f?p=&APP_ID.:441:&SESSION.::::P441_DIV,P441_FY,P441_PRIOR_PAGE:1650,&P328_FY.,&APP_PAGE_ID.'
                                        link,
                                     label,
                                     cio
                                FROM (  SELECT NULL link,
                                                  TO_CHAR (fiscal_year, '0000')
                                               || TO_CHAR (fiscal_month, '00')
                                                  AS label,
                                               SUM (NVL (minipay_detail.contract_pay_amt, 0)) AS cio
                                          FROM lhr.minipay_detail, job_number_tbl
                                         WHERE     minipay_detail.object_class_cd = '018'
                                               AND minipay_detail.job_number_cd =
                                                         job_number_tbl.job_oit_cde
                                                      || job_number_tbl.job_agency_cde
                                                      || job_number_tbl.job_seq
                                               AND SUBSTR (job_number_tbl.ifs_title_dsc, 1, 3) =
                                                      'CIO'
                                               AND (   :p328_fy IS NULL
                                                    OR minipay_detail.fiscal_year = :p328_fy)
                                      GROUP BY    TO_CHAR (fiscal_year, '0000')
                                               || TO_CHAR (fiscal_month, '00'),
                                               'CIO'
                                      -- Create a row for each fiscal month with a value of zero(0)
                                      UNION ALL
                                        SELECT DISTINCT
                                               NULL link,
                                                  TO_CHAR (fiscal_year, '0000')
                                               || TO_CHAR (fiscal_month, '00')
                                                  AS label,
                                               0 "CIO"
                                          FROM lhr.minipay_detail
                                         WHERE (   :p328_fy IS NULL
                                                OR minipay_detail.fiscal_year = :p328_fy)
                                      GROUP BY    TO_CHAR (fiscal_year, '0000')
                                               || TO_CHAR (fiscal_month, '00')))
                    GROUP BY link, label
                    ORDER BY label
                    

                     

                    Jeff