cfchart not showing zero if query is null

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)
DateofOccurrence
DateOccurrenceClosed


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#">
SELECT
   COUNT(OccurrenceNumber) As OccTotal,
   datepart(year,DateofReport) as YearsTotal
FROM
   OccurrenceDateTracking
GROUP BY datepart(year,DateofReport)
ORDER BY datepart(year,DateofReport)
</cfquery>

This query gets counts for the pending occurrences
<cfquery Name="OccOpenChart" datasource="#DSN#">
SELECT
   COUNT(OccurrenceNumber) As OccOpen,
   (CASE WHEN Count(OccurrenceNumber) IS NULL THEN 0 ELSE Count(OccurrenceNumber) END) As OccTotal,
   datepart(year,DateofReport) as YearsOpen
FROM
   OccurrenceDateTracking
WHERE
   datepart(year,DateOccurrenceClosed) IS NULL
GROUP BY datepart(year,DateOccurrenceClosed), datepart(year,DateofReport)
</cfquery>

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" / >
    </cfchart>

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.

Cliff
ctreevesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Firstly, are you sure you are getting the correct data with the query as it appears that you are are filtering on:
datepart(year,DateOccurrenceClosed) IS NULL

If datepart(year,DateOccurrenceClosed) IS NULL, then you will not get the count of the closed items in the total count.

Try this (code snippet) and then if you are missing years of data, you can write a table with all the years that you want in report and UNION it in with this query.

i.e.

SELECT ...
UNION
SELECT 0, 0, 1998
UNION
SELECT 0, 0, 1999
UNION
SELECT 0, 0, 2000
...

Hopefully you get the drift.  
<cfquery Name="OccOpenChart" datasource="#DSN#">
SELECT SUM(OccTotal) AS OccTotal
, SUM(OccOpen) AS OccOpen
, OccYear
FROM (
SELECT COUNT(OccurrenceNumber) As OccTotal, 0 As OccOpen
, Year(DateofReport) as OccYear
FROM OccurrenceDateTracking
GROUP BY Year(DateofReport)
UNION ALL
SELECT 0, COUNT(OccurrenceNumber)
, Year(DateOccurrenceClosed)
FROM OccurrenceDateTracking
WHERE DateOccurrenceClosed IS NULL
GROUP BY Year(DateOccurrenceClosed)
) derived
GROUP BY OccYear
ORDER BY OccYear
</cfquery>

Open in new window

0
azadisaryevCommented:
shouldn't you be using OccTotal in your second chart series' valuecolumn? OccTotal is the one you set to 0 is count(OccurrenceNumber) is null...

if that does not help, then there may be some interpolation going on - CF does it by default. to change this behaviour, edit the default.xml file in {cf_root}\charting\styles folder and change isInterpolated attribute in this line <frameChart autoAdjust="false" is3D="false" isInterpolated="true"> (it is the second line in the file) to 'false'.

if you do not want to (or cannot) edit the default.xml file, copy it to the same folder as your page with the chart, edit that file, and point to it in your <cfchart>'s STYLE attribute.

hth
0
ctreevesAuthor Commented:
Thank you for the quick replies!

I have two queries, the first gets the count, the second gets the open count.
I presumed that if the open count was null or 0 for a year, then it would show 0, but it's showing the last positive value (2 in this case).

I have a test page here (http://www.jptm.info/testing/stats.cfm) I'll need to remove it soon.
This is getting the data from the database, so you can see in the table below that there are definitely years missing for the open records.

Can a table with 0 values be programatically created if the year has no open Occurrences?  Since it's live data, I don't want to review it and update a table.

As for updating the xml file, I am on a shared server and do not have access to it.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

azadisaryevCommented:
1) in your second query you have OccTotal column (defined with the CASE statement) that should return 0 if no closed occurrences are found, but in your chart's second series you are using OccOpen column instead, which is NULL if no occurrences are found..

2) if you are on a shared server, just edit the default.xml file from your local cf install as i suggested previously, save it as some other filename (i.e. mydefault.xml), upload it to your chart page's folder on your shared server and add style="mydefault.xml" to your <cfchart> tag.

hth
0
Kevin CrossChief Technology OfficerCommented:
Try my query that uses one sql statement to get both values.  It should is essentially building in open count years to match the total count years.  You should end up with all the years listed with zeros instead of being missing from report at all...

