cfc to return JSON to a google.visualization.LineChart

Report · May 11, 2022

Hi,

( ColdFusion Server Developer 2016,0,11,314546 )

Consider:

<cfcomponent>
	<cffunction access="remote" name="getDCR" output="false" returntype="string"  returnformat="JSON">
		
			<cfset var graphresultset= "" />
		
			<cfquery datasource="datasouce" name="dcr">
				SELECT
					[FINANCIAL_YEAR],
					DATE_FIELD,
					COUNT(*) as DCR_Count
				FROM	
                                      XXXX
				WHERE
					YYYYY
				GROUP BY
					P.[FINANCIAL_YEAR],
					P.DATE_FIELD
				ORDER BY
					P.DATE_FIELD	
			</cfquery>


		<cfreturn graphresultset>
	</cffunction>
</cfcomponent>

that returns:

"COLUMNS":["FINANCIAL_YEAR","DATE_FIELD","DCR_COUNT"],
"DATA":[["FY19/20","July, 01 2019 00:00:00",127],["FY19/20","August, 01 2019 00:00:00",2740],["FY19/20","September, 01 2019 00:00:00",4140],["FY19/20","October, 01 2019 00:00:00",4154],["FY19/20","November, 01 2019 00:00:00",3653],["FY19/20","December, 01 2019 00:00:00",3521],["FY19/20","January, 01 2020 00:00:00",3889],["FY19/20","February, 01 2020 00:00:00",3163],["FY19/20","March, 01 2020 00:00:00",3131],["FY19/20","April, 01 2020 00:00:00",2248],["FY19/20","May, 01 2020 00:00:00",1637],["FY19/20","June, 01 2020 00:00:00",1680],["FY20/21","July, 01 2020 00:00:00",2036],["FY20/21","August, 01 2020 00:00:00",1909],["FY20/21","September, 01 2020 00:00:00",2129],["FY20/21","October, 01 2020 00:00:00",2572],["FY20/21","November, 01 2020 00:00:00",2784],["FY20/21","December, 01 2020 00:00:00",3081],["FY20/21","January, 01 2021 00:00:00",3812],["FY20/21","February, 01 2021 00:00:00",4000],["FY20/21","March, 01 2021 00:00:00",4163],["FY20/21","April, 01 2021 00:00:00",4179],["FY20/21","May, 01 2021 00:00:00",4214],["FY20/21","June, 01 2021 00:00:00",4148],["FY21/22","July, 01 2021 00:00:00",3752],["FY21/22","August, 01 2021 00:00:00",2728],["FY21/22","September, 01 2021 00:00:00",2778],["FY21/22","October, 01 2021 00:00:00",2835],["FY21/22","November, 01 2021 00:00:00",3304],["FY21/22","December, 01 2021 00:00:00",2893],["FY21/22","January, 01 2022 00:00:00",3319],["FY21/22","February, 01 2022 00:00:00",3264],["FY21/22","March, 01 2022 00:00:00",3483],["FY21/22","April, 01 2022 00:00:00",3517],["FY21/22","May, 01 2022 00:00:00",3800]]}

How do I return the JSON data in a format that looks like a DataTable to the google graph API?

<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">
    
    // Load the Visualization API and the piechart package.
    google.charts.load('current', {'packages':['corechart']});
      
    // Set a callback to run when the Google Visualization API is loaded.
    google.charts.setOnLoadCallback(drawChart);
      
    function drawChart() {
      var jsonData = $.ajax({
          url: "graph.cfc?method=getDCR",
          dataType: "json",
          async: false
          }).responseText;
		           
      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);

      // Instantiate and draw our chart, passing in some options.
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, {width: 600, height: 440});
    }

    </script>
  </head>

  <body>
    <!--Div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>

https://developers.google.com/chart/interactive/docs/gallery/linechart

https://developers.google.com/chart/interactive/docs/datatables_dataviews

I don't know if my JSON returned by the CFC is correct, or I need to parse it further in a DataTable format that the api format likes better.

Has anyone got any experience in returning data from a CFC to the google graph API and has any pointers?

Thanks,

Report · May 13, 2022

Typo: your JSON misses { at the beginning.

In any case, it looks like a good representation of a table. Why I copied it to http://json2table.com/, and tested it there, I got a table.