sum grid data

RobertKirkwood — Mon, 03 Nov 2014 19:51:05 GMT

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();
    }


});

Re: sum grid data

DerekLoi — Mon, 03 Nov 2014 19:57:05 GMT

Robert,

Have you looked into something like JsonSQL ?

or try linq.js - LINQ for JavaScript - Home

Re: sum grid data

RobertScheitlin__GISP — Mon, 03 Nov 2014 20:31:10 GMT

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

Re: sum grid data

RobertKirkwood — Mon, 03 Nov 2014 20:44:46 GMT

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!

Re: sum grid data

RobertScheitlin__GISP — Mon, 03 Nov 2014 20:49:17 GMT

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.

Re: sum grid data

RobertKirkwood — Mon, 03 Nov 2014 20:56:15 GMT

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...

Re: sum grid data

RobertScheitlin__GISP — Mon, 03 Nov 2014 21:05:51 GMT

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.

Re: sum grid data

RobertKirkwood — Mon, 03 Nov 2014 21:12:23 GMT

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

topic Re: sum grid data in ArcGIS JavaScript Maps SDK Questions

sum grid data

Re: sum grid data

Re: sum grid data

Re: sum grid data

Re: sum grid data

Re: sum grid data

Re: sum grid data

Re: sum grid data