2 Replies Latest reply: Mar 26, 2013 11:28 AM by Ewaver RSS

    Color APEX charts based on query value returned?

    Ewaver
      Hi,

      I appreciate any information regarding this topic. Do you know if there is a way to change the color of a column chart based on the value that is returned in SQL query? This specific type of query and chart only has one bar...so we don;t have to worry about multiple series of bars here.. For example,

      select null link, FTE_NAME label, (SUM(INVOICeABLE_ALLOCATION) + SUM(SECOND_BILL_ALLOC)) value1
      from FTE_VIEW
      WHERE FTE_ID = :P9_ID
      group by FTE_NAME

      This might return an allocation of 2.5 for a specific person, where we only want people allocated to 1.0 so anything > 1.0 the chart color would turn red.

      PS: There is a reason we are allowing them to enter values >1.0 by the way ;) if you are wondering..haha

      Any ideas? I also looked up in the AnyChart knowledge base, but could not find any help there..

      Kind Regards,
        • 1. Re: Color APEX charts based on query value returned?
          Logaa
          Hi,
          Try this.
          Click chart attributes- chart setting - custom - custom color->
          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:#FF0000 - static red color will display
          Item Name:P1_COLORS - dynamic from item value after set color values
          compute Item using sql query with decode statement to set the color and then use the item into custom color.

          thanks,
          Loga
          • 2. Re: Color APEX charts based on query value returned?
            Ewaver
            Hi,

            Thanks for your help. I am not clear on whether I need to create the :P1_Colors item or not. If I choose Custom colors and then have my colors as #099E4A,#F00E19 can I reference :P1_COLORS in my SQL or do I have to create the item :P1_COLORS and use a statics list? Also I've playing with the SQL, I was able to get a return value using a CASE statement, but I had no lukc with the Decode...Here is what I tried IN SQLdeveloper but could not get it to work.
            SELECT FTE_NAME, DECODE(SUM(ALLOCATION),SUM(ALLOCATION)<=1,'GREEN','RED') AS COLOR
            FROM FTE_PRJK_VIEW
            where id = :P9_ID AND FTE_PROJECT_STATUS = 'YES'
            group by FTE_NAME

            (Where i have Green and Red I would have the item :P1_COLORS = #099E4A,#F00E19, ETC..)

            Was able to get this case statement to return a value in SQL Developer..but cannot get it to work in the chart...

            select null link, FTE_NAME label,
            CASE WHEN SUM(ALLOCATION)<1
            THEN :P1_COLORS = '#099E4A'
            else :P1_COLORS = '#F00E19'
            END CASE value1
            from FTE_PRJK_VIEW
            where id = :P9_ID AND FTE_PROJECT_STATUS = 'YES'
            group by FTE_NAME

            Really Appreciate the help with this :)

            Kind Regards,