5 Replies Latest reply on Apr 6, 2017 9:29 AM by Hilary Farrell-Oracle

    Series Name in JET Charts

    Alex Nuijten

      Hi,

       

      I'm having trouble with a JET Chart,.. probably 'cause I'm misunderstanding..

       

      Anyway: I have the following query defined as the source of a Bar Chart - the query is at Region Level, not at Series level.

      select deptno
            ,job
            ,avg(sal) as avg_sal
        from emp
       group by deptno, job
      

       

      The following attributes - at series level - are set:

      Series Name: JOB

      Label: DEPTNO

      Value: AVG_SAL

       

      This results in the following Bar Chart:

       

      BarChart.PNG

       

      Looks nice and I was looking for something like this, however the data is not making any sense.. For example: there is no President in Deptno 20

      Executing the query in SQL Workshop yields the following results:

      BarChartQueryResult.PNG

       

      Any hints on what I'm doing wrong?

        • 1. Re: Series Name in JET Charts
          fac586

          Alex Nuijten wrote:

           

           

          I'm having trouble with a JET Chart,.. probably 'cause I'm misunderstanding..

           

          Anyway: I have the following query defined as the source of a Bar Chart - the query is at Region Level, not at Series level.

          1. selectdeptno
          2. ,job
          3. ,avg(sal)asavg_sal
          4. fromemp
          5. groupbydeptno,job

          The following attributes - at series level - are set:

          Series Name: JOB

          Label: DEPTNO

          Value: AVG_SAL

           

          This results in the following Bar Chart:

           

          BarChart.PNG

           

          Looks nice and I was looking for something like this, however the data is not making any sense.. For example: there is no President in Deptno 20

          Executing the query in SQL Workshop yields the following results:

          BarChartQueryResult.PNG

          Any hints on what I'm doing wrong?

          Seems to be a data densification issue: there must be an entry for each series in every group. This can be achieved using a partitioned outer join:

           

          select
              d.deptno 
            , e.job 
            , avg(e.sal) avg_sal 
          from
              dept d
                left outer join emp e
                  partition by (e.job)
                  on d.deptno = e.deptno
          group by
              d.deptno
            , e.job 
          order by
              d.deptno
            , e.job
          

           

          jet_chart_densified.PNG

          • 2. Re: Series Name in JET Charts
            Alex Nuijten

            Thanks,

             

            I came up with something similar:

            select e.deptno
                  ,j.job
                  ,avg (e.sal) as avg_sal
              from (select distinct job
                    from emp
                  ) j
            left outer
            join emp e partition by (e.deptno)
              on j.job = e.job
            group by e.deptno
                    ,j.job
            

             

            Still think it's strange though...

            • 3. Re: Series Name in JET Charts
              Dimitri Gielis

              I just hit this un-logic behaviour too (at least in my view)...

              The bars are not looking at the values, instead they look at the position, which I find not logic and just gives a wrong chart.

              I checked and in APEX 5.1.1 it's still the same behaviour.

               

              Hilary Farrell-Oracle maybe you can give your view on this thread?

               

              Thanks so much,

              Dimitri

              • 4. Re: Series Name in JET Charts
                Massimo

                Hi Dimitri

                Yes, also for me the X-axis are "positional" and the values are shifted to fill the empty labels.

                I think it's could be a bug.

                For now, I still use the old version with AnyChart

                • 5. Re: Series Name in JET Charts
                  Hilary Farrell-Oracle

                  Hi Dimitri,

                   

                  Apologies again for the delay in responding. As discussed last week during the APEX World conference, and demonstrated during my session, we do plan on revising our existing handling of such multi-series queries, given the number of questions that have been raised on the topic.  The current behaviour is expected, as we have intentionally left it to the user to handle the data densification of their queries themselves.  JET requires that each series of a multi-series chart contain the same number of data points, in order to accurately render the points against a given x-axis.  Otherwise it will simply render the data points in the order in which they are contained within the JSON object i.e the object that contains the chart data.  So if your query doesn't handle the densification, then you can end up with a chart that's misrepresenting the underlying data.  Due to the volume of questions on this topic, we will revise our handling to automatically handle this densification.  In the meantime, you will need to do some work on your side to ensure that your query renders the same number of data points for each series on your chart.

                   

                  Regards,

                  Hilary