mayson on Coldfusion Json data Grouping Issue

Wed, 03 Aug 2011 09:34:25 +0300

Hi I'm currently developing a whole suite of dashboards for my workplace and have hit an issue I cant work out and have searched every where for answers and have found none.

I'm developing within the coldfusion environment and my grid loads and populates fine but the grouping that I have enabled is not working and I think it maybe my JSON data format but cant be sure.

My JSON format looks like this:

{"ROWS":[["Account Enquiry","Adding a myki",77.0,105.0,-0.27,385.0,601.0,-0.36],["Account Enquiry","Archive cards",0.0,64.0,0.00,451.0,17.0,25.53],["Account Enquiry","Cancelling an account",27.0,52.0,-0.48,164.0,165.0,-0.01],["Account Enquiry","Change of details",305.0,483.0,-0.37,2400.0,2311.0,0.04],["Account Enquiry","Consolidate cards",0.0,84.0,0.00,685.0,45.0,14.22],["Account Enquiry","Obtain Balance",560.0,1272.0,-0.56,6828.0,3604.0,0.89],["Account Enquiry","Removing a myki",16.0,28.0,-0.43,87.0,112.0,-0.22],["Account Enquiry","Retrieve cards",0.0,13.0,0.00,56.0,1.0,55.00],["Account Enquiry","Tax invoice request",11.0,33.0,-0.67,103.0,42.0,1.45],["Account Enquiry","Transaction history",502.0,1035.0,-0.51,5370.0,3334.0,0.61]],"PAGE":1.0,"RECORDS":10,"TOTAL":1.0}

Using the following CFC.

<cfcomponent displayname="InvoiceMgr" output="false">
<cffunction name="getInvoices" access="remote" output="false" returnformat="json">
<cfset invoices = ArrayNew(1) />
<cfquery datasource="CS_1" name="q">
SELECT top 10 name, segment, current_month, previous_month, convert(numeric(17,2),m_variance) as m_variance, current_year, previous_year, Convert(numeric(17,2),y_variance) as y_variance
FROM dbo.csg_disposition_dashboard_4
group by name, segment, current_month, previous_month, m_variance, current_year, previous_year,y_variance
order by name asc
</cfquery>
<cfloop query="q">
<cfset invoices[currentrow] = [#name#,#segment#,#current_month#, #previous_month#,#m_variance#, #current_year#, #previous_year#,#y_variance#]>
</cfloop>
<cfset str = {total=1, page=1, records=#q.recordcount#, rows=invoices}>
<cfreturn str />
</cffunction>
</cfcomponent>

The Javascript i'm using is:

<html>
<head>
<title>jqGrid</title>
<link rel="stylesheet" type="text/css" href="/Jquery/themes/ui-darkness/jquery-ui-1.8.14.custom.css" />
    <link rel="stylesheet" type="text/css" href="/Jquery/css/ui.jqgrid.css" />

<script type="text/javascript">
$(document).ready(function(){
$("#list").jqGrid({
url:'contacts1.cfc?method=getInvoices',
datatype: 'json',
colNames:['Source','Segment','August','July','Variance','2011','2010','Variance'],
colModel :[
{name:'name',index:'name', width:250, editable:true},
{name:'segment',index:'segment', width:200},
{name:'current_month',index:'current_month', width:100,align:"center",sorttype:"int",summaryType:'sum'},
{name:'previous_month',index:'previous_month', width:100,align:"center",sorttype:"int",summaryType:'sum'},
{name:'m_variance',index:'m_variance', width:80,align:"center",sorttype:"int",summaryType:'avg'},
{name:'current_year',index:'current_year', width:100,align:"center",sorttype:"int",summaryType:'sum'},
{name:'previous_year',index:'previous_year', width:100,align:"center",sorttype:"int",summaryType:'sum'},
{name:'y_variance',index:'y_variance', width:80,align:"center",sortable:false,summaryType:'avg'}],
pager: $('#pager'),

rowList:[100,300,600,1000],
width: 'auto',
height: 'auto',
sortname: "name",
sortorder: "desc",
viewrecords: true,
jsonReader: {
root: "ROWS",
page: "PAGE",
total: "TOTAL",
cell: "",
id: "id",
caption: "Disposition Codes",
grouping:true,
groupingView : {
               groupField : ['name'],
     groupDataSorted : true
                }

}}
);
});
</script>
</head>
<body>
<table id="list" class="scroll"> <div id="pager" class="scroll" style="text-align:center;"> </body>
</html>

I really would like some help on this as I'm at the end of sanity, head hitting desk.

Looking forward to someone knowing the answer.

Regards

Mayson

jQuery Grid Plugin - jqGrid - Topic: Coldfusion Json data Grouping Issue

mayson on Coldfusion Json data Grouping Issue