Friday, July 25, 2008

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.

7 comments:

Patrick Wolf said...

Gary,

very useful tip! I will reference it on my APEX blog.

Patrick

Patrick Wolf said...

Gary,

another thought, maybe you should explain that the inner connect by SELECT is just used to generate some data and doesn't have to do anything with your solution. A novice developer could be confused by that.

Patrick

SydOracle said...

Thanks Patrick,
Yes, the 'feature' is the use of the RANK and ORDER BY. The FROM/WHERE clause is purely for data generation.

Unknown said...

Hi Gary,
Thanks for your post regards to the pie chart, looks great. I am new to Apex and “pie chart” but I like to use your way (if you are okay with it)in my Apex App. One thing that I am having a difficulty is how to get my values from my table. Would you be able to help me? I do very much appreciate any help I can get.

SydOracle said...

I'm happy for anything I post on my blog to be used. That's why I post :)
Feel free to post questions as comments here. Otherwise (and for better formatting etc) I follow anything tagged with oracle on the stackoverflow.com forums.

Islam said...

Mr. Gary, in my company we're going to develop new application, and they intend to use oracle forms as we developed applications using it

I think the development trend now move to APEX, I am just would like to know why are you prefer APEX, as if I Knew I will convince them to use it

Thank you for your time and consideration

SydOracle said...

I see the main drawback of Forms is the license cost. There are advantages to it though, a much rich UI for a start.

It isn't necessary to go with one technology for all interfaces, so I can see Apex sitting alongside a Forms or Java/ADF application.