[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

cfchart not showing zero if query is null

Posted on 2008-11-04
13
Medium Priority
?
1,071 Views
Last Modified: 2013-12-24
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
0
Comment
Question by:ctreeves
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22883088
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
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22883093
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
 

Author Comment

by:ctreeves
ID: 22883181
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:azadisaryev
ID: 22883207
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22883211
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
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22883245
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
 
LVL 63

Expert Comment

by:Zvonko
ID: 22883810
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
 
LVL 63

Expert Comment

by:Zvonko
ID: 22883826
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
 
LVL 63

Expert Comment

by:Zvonko
ID: 22883841
Sorry azadisaryev, I have misread your coment. Yor proposal was NOT to edit the default.xml
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22885174
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
 

Author Closing Comment

by:ctreeves
ID: 31513402
Thank you!
0
 

Author Comment

by:ctreeves
ID: 22885892
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22885995
Very cool.  Just glad to help.

Happy coding!

Kev
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question