This discussion is archived
2 Replies Latest reply: Mar 26, 2013 9:28 AM by Ewaver RSS

Color APEX charts based on query value returned?

Ewaver Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points