Ask a Jedi: Handling nulls in a chart

This post is more than 2 years old.

Nat asks:

Hey Ray, I have a cfchart question. http://crow.webapper.net/scratch/1221594752589.png

That is a cfchart output. I have multiple cfchartseries (data sets) displaying on that graph. Each data set has a different number of plot points. For instance, Fluid Milk has data up to 19 feet, Cheese all the way up to 26 feet and Cream Cheese only has data for 1 foot and 2 feet. The data in my cfquery recordset has null values for beyond 19 feet in milk and beyond 2 feet in cream chese, but the chart makes it look like the numbers are the same for all subsequent feet, not nulls.

I need the lines to disappear - just stop - after the data runs out. I.e., I need it to look like this:

http://crow.webapper.net/scratch/1221594752589_fixed.png (I photoshopped that picture.)

I have tried putting zeros in the extra feet where I have no data, but that just drops the lines down to the base of the X axis, which looks like crap since they connect up to the last, highest foot value.

I have also tried manually outputting the cfchartdata tags and doing a cfbreak when the data runs out - that is, making sure I only have as many cfchartdata tags for each series as I am sure I have data. That produces the output you see in the first image. It appears that cfchart is "pulling out" the data to the longest cfchartseries set of data.

So if you follow Nat's links, you will see what he is talking about. I'm sure we've all run into this before as well. Here is a simple script that demonstrates the issue:

<cfset q = queryNew("dept,year,sales","varchar,integer,integer")> <!--- generate random sales data ---> <cfloop index="y" from="1990" to="1998"> <cfscript> queryAddRow(q); if(y neq 1996) { querySetCell(q, "dept", "Gamma"); querySetCell(q, "year", y); querySetCell(q, "sales", randRange(80,220)); } else { querySetCell(q, "dept", "Gamma"); querySetCell(q, "year", y); } </cfscript> </cfloop> <cfdump var="#q#">

<cfchart chartWidth="400" chartHeight="400" title="Sales"> <cfchartseries type="line" query="q" itemColumn="year" valueColumn="sales" />

</cfchart>

As you can see, I set it up so that for one year, 1996, the sales value is blank. This produces the following chart (your chart may look different since I'm using random numbers):

Note that for 1996, the value is half way between 1995 and 1997. So what do we do? Not to sound like a broken record, but as I always say with these cfchart questions, you should go to the chart designer that is shipped with ColdFusion. Along with making it real easy to design a chart, it also lets you muck with the sample data as well. On a whim I decided to just erase one of the values. When I looked back at the design, it was perfect. The line didn't have any data for that part. I took a look around the design options and I found the value we need: isInterpolated. If this value is set to true then we get what we get in ColdFusion. ColdFusion must be setting this to true by default and doesn't - as far as I can see - provide a way around that.

Luckily we can just supply our XML to cfchart. Consider this version (I'm not repasting the fake query data):

<!--- style from webcharts ---> <cfsavecontent variable="style"> <?xml version="1.0" encoding="UTF-8"?> <frameChart isInterpolated="false"> </frameChart> </cfsavecontent>

<cfchart chartWidth="400" chartHeight="400" title="Sales" style="#style#"> <cfchartseries type="line" query="q" itemColumn="year" valueColumn="sales" />

</cfchart>

As you can see, I have an XML style with one value, isInterpolated="false". This produces the following chart:

Which now has the nice null values for your data. Note too that the look is a bit different. You will need to use the designer to specify other values if you want it to look exactly like the first chart above (but with null values).

So again - folks - please remember to use the chart designer. It is an awesome tool. It doesn't run on OSX, but I just RDP into my server to use it there.

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 Nat Papovich posted on 9/17/2008 at 7:29 PM

Sing praises for Ray! Thanks a million!

One note to you... Chart Designer does run on OS X. Open a terminal, type "bash" hit enter, open a Finder to /Applications/ColdFusion8/charting, then click-n-drag webcharts.sh into the bash term window. The sucker loads up and runs.

Comment 2 by Raymond Camden posted on 9/17/2008 at 7:31 PM

Heh cool tip! I didn't know that. Although honestly I can run RDP quicker than I can type all that. ;) Unless there is a way to shortcut those actions.

Comment 3 by Henry Ho posted on 9/17/2008 at 10:51 PM

Wow! I didn't even know there's this hidden gem in CF8. WebChart3D 5.1. That's weird, how come I've never see it mentioned anywhere in CFCHART doc: http://www.cfquickdocs.com/... ?

Other hidden gems (to some people) :
- ColdFusion Report Builder
- LiveCycle DS (express, 1 CPU only limit)
- Verity full text search (anyone used it before?)

anymore?

Comment 4 by Raymond Camden posted on 9/17/2008 at 10:57 PM

Hidden? Henry, you need to read the docs. ;) All of these are _fully_ explained in the docs. THe chart designer may not be, but the items you brought up certainly are.

Actually I just checked, and using the chart designer is also in the docs.

Comment 5 by Henry Ho posted on 9/18/2008 at 2:17 AM

:) IMO CFML doc on CFCHART should at least mention the chart designer, don't you think?

