Stupid CFCHART Trick of the Week

This post is more than 2 years old.

Earlier yesterday morning I blogged about cfchart and a poster asked an interesting followup question:

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?

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: <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>

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 Will B. posted on 2/8/2008 at 8:47 PM

Ray -- are you an Anne Rice fan?

Comment 2 by Andrew posted on 2/8/2008 at 8:56 PM

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.

Comment 3 by Raymond Camden posted on 2/8/2008 at 9:09 PM

Heck no. My dog writes better than her. I'll give her props for bringing something new to the genre, but still. Ick.

Comment 4 by Richard Dillman posted on 2/9/2008 at 2:18 AM

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.

Comment 5 by Raymond Camden posted on 2/9/2008 at 2:23 AM

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?

Comment 6 by Magnus posted on 2/10/2008 at 7:56 AM

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?

Comment 7 by Raymond Camden posted on 2/10/2008 at 6:54 PM

Magnus - I don't think so. But as I've blogged here recently, there are quite a few nice alternatives to cfchart.

Comment 8 by Dylan posted on 11/4/2009 at 8:32 PM

Ray,

Any chance the new CF9 has an updated WebCharts3D engine that resolves this? Or should we start exploring alternatives?

Regards,

Dylan

Comment 9 by Raymond Camden posted on 11/4/2009 at 8:57 PM

As far as I know (no proof of this), the charting engine did NOT change in any way in ColdFusion 9.

Comment 10 by Dylan posted on 11/4/2009 at 10:19 PM

Yep, just downloaded CF9 to confirm. CF9 is still using WebCharts3D 5.1.