12 Replies Latest reply: Mar 29, 2013 7:43 AM by Sakthi RSS

    Horizontal Bar Chart - Apex 4.0

    Sakthi
      Hi All,

      Please find my below requirement and help me to achieve this.
      SELECT empno,ename,res_from_date,res_to_date FROM EMP WHERE DEPTNO = 10;
      
      EMPNO     ENAME                RES_FROM_DATE                RES_TO_DATE 
      7839      KING              01/01/2013                      03/31/2013 
      7782     CLARK             01/01/2013                      05/31/2013 
      7934     MILLER            01/01/2013                      01/31/2013 
      From the above data, it is clearly mentioned that employee king has been reserved from 01/01/2013 to 03/31/2013 and similarly others too.

      Now my graph must be in this way, X axis to be populated with all the months of the current year (Jan - Dec) and my Y axis must be populated with the employee names. Also against the employee King, i need a horizontal bar filling the month from Jan to Mar and the similar way for others too.
      Emp_Name                                    
      (Y Axis)
      
      King
                                          
      Clark
                                          
      Miller                                    
      
      Months                     Jan            Feb                        Mar                       Apr                        May                    Jun
      (X Axis)
      Please help me to proceed further.

      Thanks in advance.

      Regards,
      Sakthi.
        • 1. Re: Horizontal Bar Chart - Apex 4.0
          Howard (... in Training)
          Perhaps you want a Gantt Chart. Please see the example(s) here: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/apex/r40/apexcharts/apexcharts_ll.htm

          Regards,
          Howard
          • 2. Re: Horizontal Bar Chart - Apex 4.0
            Sakthi
            Hi Howard,

            Thanks for your reply.

            We are using Gantt Chart in our application currently for this scenario.

            But client feels, that Gantt Chart is not looking good and they particularly needs this with horizontal bar charts.

            Is there any way to achieve this using bar charts in Apex???

            Please help.

            Thanks once again.

            Regards,
            Sakthi.
            • 3. Re: Horizontal Bar Chart - Apex 4.0
              Howard (... in Training)
              If you've looked -- as I did, I suppose you haven't found any "native" APEX AnyChart type giving horizonal segments either. The Gantt chart gives horizontal lines and I suppose you could fiddle with the line width setting making the lines super thick if you tried. But not exactly what we want.

              The data set for this would be "points" of (x,y<sub>1</sub>,y<sub>2</sub>). The usual horizontal and column bar charts are for data points (x,y<sub>1</sub>=0,y<sub>2</sub>). Theoretically, if one could hide the section of the bar from [0,y<sub>1</sub>], then that would solve the problem. I have no idea how to do that! Sorry.

              Howard
              • 4. Re: Horizontal Bar Chart - Apex 4.0
                Sakthi
                Hi Howard,

                Thanks for your reply and efforts.

                The ultimate goal of this chart to show, the conflicts between the same employee reserved for same duration.

                (i.e.) On January month there is a conflict between King and Miller.
                On February month there is a conflict between King and Clark.

                To display this in a pictorical representation, we need a Chart but not a Gantt Chart.

                Is there any other way to give this kind of pictorical representation in Apex as a solution to the customer??

                Is there any other way to have non numerical values in axes???

                Please help.

                Thanks in advance.

                Regards,
                Sakthi.
                • 5. Re: Horizontal Bar Chart - Apex 4.0
                  Howard (... in Training)
                  Let's talk about axes. Some (many?) axes -- depending on chart type -- are set up as a (sorted) collection of LABELS. For example, you could have data for 3 points with 1, 2, 5 on the y-axis and you might expect to see 1, 2, 3, 4, 5 but depending on the type of chart you might get a range 0, 1, 2, 3, 4, 5, 6 or you might just get just the three points 1, 2, 5. For horizonal bar and stacked bar column charts, I think the axis are (usually?) a collection of labels. Which means for example, that if you have data for Jan, Mar and April, you will not see a chart with Jan, Feb, Mar, Apr. The chart doesn't infer a missing February. You'll probably see (sorted) Apr, Mar, Jan. Other chart types do give a mathmatical scale.

                  That said, can you create the following data from what you have?
                  EMPNO     ENAME         RES_MONTH
                  7839      KING          01-JAN
                  7839      KING          02-FEB
                  7839      KING          03-MAR
                  7782      CLARK         01-JAN
                  7782      CLARK         02-FEB
                  7782      CLARK         03-MAR
                  7782      CLARK         04-APR
                  7782      CLARK         05-MAY
                  7934      MILLER        01-JAN
                  Then try a horizontal bar (or stacked column) chart with Months for the labels.

                  Series 1
                  SELECT NULL LINK,
                  RES_MONTH LABEL,
                  1 "KING"
                  FROM <table>
                  WHERE ENAME = 'KING'
                  ORDER BY RES_MONTH

                  Series 2
                  SELECT NULL LINK,
                  RES_MONTH LABEL,
                  1 "CLARK"
                  FROM <table>
                  WHERE ENAME = 'CLARK'
                  ORDER BY RES_MONTH

                  Series 3
                  SELECT NULL LINK,
                  RES_MONTH LABEL,
                  1 "MILLER"
                  FROM <table>
                  WHERE ENAME = 'MILLER'
                  ORDER BY RES_MONTH

                  (There must be a solution for a variable number of employees ("N") but I haven't worked that problem yet.)

                  Try charting that and see if you get closer to what you need.

                  Howard
                  • 6. Re: Horizontal Bar Chart - Apex 4.0
                    Sakthi
                    Hi Howard,

                    Thanks alot for your efforts and help.

                    As you said, the output is very close to my requirement.

                    But am bothering about the following things over here,

                    1. Label (To display the employee name instead of the number 1)
                    2. We are creating the chart series statically but i cannot do this static series creation every time. I do not have any idea of making this automatic. (The table is containing three employees detail hence we created 3 chart series, the data will be modified then and there, at that time how should i handle this??)

                    Please help and advice.

                    Thanks in advance.

                    Regards,
                    Sakthi.
                    • 7. Re: Horizontal Bar Chart - Apex 4.0
                      Howard (... in Training)
                      It gets a little complex but the solution is here: http://ruepprich.wordpress.com/2011/03/31/apex-4-charts-dynamically-adding-a-series-2/

                      Look at the chart about 25% down the page. Imagine the state abbrevs (TX, OK) replaced by your employee numbers.

                      Here it is for two states and then they show how to do "N" states -- data dependent.
                      SELECT NULL
                            ,sales_date
                            ,SUM(decode(state, 'TX', sales_amount, 0)) "Texas"
                            ,SUM(decode(state, 'OK', sales_amount, 0)) "Oklahoma"
                       FROM sales
                      GROUP BY sales_date
                      ORDER BY sales_date;
                      In this case, sales_date is out RES_MONTH
                      No SUM is desired,
                      sales_amount is 1 (just set it to a standard "resource_unit", i.e., 1 person-month)

                      So I think you'd have something like:
                      SELECT NULL
                            ,RES_MONTH
                            ,decode(ename, 'CLARK', 1, 0)) "CLARK"
                            ,decode(ename, 'MILLER', 1, 0)) "MILLER"
                       FROM employee-table
                      ORDER BY RES_MONTH;
                      Okay, you want EMPNO. And you might want NULL instead of 0 so an employee just disappears when not allocated.
                      SELECT NULL
                            ,RES_MONTH
                            ,decode(EMPNO, '7839', 1, NULL)) "7839"
                            ,decode(EMPNO, '7782', 1, NULL)) "7782"
                            ,decode(EMPNO, '7934', 1, NULL)) "7934"
                       FROM employee-table
                      ORDER BY RES_MONTH;
                      Then converted the chart to use a function returning a query.

                      I'm sure there are errors and gaps here but perhaps you get the idea.

                      Howard
                      • 8. Re: Horizontal Bar / Gantt Chart - Apex 4.0
                        Sakthi
                        Hi Howard,

                        Thanks a ton to you.

                        It really gives the output what i expected.

                        One more question to you, is there a possibility of changing the gannt chart bar colours based upon the values in Apex 4.0???

                        The ultimate goal is to give a colourful represention of gannt chart to the customer.

                        Please help me in sharing info about customized gantt chart with good look and feel.

                        Thanks in advance.

                        Regards,
                        Sakthi.
                        • 9. Re: Horizontal Bar / Gantt Chart - Apex 4.0
                          Sakthi
                          Hi Howard,

                          Thanks a ton to you.

                          It really gives the output what i expected.

                          One more question to you, is there a possibility of changing the gannt chart bar colours based upon the values in Apex 4.0???

                          The ultimate goal is to give a colourful represention of gannt chart to the customer.

                          Please help me in sharing info about customized gantt chart with good look and feel.

                          Thanks in advance.

                          Regards,
                          Sakthi.
                          • 10. Re: Horizontal Bar / Gantt Chart - Apex 4.0
                            Howard (... in Training)
                            I haven't used this yet but for your chart, under Chart Attributes, then under Chart Settings, change the Color Scheme selector to Custom. Then you can set the color values to be used. It says:

                            >
                            Enter colors separated by comma, or an item in the application that holds a comma separated list of colors. Set of Custom Colors will be used to change chart colors automatically if you selected Custom option for the Color Scheme.

                            For example:
                            Comma separated list of colors:#005381,#0D813D,#BB8199
                            Item Name:P1_COLORS
                            >
                            This suggests you can put in constant values or the name of a variable which will hold the values and which you could vary. [Right off, I don't know how to find the colors the Gantt charts use.]

                            I Googled for a good APEX example with Custom Colors but did not come up with one.

                            Hope this helps,
                            Howard
                            • 11. Re: Horizontal Bar / Gantt Chart - Apex 4.0
                              Sakthi
                              Hi Howard,

                              Thank you so much.

                              Even i did the same for changing the colors.

                              For resource gantt chart it randomly picks up any color from the provided custom colors during execution and applies the same color for all the available resource bars.

                              For project gantt chart for each duration it picks up each color from the provided custom colors but again the same combination remains for all the available project bars.

                              So i think in Gantt chart we cannot differentiate the values (resources / projects) with colors.

                              Please share your view if my understanding is wrong.

                              Also i dont have any idea about AnyChart and integrating the same with Apex. Please share any useful documents if you have about this Apex with AnyChart.

                              Thanks in advance.

                              Regards,
                              Sakthi.
                              • 12. Re: Horizontal Bar / Gantt Chart - Apex 4.0
                                Howard (... in Training)
                                Well, I've never used the Gantt charts. Oh about AnyChart: A version of AnyChart is already integrated with APEX and that's what you use when you do the usual charts in APEX. But then, in addition, you can go to the On-line AnyChart documentation to discover features/attributes that can be added as custom XML. Features documented in AnyChart.

                                Howard