Small slices of pie

Small post to get back into things.

I had to do a Pie Chart in Apex. The problem was, on the basic select, some of the labels ran together.



If you order the items by how big the slice of pie is, then all the items with small values will be next to each other so their labels will be close and there's a good chance of them overlapping. What I wanted to do was re-order the items to minimize the chance of small items being placed together. Obviously if there are lots of small items and only a couple of big ones that won't always be possible, but its worth a try.

What I came up with was analytics.

Firstly, I rank the items by the value, both ascending and descending. The item with the highest value has both the highest and lowest rankings, and so does the item with the lowest. Ones in the middle stay in the middle. I order by the greatest of the two rankings, with a slight adjustment to the 'descending' ranking to avoid ordering collisions.

select link, label, value
from
(select null link, label, count(*) value,
    rank() over (order by count(*)) rnk,
    rank() over (order by count(*) desc) rnk2
from
(select case when rownum /2 = trunc(rownum/2) then 'able'
            when rownum /3 = trunc(rownum/3) then 'baker'
            when rownum /5 = trunc(rownum/5) then 'charlie'
            when rownum /7 = trunc(rownum/7) then 'delta'
            when rownum /11 = trunc(rownum/11) then 'echo'
            when rownum /13 = trunc(rownum/13) then 'foxtrot'
            when rownum /17 = trunc(rownum/17) then 'golf'
            else 'juliet' end label
from dual
connect by level < 1000) a  
group by label)
order by greatest(rnk,rnk2-.1) desc 

This way, the biggest slice gets put next to the smallest and so on, until the two middling ones are next to each other. And so there's a much reduced chance of label overlaps.