cfc to return JSON to a google.visualization.LineChart
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,
AdChoices