0
azadisaryevCommented:
alternatively to editing default.xml file, here's a great tip: http://www.coldfusionjedi.com/index.cfm/2008/9/17/Ask-a-Jedi-Handling-nulls-in-a-chart
0
ZvonkoSystems architectCommented:
Thanks azadisaryev for the link but there is no need for editing the default.xml.
As demonstrated on that page you can create your own #style# xml at runtime.
Check this:

<cfsavecontent variable="style">
<?xml version="1.0" encoding="UTF-8"?>
<frameChart autoAdjust="false" is3D="false" isInterpolated="false">
		<frame xDepth="6" yDepth="6"  outline="#333333" lightColor="white"
			leftAxisPlacement="Front" rightAxisPlacement="Front" stripColor="#CCCCCC"/>
		<xAxis>
			<labelStyle isHideOverlapped="true" orientation="Horizontal"/>
			<titleStyle font="Arial-12-bold" isMultiline="false"/>
		</xAxis>
		<yAxis scaleMin="0">
			<titleStyle font="Arial-12-bold"/>
			<dateTimeStyle majorUnit="Year" minorUnit="Month"/>
			<labelFormat style="Pattern" pattern="#,##0.########"/>
		</yAxis>
		<yAxis2>
			<titleStyle font="Arial-12-bold"/>
		</yAxis2>
		<topYAxis>
			<titleStyle font="Arial-12-bold"/>
		</topYAxis>
		<topYAxis2>
			<titleStyle font="Arial-12-bold"/>
		</topYAxis2>
		<dataLabels foreground="black"/>
		<legend isVisible="false" showColumnLegend="true">
			<decoration style="None"/>
		</legend>
		<elements action="" shape="Area" drawOutline="false">
			<morph morph="Grow"/>
			<series index="0">
				<paint color="#E48701"/>
			</series>
			<series index="1">
				<paint color="#A5BC4E"/>
			</series>
		</elements>
		<popup background="#C8FFFFFF" foreground="#333333"/>
		<paint paint="Plain"/>
		<insets left="5" top="5" right="5" bottom="5"/>
</frameChart>
</cfsavecontent>
 
<cfchart format="flash" chartwidth="700" chartheight="500" showlegend="yes" xaxistitle="Year of Report" style="#style#">
    <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" / >
</cfchart>

Open in new window

0
ZvonkoSystems architectCommented:
If the colors are not so important to you you can reduce the xml to this:
<cfsavecontent variable="style">
<?xml version="1.0" encoding="UTF-8"?>
<frameChart  is3D="false" isInterpolated="false">
</frameChart>
</cfsavecontent>
 
<cfchart format="flash" chartwidth="700" chartheight="500" showlegend="yes" xaxistitle="Year of Report" style="#style#">
    <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" / >
</cfchart>

Open in new window

0
ZvonkoSystems architectCommented:
Sorry azadisaryev, I have misread your coment. Yor proposal was NOT to edit the default.xml
0
Kevin CrossChief Technology OfficerCommented:
And seems like you are getting some good information on the CF side, but I corrected my type-o anyway...Cannot take Year(DateOccurrenceClosed) is DateOccurrenceClosed is null therefore my logic must be changed to this:


<cfquery Name="OccOpenChart" datasource="#DSN#">
SELECT SUM(OccTotal) AS OccTotal
, SUM(OccOpen) AS OccOpen
, OccYear
FROM (
SELECT COUNT(OccurrenceNumber) As OccTotal, 0 As OccOpen
, Year(DateofReport) as OccYear
FROM OccurrenceDateTracking
GROUP BY Year(DateofReport)
UNION ALL
SELECT 0, COUNT(OccurrenceNumber)
, Year(DateofReport)
FROM OccurrenceDateTracking
WHERE DateOccurrenceClosed IS NULL
GROUP BY Year(DateofReport)
) derived
GROUP BY OccYear
ORDER BY OccYear
</cfquery>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ctreevesAuthor Commented:
Thank you!
0
ctreevesAuthor Commented:
Thank you very much to everyone for your kind replies.
The query provided by mwvisa1 works well.
I'll leave the chart here for a couple of days, but then remove it.
http://www.jptm.info/testing/stats.cfm
0
Kevin CrossChief Technology OfficerCommented:
Very cool.  Just glad to help.

Happy coding!

Kev
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.