Blog

Using Column Summaries in the jQuery igGrid

Jordan Tsankov / Wednesday, December 7, 2011

One of the out-of-the-box features of the igGrid control is Column Summary. These column summaries are not a wall of text that adds extra description to the type of data a column holds. Rather, they represent aggregate functions that you can apply to the given column – things like minimum value, maximum value, total count of entries and so forth. Enabling column summaries on an igGrid will make the grid look like this:

summaries_on

You can see how helpful this feature is as it gives you concise statistical information about the data displayed in every column. Furthermore, you can also implement custom functions to act as extra summaries. For example, if you don’t need to know the entire total value of visible elements in a column, you can implement a column summary that gathers the value of every second element instead. This blog post is aimed at showing you how to do such a thing and then how to extract the summaries from the table and use them in another control.

If you are eager to grab the demo solution, then just click here.

Let’s get on with it !

Before we begin

I am expecting you to have an igGrid set up and running, but in case you don’t – here’s a really quick rundown on what to do in order to get one:

Open up your Infragistics products installation folder, navigate to jQuery/combined/min/ and copy ig.ui.min.js into your Scripts/ folder of your project. Then go to jQuery/demos/scripts/ and copy jquery-1.4.4.min.js and jquery-ui.min.js into the Scripts/ folder of your project ( alternatively get a copy of jQuery and jQueryUI ). Once you’re done with these files, go to jQuery/themes/ and copy all three folders into your Styles/ or Content/ folder in your project.

Add links to the added JavaScript & CSS files in the <head> tag of the page you intend to use them on, and you’re all set.

Enabling the summaries

With the required files added in, you are now ready to set up column summaries for an igGrid of your choice. To do so, you just add the summaries as a new object to the features declaration of that grid, like so:

   1: $("#gridTarget").igGrid({
   2:                 autoGenerateColumns: true,
   3:                 width: 700,
   4:                 dataSource: jsonp,
   5:                 features: [
   6:                 { name: "Paging", pageSize: 5 },
   7:                 { name: "Summaries" }
   8:                 ]
   9:             });

With “autoGenerateColumns” set to true, our NetAdvantage for jQuery igGrid will infer each column’s data type and will permit the appropriate column summaries for every column. This means that every column containing numbers only will have all the aggregators such as min / max / average / count / sum available to it. To illustrate this, we’ll use NetFlix’s oData API to supply ourselves with a data source. We’ll pick a few columns that we want to display and define these columns manually.

   1: var data_url = "http://odata.netflix.com/Catalog/Titles?$format=json&$callback=?";
   2: var jsonp = new $.ig.JSONPDataSource({ dataSource: data_url, responseDataKey: "d.results" });
   3:  
   4: $("#gridTarget").igGrid({
   5:                autoGenerateColumns: false,
   6:                width: 700,
   7:                dataSource: jsonp,
   8:                columns: [
   9:                    { headerText: "Movie Name", key: "Name" },
  10:                    { headerText: "Release Year", key: "ReleaseYear"},
  11:                    { headerText: "Rating", key: "AverageRating"}
  12:                ],
  13:                features: [
  14:                { name: "Paging", pageSize: 5 },
  15:                { name: "Summaries" }
  16:                ]
  17:            });

The columns chosen were movie title, its release year and its rating. We’ve defined a JSONP data source and linked it to NetFlix’s service. Next, we instantiate our grid and list our object as a data source for the grid. We then manually describe the columns we want to display – take note that we haven’t specified the data type of either of the columns. The result we get from this code looks like this:

summaries_datatype

Even though both the release year and the rating columns only contain numbers, you see that the only available column summary is the generic “count” one – the ones that are typically available for numbers are not present. To fix this issue, you’ll have to specify the data type of every column – let’s see it done :

   1: $("#gridTarget").igGrid({
   2:                autoGenerateColumns: false,
   3:                width: 700,
   4:                dataSource: jsonp,
   5:                columns: [
   6:                    { headerText: "Movie Name", key: "Name" },
   7:                    { headerText: "Release Year", key: "ReleaseYear", dataType: "number" },
   8:                    { headerText: "Rating", key: "AverageRating", dataType: "number" }
   9:                ],
  10:                features: [
  11:                { name: "Paging", pageSize: 5 },
  12:                { name: "Summaries" }
  13:                ]
  14:            });

