asked on

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

Kevin Cross

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

azadisaryev

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

ctreeves

ASKER

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.

azadisaryev

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

Kevin Cross

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...

azadisaryev

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

Zvonko

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

Zvonko

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

Zvonko

Sorry azadisaryev, I have misread your coment. Yor proposal was NOT to edit the default.xml

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ctreeves

ASKER

Thank you!

ctreeves

ASKER

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

Kevin Cross

Very cool. Just glad to help.

Happy coding!

Kev