Ask a Jedi: CFCHART Question

This post is more than 2 years old.

Frank asks:

I have a question about how to use ColdFusion Chart.

I get data from a database table and wanted to show it on the chart. A typical data would be like this:

NAME             MONTH          COST

name1 2 100 name1 3 80 name2 1 60 name3 2 30

I would like to use name on X-axis and cost as Y-axis on a barchart. However, since I have two records of name1. There is only one name1 showing up. Do you have any experience of fixing this problem?

First off - what you really have here isn't one simple set of data, but a series of data. For each month, you have a set of users and sales. CFCHART supports multiple series of data per chart, so right away we can support exactly what you want. However you will run into a little issue when you try to use your data. Let me show you the code first, then I'll show you how I fixed it.

First off, I create a query of data:

<cfset data = queryNew("name,month,cost")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name1")> <cfset querySetCell(data,"month", "2")> <cfset querySetCell(data,"cost", "100")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name1")> <cfset querySetCell(data,"month", "3")> <cfset querySetCell(data,"cost", "80")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name2")> <cfset querySetCell(data,"month", "1")> <cfset querySetCell(data,"cost", "60")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name3")> <cfset querySetCell(data,"month", "2")> <cfset querySetCell(data,"cost", "30")>

I know that my series will be by month, so I then ask for all the distinct months:

<!--- get months ---> <cfquery name="getMonths" dbtype="query"> select distinct [month] from data order by [month] asc </cfquery>

Notice I escape month since it is a reserved word in query of query. Now I create my chart:

<cfchart title="Sales" show3d="true" showLegend="true"> <cfloop query="getMonths"> <cfquery name="databymonth" dbtype="query"> select * from data where [month] = <cfqueryparam cfsqltype="cf_sql_integer" value="#month#"> </cfquery> <cfchartseries type="bar" itemColumn="name" valueColumn="cost" query="databymonth" seriesLabel="Sales for Month #month#"> </cfchartseries> </cfloop> </cfchart>

Notice that I loop over my getMonths query, and for each month, I get a set of data for that month. I then supply that query to the chartseries tag. So far so good, but what happens when you view the chart?

That can't be right. In cases where we don't have data, the bars don't match at all. Unfortunately, I've seen this before. The cfchart support in ColdFusion sometimes seems to get confused by missing values. The only way to fix this is to 'complete' the data and ensure you always have a value for each month and each user. This could be done in SQL, but since my data was fake, I fixed it manually like so:

<cfloop query="getMonths"> <cfset thisMonth = month> <cfloop query="getNames"> <!--- do we have data for name X on month Y? ---> <cfquery name="hasData" dbtype="query"> select cost from data where [month] = <cfqueryparam cfsqltype="cf_sql_integer" value="#thisMonth#"> and name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#name#"> </cfquery> <cfif hasData.recordCount is 0> <cfset queryAddRow(data)> <cfset querySetCell(data,"name", name)> <cfset querySetCell(data,"month", thisMonth)> <cfset querySetCell(data,"cost", "0")> </cfif> </cfloop> </cfloop>

When run, we now get the correct chart:

And lastly, here is the complete template.

<cfset data = queryNew("name,month,cost")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name1")> <cfset querySetCell(data,"month", "2")> <cfset querySetCell(data,"cost", "100")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name1")> <cfset querySetCell(data,"month", "3")> <cfset querySetCell(data,"cost", "80")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name2")> <cfset querySetCell(data,"month", "1")> <cfset querySetCell(data,"cost", "60")>

<cfset queryAddRow(data)> <cfset querySetCell(data,"name", "Name3")> <cfset querySetCell(data,"month", "2")> <cfset querySetCell(data,"cost", "30")>

<!--- get months ---> <cfquery name="getMonths" dbtype="query"> select distinct [month] from data order by [month] asc </cfquery>

<!--- Begin Fixing Data ---> <cfquery name="getNames" dbtype="query"> select distinct name from data order by name asc </cfquery>

<cfloop query="getMonths"> <cfset thisMonth = month> <cfloop query="getNames"> <!--- do we have data for name X on month Y? ---> <cfquery name="hasData" dbtype="query"> select cost from data where [month] = <cfqueryparam cfsqltype="cf_sql_integer" value="#thisMonth#"> and name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#name#"> </cfquery> <cfif hasData.recordCount is 0> <cfset queryAddRow(data)> <cfset querySetCell(data,"name", name)> <cfset querySetCell(data,"month", thisMonth)> <cfset querySetCell(data,"cost", "0")> </cfif> </cfloop> </cfloop>

<cfchart title="Sales" show3d="true" showLegend="true"> <cfloop query="getMonths"> <cfquery name="databymonth" dbtype="query"> select * from data where [month] = <cfqueryparam cfsqltype="cf_sql_integer" value="#month#"> </cfquery> <cfchartseries type="bar" itemColumn="name" valueColumn="cost" query="databymonth" seriesLabel="Sales for Month #month#"> </cfchartseries> </cfloop> </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 Emilie posted on 2/7/2008 at 6:42 PM

Thanks Ray. This is very topical for me as I was about to do some cfchart work. Out of curiosity how would you handle the missing values with SQL? Would you use the ISNULL function?

Comment 2 by Raymond Camden posted on 2/7/2008 at 7:03 PM

Would you hate me if I said I was something of a SQL noob? I _believe_ that is how I'd do it - or with CASE. For sure I'd stumble around a bit. ;)

Comment 3 by todd sharp posted on 2/7/2008 at 7:06 PM

Forgive me for not reading the whole post - but - isNull() (or coallesce()) can be used for null values. Either that or wrap your chart values in val().

Comment 4 by Emilie posted on 2/7/2008 at 10:02 PM

