How do I use cfchart area type to show comments per time period

Question

I'm trying to create an area chart using cfchart, but not sure how to go about it. I'm doing a query of all comments in a table and want to show time period (days or months on x-axis) and output number of comments as they are created. Here's the comment query:

<cfquery name="qGetComments" datasource="#session.datasource#">
SELECT PersonFirstName
       , PersonLastName
       , PersonZip
       , CommentCode
       , refTopicID
       , tbComment.CommentID
       , tbComment.CreateDateTime
FROM tbPerson 
JOIN tbComment ON tbPerson.PersonID = tbComment.PersonID
</cfquery>

From here, I'm not sure the values to put in the cfchart to output the area chart the way I want. Something like this?

<cfchart chartWidth="400" show3D="yes"> 
    <cfchartseries type="area" query="qGetComments" 
       valueColumn="CommentID" 
       itemColumn="CreateDateTime" /> 
</cfchart>

(Edit) *show comments per time period* Then you need an aggregate query. Specifically one that incorporates a `count(...)` and groups by some date interval you select (by day, month, ecetera). That will involve date functions, which are often database specific. So 1) what date interval ? 2) What is your DBMS and version? — Leigh, Nov 03 '13 at 02:13
You might also want to look at using the native ZingChart library instead of `cfchart`. I have found that using the native library is often times easier than using `cfchart` in ColdFusion 10. The ZingChart website has a 'chart builder' that allows you to generate the JSON for a certain type of chart with a certain type of formatting. You can take that JSON and use it as a foundation for what you need to do. — Scott Stroz, Nov 03 '13 at 14:17
Hey @leigh I'm using SQL Server. Grouping by day would be perfect. Thanks for helping! — user1431633, Nov 03 '13 at 21:18
Which version, 2005, 2008, ..? Though there are standards, syntax varies by version as well (so it is always good to include both in question tags). — Leigh, Nov 03 '13 at 21:53

score 2 · Accepted Answer · answered Nov 04 '13 at 00:13

A simple way to group the records by date in Server 2008+ above is cast the datetime column to a date. Then use simply use the "CommentDate" column as the chart item and "NumOfComments" for the value:

SELECT CONVERT(DATE, tbComment.CreateDateTime) AS CommentDate
      , COUNT(*) NumOfComments
FROM  tbPerson INNER JOIN tbComment ON tbPerson.PersonID = tbComment.PersonID
GROUP BY CONVERT(DATE, tbComment.CreateDateTime);

You can make the chart labels a little more user friendly by formatting the date within the SQL (see cast and convert). Just be sure to order the results by the date value, and not the formatted string. Otherwise, the chart labels may not appear in proper chronological order (it is a common mistake):

--- format as:  Mon dd, yyyy
SELECT CONVERT(VARCHAR, tbComment.CreateDateTime, 107) AS CommentDateString
      , COUNT(*) NumOfComments 
FROM  tbPerson INNER JOIN tbComment ON tbPerson.PersonID = tbComment.PersonID 
GROUP BY CONVERT(VARCHAR, tbComment.CreateDateTime, 107) 
ORDER BY CONVERT(DATE, CONVERT(VARCHAR, tbComment.CreateDateTime, 107), 107)

SQLFiddle

As an aside, if HTML charts are an option, you might look into Scott's suggestion. Generally CFChart is fine for simple cases, but once you start to customize the appearance (which invariably happens), you are usually better off using the underlying tools directly.

How do I use cfchart area type to show comments per time period

1 Answers1