I want to make a simple cfchart that outputs two values in a bar chart by year.
There i one table for the data called Occurrences.
Hre are the three fields I am trying to use.
OccurrenceNumber (primary key)
The DateofOccurrence is the date of the event and is a required field.
The DateOccurrenceClosed is only filled in when the event is closed.
The chart is trying to show people all Occurrences by year, and how many are still open / pending.
I got it to work (partially) by using two queries.
This query gets a count of all the occurrences
<cfquery Name="OccTotalChart" datasource="#DSN#">
COUNT(OccurrenceNumber) As OccTotal,
datepart(year,DateofReport) as YearsTotal
GROUP BY datepart(year,DateofReport)
ORDER BY datepart(year,DateofReport)
This query gets counts for the pending occurrences
<cfquery Name="OccOpenChart" datasource="#DSN#">
COUNT(OccurrenceNumber) As OccOpen,
(CASE WHEN Count(OccurrenceNumber) IS NULL THEN 0 ELSE Count(OccurrenceNumber) END) As OccTotal,
datepart(year,DateofReport) as YearsOpen
datepart(year,DateOccurrenceClosed) IS NULL
GROUP BY datepart(year,DateOccurrenceClosed), datepart(year,DateofReport)
Then I use this for the chart
<cfchart format="flash" chartwidth="700" chartheight="500" showlegend="yes" xaxistitle="Year of Report">
<cfchartseries type="bar" query="OccTotalChart" itemcolumn="YearsTotal" valuecolumn="OccTotal" serieslabel="Total" paintstyle="shade" />
<cfchartseries type="bar" query="OccOpenChart" itemcolumn="YearsOpen" valuecolumn="OccOpen" serieslabel="Open" paintstyle="shade" / >
It works great except... There are 10 years worth of data. The first six do not have any open occurrences, just the last four. The first year that has open occurrences has 2 open. The prior 6 years should all be 0, but all list as 2.
If I make two separate charts they work fine, I just need to have the prior six years not show on the combined chart or to show as 0.
Pointers / suggestions / corrections are appreciated.