Update to my Pie chart with lots of data post

This post is more than 2 years old.

Two days ago I wrote about a pie chart that had too much data. As you can see in the blog post, the amount of data and size of the pie chart causes the labels to run in together. It's also just a bit too much for a pie. As one commenter said, a bar char would be better, or as another said, it may make sense to combine some of the smaller slices into an "Other" slice. In this post I do exactly that. Given a random query I'm going to see if I have too many data points and if so - I'll combine them into a new slice called Other. I'm going to begin with the assumption that q, our query, already exists. At the very end of the blog post I'll put up the entire code template in case you want to try this yourself.

First - I chose 6 as the max number of slices I wanted. You can go higher or lower of course.

<cfif q.recordCount gt 6> <!--- number of items we want to strip/join ---> <cfset otherCount = q.recordCount - 5>

This code block shows my initial check to see if I have too much data. If I do, I create a variable called otherCount. This is how many rows of data that will be cominbed into the other slice. Notice it is 5, not 6. Remember that I'm adding a new slice, Other. So I want to leave 6-1 slices of data. Now let's get the data that will fall into the Other block.

<cfquery name="getOthers" dbtype="query" maxrows="#otherCount#"> select fruit, total from q order by total asc </cfquery>

Nothing crazy here - just a query of query. I sort by total asc as I want to combine the smallest items. Now let's get their total:

<!--- total of Other ---> <cfset otherTotal = arraySum(listToArray(valueList(getOthers.total)))>

And then let's get the query minus the rows we took out.

<!--- query without others ---> <cfquery name="newData" dbtype="query"> select * from q where fruit not in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#valueList(getOthers.fruit)#" list="true">) </cfquery>

Now we need to add in our new slice:

<!--- add Other ---> <cfset queryAddRow(newData)> <cfset querySetCell(newData, "fruit", "Other")> <cfset querySetCell(newData, "total", otherTotal)>

And finally - copy over newData:

<cfset q = newData> </cfif>

And here is the final result - compare it to the screen shots in the previous post:

Neat! So here is the complete template. Enjoy.

<cfset q = queryNew("fruit,total","cf_sql_varchar,cf_sql_integer")> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","apples")> <cfset querySetCell(q, "total",112)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","oranges")> <cfset querySetCell(q, "total",304)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","bananas")> <cfset querySetCell(q, "total",0)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","pears")> <cfset querySetCell(q, "total",0)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","grapes")> <cfset querySetCell(q, "total",16)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","strawberries")> <cfset querySetCell(q, "total",80)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","plums")> <cfset querySetCell(q, "total",48)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","pineapples")> <cfset querySetCell(q, "total",32)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","blueberries")> <cfset querySetCell(q, "total",16)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","raspberries")> <cfset querySetCell(q, "total",32)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","apricots")> <cfset querySetCell(q, "total",256)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","tangerines")> <cfset querySetCell(q, "total",705)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","cherries")> <cfset querySetCell(q, "total",1)> <cfset queryAddRow(q)> <cfset querySetCell(q, "fruit","peaches")> <cfset querySetCell(q, "total",0)>

<cfif q.recordCount gt 6> <!--- number of items we want to strip/join ---> <cfset otherCount = q.recordCount - 5> <cfquery name="getOthers" dbtype="query" maxrows="#otherCount#"> select fruit, total from q order by total asc </cfquery> <!--- total of Other ---> <cfset otherTotal = arraySum(listToArray(valueList(getOthers.total)))>

&lt;!--- query without others ---&gt;	
&lt;cfquery name="newData" dbtype="query"&gt;
select	*
from	q
where	fruit not in (&lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#valueList(getOthers.fruit)#" list="true"&gt;)
&lt;/cfquery&gt;

&lt;!--- add Other ---&gt;
&lt;cfset queryAddRow(newData)&gt;
&lt;cfset querySetCell(newData, "fruit", "Other")&gt;
&lt;cfset querySetCell(newData, "total", otherTotal)&gt;

&lt;cfset q = newData&gt;

</cfif>

<cfchart chartheight="500" chartwidth="500"> <cfchartseries type="pie" query="q" itemcolumn="fruit" valuecolumn="total" datalabelstyle="pattern" > </cfchart>

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Nialls Blog posted on 1/26/2011 at 7:01 PM

Hi Ray,
Just a thought, but I noticed in your last few posts you've been creating a lot of dummy query data...couldn't you use something like Hal Helms original QuerySim or Todd's version - http://www.web-rat.com/down... ?

Cheers,
Niall.

Comment 2 by Raymond Camden posted on 1/26/2011 at 7:26 PM

Dude - scary. I had the same thought. ;) I actually build an extension for CFB that would generate data, but it was randomized. But yeah - using querysim would be better. My only worry would be that I try not to do anything 'extra' in a blog post that may confuse or distract the reader.

Comment 3 by Nialls Blog posted on 1/26/2011 at 8:26 PM

Ha - proud to have shared a nano-second of similar thought as you ;-) Also, I completly agree with the fundamental point of keeping blog posts concise!

Comment 4 by Steve Bryant posted on 1/27/2011 at 3:44 AM

Ray,

This is a really cool idea. As I was reading it, I thought it would be cool if you had the option to limit either by maximum number of rows or by minimum percentage value.

So, I threw together a UDF to do just that. Here would be the code to limit by no more than 8 rows and with a minimum of 6 percent.

q = QueryWithOthers(q,'fruit','total',8,6);

http://www.bryantwebconsult...

Comment 5 by Raymond Camden posted on 1/28/2011 at 7:21 AM

Nice, Steve!