5 Replies Latest reply on Nov 21, 2017 5:37 PM by Severine

    sorting x-axes (text column) in bar chart with multiple series

    3533578

      Dear all,

       

      I am trying to generate a bar chart with the top 20 of events sorted in descending order for number of participants. I have a working SQL script, which produces the correct order in SQL developer (with order by clause). And also my bar chart shows this order, as long as I only have a single series. However, if the series are marked somehow (for instance by the organiser) in multiple series or with a colour column, the legend shows the correct values, but the order changes to alphabetical order on name of event. How can I change this? How can I plot multiple series in a bar chart while retaining top20 order?

       

      many thanks for your help,

       

      Lidewij

        • 1. Re: sorting x-axes (text column) in bar chart with multiple series
          Severine

          I have the same issue, The "order by" clause does not seem to work anymore when two series are implemented in the bar chart (apex 5.1.1 - oracle jet), whereas it works fine with an unique serie .

           

          Do you have any workaround to solve this issue!

          Thanks for your help!

          Severine

          • 2. Re: sorting x-axes (text column) in bar chart with multiple series
            3533578

            HI Severine,

             

            This is how i solved it:

             

            Imagine I want to show the total number of visitors for each movie seperated by age group/gender, for instance, boys, girls, man and women.

             

            I did not full in the series atribute of my chart. Instead I manually coded 4 seperate series for e.g. man, women, boys, girls. I do not sort any of these series, so not order by in the sql code.

             

            Each series is supposed to contain 0 values in case no values are present for the specific label of a bar chart. you can achieve this by joining the sql table with itself with a left outer join. In this wa, values are always given, also when 0 is found from a sum or count. In this way, I can stack the series on top of each other, while still only the series is shown that has a value for the label  column (each specific movie).

             

            Next I add to the following javascript code to the chart attributes:

             

            function( options ){

                options.dataFilter = function( data ) {

                    // Let first two series to be in its own stack

                    data.series[ 0 ].stackCategory = "separate-stack-0";

                    data.series[ 1 ].stackCategory = "separate-stack-0";

                    data.series[ 2 ].stackCategory = "separate-stack-0";

                    data.series[ 3 ].stackCategory = "separate-stack-0";

                    return data;

                  };

                options.sorting  = "descending"

                return options;

             

            As long as seperate-stack-0 are the same across all data series, they are stacke on top of each other. If the name is different, the data series is shown as a new column. For instance, if you want boys and man stacked in one bar and girls and women stacked on a seperate bar, you can change the code to:

             

                    boys: data.series[ 0 ].stackCategory = "separate-stack-0";

                    man: data.series[ 1 ].stackCategory = "separate-stack-0";

                    girls: data.series[ 2 ].stackCategory = "separate-stack-1";

                    women: data.series[ 3 ].stackCategory = "separate-stack-1";

             

            The order of the series in the legend and chart area follows the order of the series in the chart plug-in of  apex.

             

            Finally the options.sorting takes care of the ordering of the values, in my case descending for the value column (number of visitors). please check out this oraclejet document for a description of ojchart.sorting

             

            https://docs.oracle.com/middleware/jet220/jet/reference-jet/oj.ojChart.html#sorting

             

            And I would recommend to download the very latest apex update, since last october some bugs were fixed in the bar charts of apex.

             

            I hope this covers your needs,

             

            BR, Lidewij

            • 3. Re: sorting x-axes (text column) in bar chart with multiple series
              Severine

              Hi Lidewij,

               

              Thank you for your reply. Actually I would like to sort the x_axis though a criterea like a column of my sql query, not though the value data

              Can I do it though the javascript code ?

               

              Note that I use the latest apex version (5.1.3)

              Thank you.

              Severine

              • 4. Re: sorting x-axes (text column) in bar chart with multiple series
                Christopher C

                Hi Severine,

                 

                I was working on the same problem as you, where I need to sort my x-axis by an additional column in my query (sort_by column).   Were you able to make any progress or workarounds?

                 

                Thanks.

                • 5. Re: sorting x-axes (text column) in bar chart with multiple series
                  Severine

                  Hi Christopher,

                   

                  Actually it seems that the order is deduced from the Label column. So the only workaround I found is to prefix the label with the order number.

                  But that's not nice at all ...

                   

                  Severine