CFChart - Need to know how to pass NULL values from a query in CF11

Community Beginner ,
Apr 18, 2017 Apr 18, 2017

Copy link to clipboard

Copied

HI Everyone,

I am really hoping this is an easy question to answer because I have been searching everywhere and all I try is not working.

I have a SQL query (created by Oracle DB) that creates multiple cfchartseries using date as the column item and a number for the value. My issue is there are null values for the value field but I need it to plot

<cfchartseries

  label="LRF"

  scales= "x,y"

  >

       <cfloop query="qTrend">

            <cfif qTrend.LRF gt 0>

                 <cfchartdata item="#qTrend.report_date#" value="#qTrend.LRF#">

            <cfelse>

                 (i have tried:

                     <cfchartdata item="#qTrend.report_date#" value=" ">

<cfchartdata item="#qTrend.report_date#" value="null">

<cfchartdata item="#qTrend.report_date#" value="">

<cfchartdata item="#qTrend.report_date#" value="0">

and none work)

            </cfif>

       </cfloop>

  </cfchartseries>

The ERROR I get is
Attribute validation error for CFCHARTDATA. The value of the VALUE attribute is invalid. The value cannot be converted to a numeric because it is not a simple value.Simple values are booleans, numbers, strings, and date-time values

The Chart will display when I use the value ="0" but this pulls the line down instead of just STOPPING and then starting again when a numeric value is in the next record.

Here is my query

THANKS for help... I need it quickly if possible

JV

Views

644

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Community Beginner , Apr 20, 2017 Apr 20, 2017
The issues is that when using the <cfchartdata> tag it will not accept a blank, null, space without creating an error. I did not find a resolve in using this method to insert my chart values so I created a work around that is working.I removed the <cfchartdata> tag all together and built an ArrayNew(2) to pass both item and value data. Like shown here<cfset a_data=ArrayNew(2)>   <cfloop query="qTrend">       <cfset a_data[#qTrend.currentrow#][1]="#qTrend.report_date#">       <cfif qTrend.LRF gt ...

Likes

Translate

Translate
LEGEND ,
Apr 20, 2017 Apr 20, 2017

Copy link to clipboard

Copied

I suspect that the issue lies in how you are comparing values in your conditional.

Change this: <cfif qTrend.LRF gt 0>  to this: <cfif val(qTrend.LRF) gt 0> to force the value to numeric.  If it's blank (CF interprets database nulls as empty strings), then val('') is 0.

HTH,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 20, 2017 Apr 20, 2017

Copy link to clipboard

Copied

LATEST

The issues is that when using the <cfchartdata> tag it will not accept a blank, null, space without creating an error. I did not find a resolve in using this method to insert my chart values so I created a work around that is working.

I removed the <cfchartdata> tag all together and built an ArrayNew(2) to pass both item and value data. Like shown here

<cfset a_data=ArrayNew(2)>

   <cfloop query="qTrend">

       <cfset a_data[#qTrend.currentrow#][1]="#qTrend.report_date#">

       <cfif qTrend.LRF gt 0>

            <cfset a_data[#qTrend.currentrow#][2]="#qTrend.LRF#">

       <cfelse>

            <cfset a_data[#qTrend.currentrow#][2]="null">

       </cfif>

  </cfloop>

THEN pass in the Array in the <cfchartseries> tag as show below:

  <cfchartseries

  label="LRF (InMill)"

  scales= "x,y"

data=#a_data#

  >

NOTE: this attribute will only take an Array of Arrays so make sure you do not attempt to insert an Array with a Struct in it... ERROR!

There were many ways to write this but for some reason between CF11, Tomcat, and ZingCharts this is the only way I found other then completely removing the <cfchartseries> tags and completely working around CF11 and using the "style" .js file to built the entire chart.

I really hope this helps the next person looking for this FIX....!!

I am also open to other ways ... so if you like please continue to post to this if you find a better simpler way...THANKS

JV

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines