Loading canvasjs chart with data from database

Question

I am using coldfusion to load data into my pie chart but instead of a pie chart I get a list of 35 datapoints, and no chart displayed.

This is my code:

<script type="text/javascript">
$(document).ready(function () {
    $.getJSON("display.cfc?method=getData&lob=all", function (result) {
        alert(result);
        var chart2 = new CanvasJS.Chart("piechart", {
            theme: "theme2",
            legend: {
                fontSize: 12,
                horizontalAlign: "right",
                verticalAlign: "center"
            },
            data: [{
                type: "pie",
                showInLegend: true,
                dataPoints: result
            }]
        });
        chart2.render();
    });
});
</script>
<div id="piechart" style="display: inline-block; height: 200px; width: 400px;"></div>

my coldfusion function is returning this:

{y: 142, legendtext: "In Progress"}, {y: 1083, legendtext: "New"},{y: 48, legendtext: "Error"}

This is the function:

<cffunction name="getData" access="remote" returnformat="json">
<cfargument name="lob" type="string" required="yes" />
<cfset var theQuery = getTransitionStatusCounts(#arguments.lob#)>
<cfset var rObj = "">
<cfset var rObjArray = []>

<cfoutput query="theQuery">
    <cfset rObj = '{y: #count#, legendtext: "#status#"}'>
    <cfset arrayAppend(rObjArray, rObj)>
</cfoutput>

<cfreturn rObjArray>

</cffunction>

Has anyone else had this problem?

KobbyPemson · Answer 1 · 2014-02-25T19:53:22.927

2 things.
1. datapoints needs to be in an array
2. use coldfusion's json as is. don't manually create json in the cfc

$(document).ready(function () {
   $.getJSON("display.cfc?method=getData&lob=all", function (result) {       
      dp = [];
      for(var i = 0 ; i< result.DATA.length; i++){
          dp.push({y: result.DATA[i][0], label: result.DATA[i][1]})};       
      var chart2 = new CanvasJS.Chart("piechart", {
        theme: "theme2",
        data: [{
            type: "pie",
            dataPoints: dp }]});
        chart2.render();});});

This is what i have in my cfc

<cfcomponent access="remote">
<cffunction name="getData" access="remote" returnformat="json">
    <cfargument name="lob" type="string" required="yes" />
    <cfset var myQuery = QueryNew("y,label")/>
    <cfset QueryAddRow(myQuery,3) />

    <cfset QuerySetCell(myQuery, "y" , 142,1) />
    <cfset QuerySetCell(myQuery, "label" , "In Progress", 1) />

    <cfset QuerySetCell(myQuery, "y" , 1083,2) />
    <cfset QuerySetCell(myQuery, "label" , "New",2) />

    <cfset QuerySetCell(myQuery, "y" , 48,3) />
    <cfset QuerySetCell(myQuery, "label" , "Error",3) />

    <cfreturn myQuery/>    
  </cffunction>
</cfcomponent>

(Edit) Unrelated to the question: be sure to always `var/local` scope function local variables. Also, no need to capture results if they are not used. Just write `` — Leigh, Feb 25 '14 at 18:44

Leigh · Answer 2 · 2014-03-10T04:23:31.980

The problem is you are mixing manually constructed JSON strings with returnformat="json". So when CF encodes the array, it has no idea the array elements were already encoded (well partially), and treats the elements as a string, escaping the existing quotes. So the end result is not an array of structures in JSON ie The result is this:

  [ "{y: 142, legendtext: \"In Progress\"}",.... ]

... instead of:

  [ {"y":142, "legendtext":"In Progress"},....]

You do not need to roll your own JSON. Just create an array of structures and CF will do the rest:

    ...
    <cfoutput query="theQuery">
        <!--- use structure notation to preserve case --->
        <cfset rObj = {} />
        <cfset rObj["y"] = count />
        <cfset rObj["legendtext"] = status />
        <cfset arrayAppend(rObjArray, rObj)>
    </cfoutput>

    <cfreturn rObjArray>

Also, for greater flexibility you could have the function return a plain array, then specify JSON format by using the parameter ?returnformat=json when you call the .cfc from jQuery.

score 0 · Answer 3 · answered Feb 25 '14 at 17:35

0

Your json data is not formatted properly - array should be enclosed in [] and variables in quotes. Try outputting the below JSON - just a sting concatenation should work in your case.

[{"y": 142, "legendtext": "In Progress"}, {"y": 1083, "legendtext": "New"},{"y": 48, "legendtext": "Error"}]

answered Feb 25 '14 at 17:35

Sunil Urs

358
1
6

The array will be denoted by `[...]`. Since they are using `returnformat="json"` it is done automatically. – Leigh Feb 25 '14 at 18:22

Loading canvasjs chart with data from database

3 Answers3