With the above additions, the widget promptly complies and automagically displays more summaries for the columns that contain numbers, officially this time, hehe.

summaries_datatype

Even with such an improvement over the informational capabilities of our igGrid ( adding aggregate calculations for every column ), there is still room for more addition. Let’s move on to the next part of this post, where we’ll discuss how to…

…Add your own summaries

In the examples above, let’s focus on the “Release Year” column. Now, honestly, you might only need the sum of all the release years in some very rare circumstance. The average of all the release years isn’t that helpful as well. However, two useful aggregate functions would be counting the release years above and below a given year. Let’s say you’re more interested in those than the “Sum” and “AVG” ones. Let’s see how would you go about creating your custom aggregate function now.

   1: years = {
   2:     year: 0,
   3:     bigger: function (data) {
   4:         var i, l = data.length, count = 0;
   5:         for (i = 0; i < l; i++) {
   6:             if (data[i] > this.year) count++;
   7:         }
   8:         return count;
   9:     },
  10:     smaller: function (data) {
  11:         var i, l = data.length, count = 0;
  12:         for (i = 0; i < l; i++) {
  13:             if (data[i] <= this.year) count++;
  14:         }
  15:         return count;
  16:     }
  17: };
  18:  
  19: years.year = 1995;

What we’re doing here is preparing an object which will compare an array’s values to a predefined number and count the amount of numbers that are smaller than or bigger than it. The last line in the snippet – line 19 – sets the number we’ll compare to to 1995. Now we move on to actually telling the column summaries to use this function.

   1: $("#gridTarget").igGrid({
   2:     autoGenerateColumns: false,
   3:     width: 700,
   4:     dataSource: jsonp,
   5:     columns: [
   6:         { headerText: "Movie Name", key: "Name" },
   7:         { headerText: "Release Year", key: "ReleaseYear", dataType: "number" },
   8:         { headerText: "Rating", key: "AverageRating", dataType: "number" }
   9:     ],
  10:     features: [
  11:     { name: "Paging", pageSize: 5 },
  12:     { name: "Summaries", columnSettings: [
  13:         { columnKey: "ReleaseYear", allowSummaries: true, summaryOperands: [
  14:             { rowDisplayLabel: "Release year below or equal to " + years.year, active: true, type: "custom", summaryCalculator: $.proxy(years, "smaller"), order: 0, decimalDisplay: 0 },
  15:             { rowDisplayLabel: "Release year above " + years.year, active: true, type: "custom", summaryCalculator: $.proxy(years, "bigger"), order: 1, decimalDisplay: 0 }
  16:         ]
  17:         }
  18:     ], resultTemplate: "{0}: {1}", summariesCalculated: function (evt, ui) {
  19:         updateTree();
  20:     }
  21:     }
  22:     ]
  23: });

On line 12 you see that we’ve added the columnSettings property, which bestows unique behavior to particular columns. In our example we’ve decided to add our custom summaries to the “Release Year” column, so we reference it by its column key – “ReleaseYear” ( as seen on line 13 ). We then go on to specifying the different summaries available – in our case we want two. One for years before 1995 and one for years after 1995.  This happens on lines 14 and 15, where we’ve applied various descriptive properties for every summary. 

I would just like to draw your attention to the summaryCalculator property. You may notice that what happens in there is not so straightforward – we’re using the jQuery proxy function. There’s a good reason for that – in the snippet where we made the object used to compare years, you see that we compare each data element with this.year ( lines 6 and 13 ). Now if you’ve read the docs, you see that the summaryCalculator property accepts a function name. So maybe it’d be natural to try to set it like this :

   1: summaryCalculator: years.bigger

