sum grid data

[RobertKirkwood]

I am creating a dojo grid. I would like to have the grid give me unique values for the Lease Type with the sum in a column next to it. The output looks like this:

I would like it to show one Lease Type with the sum of the Annual Revenue. Here is the code i am using:

var grid;
var myLeaseData;
var myQueryTask;
var UniqueLeaseCodes = [];
var FoundLeaseCode = false;
var myQuery;
require([
    "esri/tasks/QueryTask", "esri/tasks/StatisticDefinition", "esri/tasks/query",
    "dojo/number", "dojo/date/locale", "dojo/dom", "dojo/on",
    "dojo/_base/array", "dojo/store/Memory",
    "dgrid/OnDemandGrid", "dojo/domReady!"
], function (
        QueryTask, StatisticDefinition, Query,
        number, locale, dom, on,
        arrayUtils, Memory,
        OnDemandGrid
        ) {
    // create a dgrid 
    var sortAttr = [{
            attribute: "LEASE_TYPE",
            descending: false
        }];
    grid = new OnDemandGrid({
        store: new Memory({
            idProperty: "name"


        }),
        columns: {
            name: "Lease Type",
            annualExpense: {
                label: "Annual Expense",
                formatter: formatRound
            }

        },
        sort: sortAttr,
        query: {STATUS: "Active"},
    }, "grid");


    myQueryTask = new QueryTask(getUrlKlondike('Leases'));
    var myDate = new Date();
    var myMS = myDate.getMilliseconds();
    myQuery = new Query();


    myQuery.outFields = ['*'];
    myQuery.returnGeometry = false;
    myQuery.where = "(" + myMS + "=" + myMS + ")";


    myQueryTask.execute(myQuery, function (results) {

        myLeaseData = [
            {name: "", id: ""},
            {name: "", id: ""}
        ];

        for (var i = 0; i < results.features.length; i++) {
            if (results.features.attributes.LEASE_TYPE) {
                myLeaseData.push({
                    name: results.features.attributes.LEASE_TYPE,
                    annualExpense: results.features.attributes.ANNUAL_EXPENSE,
                    annualRevenue: results.features.attributes.ANNUAL_REVENUE,
                    leaseDPSF: results.features.attributes.DOLLARS_PER_SQ_FT,
                    objectIDCheck: results.features.attributes.LEASE_ID,
                    id: results.features.attributes.LEASE_ID,
                    OBJECTID: results.features.attributes.OBJECTID,
                    SQ_FT: results.features.attributes.SQ_FT,
                    EFFECTIVE_DATE: results.features.attributes.EFFECTIVE_DATE,
                    EXPIRATION_DATE: results.features.attributes.EXPIRATION_DATE,
                    LINK_TO_DOC: results.features.attributes.LINK_TO_DOC,
                    LESSEE: results.features.attributes.LESSEE,
                    LESSOR: results.features.attributes.LESSOR,
                    LEGAL_DESCRIPTION: results.features.attributes.LEGAL_DESCRIPTION,
                    LEASE_TERM_YEARS: results.features.attributes.LEASE_TERM_YEARS,
                    ANNUAL_REVENUE: results.features.attributes.ANNUAL_REVENUE,
                    ANNUAL_EXPENSE: results.features.attributes.ANNUAL_EXPENSE,
                    LESSOR_NAME: results.features.attributes.LESSOR_NAME,
                    LESSEE_NAME: results.features.attributes.LESSEE_NAME,
                    STATUS: results.features.attributes.STATUS,
                    CONTACT_DEPT: results.features.attributes.CONTACT_DEPT,
                    LEASE_USE: results.features.attributes.LEASE_USE,
                    LEASE_TYPE: results.features.attributes.LEASE_TYPE,
                    NOTES: results.features.attributes.NOTES,
                    DOC_ACRES: results.features.attributes.DOC_ACRES,
                    DOLLARS_PER_SQ_FT: results.features.attributes.DOLLARS_PER_SQ_FT,
                    RENEWAL_NOTIFICATION: results.features.attributes.RENEWAL_NOTIFICATION,
                    LEASE_ID: results.features.attributes.LEASE_ID
                });
            }


        }


        console.log('Query A: ' + myLeaseData.length);
        updateGrid(myLeaseData);
    });


    // formatting function for numbers
    function formatRound(value) {
        return number.round(value, 2);
    }

    function updateGrid(myLeaseData) {
        grid.store.setData(myLeaseData);
        grid.refresh();
    }


});

[DerekLoi]

Robert,

Have you looked into something like JsonSQL ?

or try linq.js - LINQ for JavaScript - Home

[RobertScheitlin__GISP]

Robert,

   If you are using ArcGIS Server 10.1 or greater than you can use the outStatistics of Query to supply StatisticDefinition class and also use returnDistinctValues

[RobertKirkwood]

I looked at those methods but was unclear of how to implement them with the grid. I will do more research and see how to make them work...Thanks!

[RobertScheitlin__GISP]

Robert,

   The datagrid is just the means you are using to display the Query results. The Query parameters like returnDistinctValues and outStatistics is what will refine the data for being displayed in the grid.

[RobertKirkwood]

Do i need to redo myQueryTask and replace it with the StatisticDefinition? or is there a place in my code i can implement that? Sorry i am still learning...

[RobertScheitlin__GISP · Accepted solution]

Robert,

  The returnDistinct is just a boolean property to set on the Query class and outStatistics which is an array of StatisticDefinitions. See the documentation links I already provided.

[RobertKirkwood]

Thanks! i got the returnDistinct to work. now i will work on the stats...thanks again!