Clarity

Hello,

Is it possible to define the colors used in a pie chart? Colors are randomly picked in the pie chart and sometimes they are not appropriate.
Wondering if there is any way to dictate the colors.

Thank you
Rajani.

There isn't really a good way to do that. You could define a color palette under System Options on the admin side and keep moving the colors up and down the list until you get what the desired colors you want, but ultimately it is randomly selected and a rather futile effort.

Something I certainly wish was improved in the product.

The colors that the pie chart uses are not random - they are produced in the order of the colors defined in the system palette Rob refers to.

i.e. the "first" value that is returned by your query will appear in the first color defined in the palette, the second value in the second color etc etc etc.

So the trick is just to make sure your QUERY always provides a value for the "segment", even when the segment is NULL (missing).

That way your pie chart will ALWAYS produce the relevant segment in the correct colors (the null segments are just not displayed in the pie).


Its actually pretty simple once you get your head around making the QUERY results consistent (and the need to provide the NULLs)

Agree, the trick is with the query. I figured out the query using left join to get 0 count rows too. I still have a problem. I need the count of projects without a status_indicator value defined.


select v.name, nvl(temp.c, 0)
from cmn_lookups_v v
left join (
select count(*) c, nvl(status_indicator,99) status_indicator
from inv_investments
group by nvl(status_indicator,99)
) temp
on v.lookup_enum = temp.status_indicator
where v.lookup_type = 'INVESTMENT_STATUS_INDICATOR' and v.language_code = 'en'


Any ideas ?? Appreacite your help.

Thank you,
Rajani.

[color=#121E9C][size=4][font=Arial]Hi Rajani,

Try this.

select

coalesce(inv.status_indicator, 99) as color_code, 

count(*) as project_cnt, 

coalesce((


select v.name


from  cmn_lookups_v v 


where v.lookup_type   = 'INVESTMENT_STATUS_INDICATOR' 


  and v.language_code = 'en' 


  and v.lookup_enum   =  inv.status_indicator

), 'NONE') as color

from
 inv_investments inv
group by inv.status_indicator
order by 1

I used the standard COALESCE function instead of the ORACLE specific nvl function to make this applicable to both MSSQL and QORACLE databases.
Hope this helps.

Patrick
[font][size][color]

I tried union but your query is much better.

Thanks again.