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:

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

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

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:

When run, we now get the correct chart:

And lastly, here is the complete template.