How could I? The blog <em>is</em> called Coldfusion Jedi not SQL :)

Comment 5 by Magnus posted on 2/8/2008 at 3:45 AM

I have another cfchart question (Maybe I am cheating by tacking it on here). I tried to chart some data based on ranked lists, like a bestseller list. I wanted to chart the position of a "book" on the list over the weeks. On the list, 1 is higher than 2 is higher than 10, so when it charted it came out upside down. I didn't see anything in the cfchart attributes that allowed me to invert this.

I have to admit, it wasn't a crucial need to chart this so I moved on to other problems, but it has sort of bugged me since then. Was I missing something easy?

Comment 6 by Raymond Camden posted on 2/8/2008 at 4:00 AM

Ahah, I think I found it. If you use the chart editor, there is an option to reverse the yaxis. It seems to reverse the bars though as well.

If you want - send me some data, in the form of a fake query like above, and I'll try to get it working.

Comment 7 by Scott posted on 2/27/2008 at 9:33 PM

With cfchart, you can do this by looping through a distinct list of months, but if you used the same dataset, how could you chart this in Flex?

Comment 8 by Josh posted on 3/28/2008 at 10:33 PM

Running into this "CFChart gets confused issue". Not all of my datasets have values for each item. My query happens to be a predefined view that uses alot of SQL UDF's.

Just wanted to state that I think it stinks that CF cant handle a dataset for multiple series unless each set is the same and has values. Anyone know if they evolved in CF8 to solve this problem?

Comment 9 by Don posted on 2/25/2010 at 1:26 AM

For missing data there is a setting in the default chart that cures this problem.
I had to go here
C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\charting\styles
But just search for the charting folder if you need to.
There you will find the file default.xml which you can open for editing. Find this line :
<frameChart autoAdjust="false" is3D="false" isInterpolated="true"> and change the isInterpolated to false.

Comment 10 by Raymond Camden posted on 2/25/2010 at 2:48 AM

I don't think I'd recommend doing it globally. I'd do it in a style instead.

http://www.coldfusionjedi.c...

Comment 11 by Don posted on 2/25/2010 at 3:14 AM

Now how would you do this so it shows it grouped by month instead of employee? That's what I have to do. Each month how many sales each employee does. Not working for me.

Comment 12 by Raymond Camden posted on 3/1/2010 at 9:52 PM

I got it to work by modifying this at the bottom:

<cfchart title="Sales" show3d="true" showLegend="true">
<cfloop query="getNames">
<cfquery name="databyemp" dbtype="query">
select *
from data
where name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#name#">
</cfquery>
<cfdump var="#databyemp#" output="/Users/ray/Desktop/test.html" format="html">
<cfchartseries type="bar" itemColumn="month" valueColumn="cost" query="databyemp" seriesLabel="Sales for Name #name#">
</cfchartseries>
</cfloop>

</cfchart>

Comment 13 by Lina Haddad posted on 2/4/2012 at 12:20 AM

i need your help cfchart please ,, i have a chart that has a title (charttitel) and i am seeing some examples that have the ability to show onhover on the title it called altText...
i looked in WebChart3D and couldn't figure it out,also one more thing please i see the autoAdjust property there and i don't see it in the xml file ,do you know what is it?
thank you.

Comment 14 by Raymond Camden posted on 2/4/2012 at 12:28 AM

To your first question, there is a root level altText property in the chart editor. I assume that isn't altText for the title, but alt text for the entire Flash or JPG chart.

I also see autoadjust in the editor too.

Comment 15 by Lina Haddad posted on 2/4/2012 at 2:03 AM

thanks you ,about atlText i saw it in this link http://stackoverflow.com/qu... if you scroll down to the chart and have your mouse over the title(Cluster Referral....)you will see tooltip (alt text) that i want to do in my app but i don't how..
and about my second questin autoAdjust ,i see it in the editor but i don't see it in the xml file ,can you please paste a copy of the xml that include the auto adjust in it..
thanks again.

Comment 16 by Raymond Camden posted on 2/4/2012 at 2:24 AM

The reason you don't see autoAdjust in the XML is that it defaults to on. If you turn it off, you will see it in the XML.

As for alt Text, did you try it? Specify a value for it and it shows up in XML. For me, I saw it like so:

<frameChart is3D="false" altText="mmoo">

Comment 17 by Lina Haddad posted on 2/4/2012 at 3:03 AM

Thank you Raymond ,i got the autoAdjust to work,
about the altText ,i applied it to both my chart and to the WebChart3D chart ,it didn't give an error but it doesn't show up when the mouse is over the chart or over the title box,
does it requir any oth settings?
thank you.

Comment 18 by Raymond Camden posted on 2/4/2012 at 3:09 AM

It may be usable for Flash based charts. What happens if you try the PNG version?

Comment 19 by Lina Haddad posted on 2/4/2012 at 3:20 AM

same thing with PNG format , but i could not test it with flas because it is erroring out for some reason.

Comment 20 by Raymond Camden posted on 2/5/2012 at 3:06 AM

Well, you may have me. You can always use JavaScript to add alt-Text to a region by hand.

Comment 21 by Lina Haddad posted on 2/7/2012 at 1:46 AM

yeah i used my own html tag for the titel and added the tolltip to it.
Thank Raymond to your help,i really appreciated.

Comment 22 by Tim Rubel posted on 9/28/2014 at 9:26 PM

Ray,

Isn't there something that can be done in cfchart so that It auto creates or skips the missing values in the series?

Comment 23 by Raymond Camden posted on 9/28/2014 at 10:14 PM

Yep - you want this: http://www.raymondcamden.co...

But to be clear, that charting engine is ancient. You should use something newer.