However you’ll see it doesn’t work. The reason is that “this” no longer refers to "years", but to the context of one of the summaryOperands objects. To overcome this issue, we use jQuery’s proxy, which allows us to ensure that "this" in our custom operand will be what we expect it to be - years.

   1: summaryCalculator: $.proxy(years, "bigger")

…which basically means “I want to force the context in the function called ‘bigger’ of the years object to be ‘years’”. Even more basically, we’re saying “I want ‘this’ in years.bigger to really mean ‘years’”.

summaries_custom

And there you have it, the two custom column summaries displayed. Before we move on to the final part of this post, I would like to again draw some attention over a certain topic. Since your custom functions are, after all, aggregate functions – it would be a good idea if you design them so they accept and work with an array of values ( as seen in the example above ).

Working with summaries

By this point we’ve explored the more advanced parts of setting up a column summary for your igGrid widget. What we missed is how you can actually use the data from these summaries – maybe even in a different control. For this purpose, we’ll use two progress bars which will represent the percent of movies on the current page that were released before and after 1995. We’re aiming for something like this:

summaries_progressbars

How to achieve this effect – here we go.

First, add two <label> elements right under the grid – like so:

   1: <label>Percent of release years under or exactly 1995: <strong id="under_label"></strong></label><div id="progress_under_3" class="progress"></div>
   2: <label>Percent of release years above 1995: <strong id="above_label"></strong></label><div id="progress_above_3" class="progress"></div>

Then, since we’d like our progress bars to update every time a new column summary is calculated, we hook a custom function to the summariesCalculated event. 

   1: $("#gridTarget").igGrid({
   2:     autoGenerateColumns: false,
   3:     width: 700,
   4:     dataSource: jsonp,
   5:     columns: [
   6:         { headerText: "Movie Name", key: "Name" },
   7:         { headerText: "Release Year", key: "ReleaseYear", dataType: "number" },
   8:         { headerText: "Rating", key: "AverageRating", dataType: "number" }
   9:     ],
  10:     features: [
  11:     { name: "Paging", pageSize: 5 },
  12:     { name: "Summaries", columnSettings: [
  13:         { columnKey: "ReleaseYear", allowSummaries: true, summaryOperands: [
  14:             { rowDisplayLabel: "Release year below or equal to " + years.year, active: true, type: "custom", summaryCalculator: $.proxy(years, "smaller"), order: 0, decimalDisplay: 0 },
  15:             { rowDisplayLabel: "Release year above " + years.year, active: true, type: "custom", summaryCalculator: $.proxy(years, "bigger"), order: 1, decimalDisplay: 0 }
  16:         ]
  17:         }
  18:     ], resultTemplate: "{0}: {1}", summariesCalculated: function (evt, ui) {
  19:         updateTree();
  20:     }
  21:     }
  22:     ]
  23: });
  24:  
  25: function updateTree() {
  26:     var summaries = $("#gridTarget").igGridSummaries("summariesFor", "ReleaseYear");
  27:     $("#progress_under_3").progressbar({ value: (summaries[0].result / 5) * 100 });
  28:     $("#progress_above_3").progressbar({ value: (summaries[1].result / 5) * 100 });
  29:     $("#under_label").text($("#progress_under_3").progressbar("value"));
  30:     $("#above_label").text($("#progress_above_3").progressbar("value"));
  31: }

On lines 18 and 19 we define the function we want to call every time summariesCalculated is fired. That function, updateTree, gets all summaries for the column with a columnKey “ReleaseYear” and then appropriately updates the values of the progress bars and the labels in front of each bar. Line 26 is just one of the methods available for use when dealing with column summaries.

Conclusion

Having Column Summaries enabled on your igGrid widget will bring about statistical information that is quite useful when assessing columns in bulk. This post hopefully helped you understand how to enable column summaries for any igGrid, and how to add your own aggregate functions to a column. Additionally, we went through separating the summary data from the grid and displaying it in a more convenient and user-friendly way.

Here are some helpful links:

igGrid architecture

igGrid Column Summaries API

Download a sample project by clicking here.

igGridColumnSummaries.zip