As for the other hidden gems, yes they're not all that hidden to us, that's why I had "Other hidden gems (to some people)"

ColdFusion Report Builder is a separate download. I bet many people don't even know such product exists if they don't use CF to generate report.

LiveCycle DS Express is quite hidden too. You can't find any LiveCycle DS settings in ColdFusion Administrator.

Comment 6 by Raymond Camden posted on 9/18/2008 at 10:09 PM

Eh? The docs do mention it. Don't just look at the reference, but the developer guide as well.

As for Report Builder and Livecycle, both are _fully_ covered in the developer guide.

I'd argue that if they are hidden, it is only to folks who don't take the time to read the docs. I will admit to not having read every page, but I do at least skim the TOC. ;)

Comment 7 by Steve posted on 10/22/2008 at 8:01 PM

Thank Raymond, this post really helped me out.

Comment 8 by Steve Leberman posted on 5/25/2010 at 5:15 PM

This is a great post. It almost solves my issue. I have graphs being displayed that contain null values that I woul like to skip as you have in this example. The main difference is that I have multiple sets of data being displayed on the same graph, so I am using the cfchartdata tag to represent each line on the graph. I am having trouble getting the null points to plot if I display multiple lines using the cfchartdata tag instead of just the cfchartseries tag with the query in it. Any thoughts??

Comment 9 by Steve Leberman posted on 5/25/2010 at 5:40 PM

I actually came up with my own solution, not sure how elegant but it works (o: Maybe this will help someone... or maybe someone can show me how to do more elegant...lol..

<cfsavecontent variable="style">
<?xml version="1.0" encoding="UTF-8"?>
<frameChart isInterpolated="false"></frameChart>
</cfsavecontent>
<cfoutput query="QGraphSurvey" group="evalmonth">
<cfquery dbtype="query" name="singlerow">
select questionnum, avgscore, evalmonth
from QGraphSurvey
Where evalmonth = '#evalmonth#'
</cfquery>
<cfchartseries type="line" query="singlerow" itemColumn="questionnum" valueColumn="avgscore" serieslabel="#dateformat(evalmonth, "mmm dd,yyyy")#" />
</cfoutput>

Comment 10 by Daniel Whiddon posted on 5/27/2011 at 3:23 AM

I have located a much more elegant solution if you are using multiple series.

<cfsavecontent variable="chartStyle">
<?xml version="1.0" encoding="UTF-8"?>
<frameChart isInterpolated="false">
<yAxis scaleMin="0"/>
</frameChart>
</cfsavecontent>

this handles the display issues you face when you just use isInterpolated="false" as it will still show all the data in relation to zero