Earlier yesterday morning I blogged about cfchart and a poster asked an interesting followup question:
His name was Magnus, and there was no way I could ignore a question from someone with a cool name like that. First let me demonstrate what he is talking about. I created some data that represented books and their ranks over a five week period: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?
<cfscript>
function addIt(week,book,position) {
queryAddRow(data);
querySetCell(data, "week", arguments.week);
querySetCell(data, "book", arguments.book);
querySetCell(data, "position", arguments.position);
}
data = queryNew("week,book,position","integer,varchar,integer");
addIt(1,"Alpha",1);
addIt(2,"Alpha",2);
addIt(3,"Alpha",1);
addIt(4,"Alpha",4);
addIt(5,"Alpha",5);
addIt(1,"Beta",3);
addIt(2,"Beta",1);
addIt(3,"Beta",2);
addIt(4,"Beta",2);
addIt(5,"Beta",1);
addIt(1,"Gamma",5);
addIt(2,"Gamma",4);
addIt(3,"Gamma",3);
addIt(4,"Gamma",1);
addIt(5,"Gamma",2);
</cfscript>
The UDF is there simply to save me some typing. I then created the chart:
<cfchart show3d="false" >
<cfloop index="book" list="#valueList(data.book)#">
<cfquery name="weekdata" dbtype="query">
select *
from data
where book = <cfqueryparam cfsqltype="cf_sql_varchar" value="#book#">
order by week
</cfquery>
<cfchartseries type="line" query="weekdata" itemcolumn="week" valuecolumn="position" serieslabel="#book#">
</cfloop>
</cfchart>
The result is a bit hard to read in static JPG format, but has nice roll overs in Flash. When I used a legend, it got a bit screwy. I'll come back to that. But as you can see, the grid starts with lower numbers first:
Once again I turned to the handy dandy chart editor. After a bit of clicking I finally found what I wanted under YAxis (duh). Clicking on this setting gives you a lot of options for the YAxis, including a "Is Reversed" tick. The resulting XML gives you:
<frameChart is3D="false">
<yAxis isReversed="true">
</yAxis>
<legend isVisible="false" showColumnLegend="false">
<decoration style="None"/>
</legend>
</frameChart>
Note - I did tweak a bit more, like turning off the column legend which was turned back on by the XML. This gives you the result you want - kinda:
As you can see, we still have a range of 1-5. I tried like heck to fix this. There is even a range option in the chart editor, but the data seems to override this. You can supply a gridlines of 5. That does give you the right number of lines, but check out the result:
Not what I'd call optimal, but I'll take a look at this a bit more later to see if I can figure this last step out. Here is the complete code of the latest version:
<cfscript>
function addIt(week,book,position) {
queryAddRow(data);
querySetCell(data, "week", arguments.week);
querySetCell(data, "book", arguments.book);
querySetCell(data, "position", arguments.position);
}
data = queryNew("week,book,position","integer,varchar,integer");
addIt(1,"Alpha",1);
addIt(2,"Alpha",2);
addIt(3,"Alpha",1);
addIt(4,"Alpha",4);
addIt(5,"Alpha",5);
addIt(1,"Beta",3);
addIt(2,"Beta",1);
addIt(3,"Beta",2);
addIt(4,"Beta",2);
addIt(5,"Beta",1);
addIt(1,"Gamma",5);
addIt(2,"Gamma",4);
addIt(3,"Gamma",3);
addIt(4,"Gamma",1);
addIt(5,"Gamma",2);
</cfscript>
<cfsavecontent variable="style">
<frameChart is3D="false">
<yAxis isReversed="true">
</yAxis>
<legend isVisible="false" showColumnLegend="false">
<decoration style="None"/>
</legend>
</frameChart>
</cfsavecontent>
<cfchart show3d="false" style="#style#" gridlines="5">
<cfloop index="book" list="#valueList(data.book)#">
<cfquery name="weekdata" dbtype="query">
select *
from data
where book = <cfqueryparam cfsqltype="cf_sql_varchar" value="#book#">
order by week
</cfquery>
<cfchartseries type="line" query="weekdata" itemcolumn="week" valuecolumn="position" serieslabel="#book#">
</cfloop>
</cfchart>
Archived Comments
Ray -- are you an Anne Rice fan?
It may be easier to figure out if Magnus could provide an example of what a completed chart would look like. I'm having trouble "visualizing" what the end result should be. I used cfchart extensively while building a dashboard site so I can understand some of the challenges using the tag. Once people started showing me what they want the chart to look like, it became much easier to code.
Heck no. My dog writes better than her. I'll give her props for bringing something new to the genre, but still. Ick.
I think you will get what your after if you change your yAxis to
<yAxis scaleMin="0" scaleMax="10" labelCount="11" isReversed="true"/>
and remove gridlines="5" from the cfchart tag.
Gridlines is definately overriding your XML style.
Also dont forget that zero is a marker too so label count is always 1 more than the number of markers you want.
That's better - and it follows the range I want. But when I try to get rid of the 0 I get odd results. I'd like 1 to be the top of the chart. Possible?
Basically, what Ray is aiming at is what I am after.
I actually think that the chart 2 in Ray's post would look ideal if you hide the '0' in the y-axis. It looks a little funny when the data points bump against the top of the chart.
Also, can you manipulate the chart style details in Bluedragon?
Magnus - I don't think so. But as I've blogged here recently, there are quite a few nice alternatives to cfchart.
Ray,
Any chance the new CF9 has an updated WebCharts3D engine that resolves this? Or should we start exploring alternatives?
Regards,
Dylan
As far as I know (no proof of this), the charting engine did NOT change in any way in ColdFusion 9.
Yep, just downloaded CF9 to confirm. CF9 is still using WebCharts3D 5.1.