```Has anyone used google.visualization.LineChart ...
# cfml-generals
sthornton1978
05/12/2022, 8:17 AM Copy code
Has anyone used google.visualization.LineChart recently?
I am looking to have a query result returned by a CFC, and I can't quite grasp how to pass the result ( as JSON? ) to the google API.
I was hoping not to re-invent the wheel.
What is causing me concern the most, is setting the data types of the columns. An example that better explains it then me ( although php) . The ajax call would call a method of a CFC.
<https://developers.google.com/chart/interactive/docs/php_example>d
drewnathanson
05/12/2022, 11:51 AMThe easiest way to make sure that the return type (on the function declaration line) has 'returnformat=json'. Now, within the body of the function, make sure that the 'return' statement has 'serializeJSON()' on it. This will automatically convert to JSON. Now, I'm assuming you will need to format the results to match the requirements for the google call.
s
sthornton1978
05/12/2022, 11:03 PMHI,
<cfcomponent>
<cffunction access="remote" name="getDCR" output="false" returntype="string" returnformat="JSON">
<cfset var grapresultset= "" />
<cfquery datasource="datasouce" name="dcr">
SELECT
[FINANCIAL_YEAR],
DATE_FIELD,
COUNT(*) as DCR_Count
FROM
WHERE
GROUP BY
P.[FINANCIAL_YEAR],
P.DATE_FIELD
ORDER BY
P.DATE_FIELD
</cfquery>
<cfreturn grapresultset>
</cffunction>
</cfcomponent>
returns:
"COLUMNS":["FINANCIAL_YEAR","DATE_FIELD","DCR_COUNT"], "DATA":[["FY19/20","July, 01 2019 000000",127],["FY19/20","August, 01 2019 000000",2740],["FY19/20","September, 01 2019 000000",4140],["FY19/20","October, 01 2019 000000",4154],["FY19/20","November, 01 2019 000000",3653],["FY19/20","December, 01 2019 000000",3521],["FY19/20","January, 01 2020 000000",3889],["FY19/20","February, 01 2020 000000",3163],["FY19/20","March, 01 2020 000000",3131],["FY19/20","April, 01 2020 000000",2248],["FY19/20","May, 01 2020 000000",1637],["FY19/20","June, 01 2020 000000",1680],["FY20/21","July, 01 2020 000000",2036],["FY20/21","August, 01 2020 000000",1909],["FY20/21","September, 01 2020 000000",2129],["FY20/21","October, 01 2020 000000",2572],["FY20/21","November, 01 2020 000000",2784],["FY20/21","December, 01 2020 000000",3081],["FY20/21","January, 01 2021 000000",3812],["FY20/21","February, 01 2021 000000",4000],["FY20/21","March, 01 2021 000000",4163],["FY20/21","April, 01 2021 000000",4179],["FY20/21","May, 01 2021 000000",4214],["FY20/21","June, 01 2021 000000",4148],["FY21/22","July, 01 2021 000000",3752],["FY21/22","August, 01 2021 000000",2728],["FY21/22","September, 01 2021 000000",2778],["FY21/22","October, 01 2021 000000",2835],["FY21/22","November, 01 2021 000000",3304],["FY21/22","December, 01 2021 000000",2893],["FY21/22","January, 01 2022 000000",3319],["FY21/22","February, 01 2022 000000",3264],["FY21/22","March, 01 2022 000000",3483],["FY21/22","April, 01 2022 000000",3517],["FY21/22","May, 01 2022 000000",3800]]}
but the API needs the data in something they refer to as a DataTable:
https://developers.google.com/chart/interactive/docs/datatables_dataviews
I was hoping someone would have used this API previously and pointed out the best approach.
sthornton1978
05/12/2022, 11:06 PMSo this JSON returned by the CFC, doesn't quite match the Datatable structure ( the header rows should have datatype for example ( but they can be inferred possibly by the API ).
I feel like converting the query result set to JSON is not the best approach, rather perhaps creating the required structure here in CF, rather than trying to manipulate the json structure that it does return in the javascript code ( jquery i think in my case ).
sthornton1978
05/12/2022, 11:07 PMI hope that someone wiser than me has already encountered this scenario, and advise what they had done.
Thanks.
s
Scott Steinbeck
05/12/2022, 11:42 PMbased on the structure in the example you would just need to tweak the return a bit
Copy code
[
['Employee Name', 'Salary'],
['Mike', {v:22500, f:'22,500'}], // Format as "22,500".
['Bob', 35000],
['Alice', 44000],
['Frank', 27000],
['Floyd', 92000],
['Fritz', 18500]
], Copy code
{ "COLUMNS":
['Employee Name', 'Salary'],
"DATA":
['Mike', {v:22500, f:'22,500'}], // Format as "22,500".
['Bob', 35000],
['Alice', 44000],
['Frank', 27000],
['Floyd', 92000],
['Fritz', 18500]
},Scott Steinbeck
05/12/2022, 11:44 PMyou should be able to do something like this after your query
Copy code
var qry = deserializeJSON(serializeJSON(dcr));
var returnData = qry.data;
arrayPrepend(returnData,qry.columns);
return returnData;Scott Steinbeck
05/12/2022, 11:49 PM5 Views