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>
Archived Comments
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?
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. ;)
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().
How could I? The blog <em>is</em> called Coldfusion Jedi not SQL :)
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?
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.
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?
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?
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.
I don't think I'd recommend doing it globally. I'd do it in a style instead.
http://www.coldfusionjedi.c...
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.
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>
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.
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.
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.
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">
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.
It may be usable for Flash based charts. What happens if you try the PNG version?
same thing with PNG format , but i could not test it with flas because it is erroring out for some reason.
Well, you may have me. You can always use JavaScript to add alt-Text to a region by hand.
yeah i used my own html tag for the titel and added the tolltip to it.
Thank Raymond to your help,i really appreciated.
Ray,
Isn't there something that can be done in cfchart so that It auto creates or skips the missing values in the series?
Yep - you want this: http://www.raymondcamden.co...
But to be clear, that charting engine is ancient. You should use something newer.