Section 14.4. Data Grid | Ajax Design Patterns

14.4. Data Grid

Database, Query, Report, Summary, Table

Figure 14-11. Data Grid

14.4.1. Goal Story

Reta is doing a little data mining on this season's sales figures. She's scrolling through a table showing a row for every transaction. Noticing that higher sales figures tend to come in the morning, she sorts the table by sales figure. There's too much data there, so she filters out any transactions below $1,000, then groups the transactions by time of day.

14.4.2. Problem

How can you help users explore data?

14.4.3. Forces

Ajax Apps have their own databases, as well as access to external systems.
Users need a way to explore all this data, in order to verify it, understand how it works, predict future trends, and so on.
You can't anticipate what users will need to do with data.

14.4.4. Solution

Report on some data in a rich table and support common querying functions. A Data Grid has the familiar database client interface: a table with a row for each result and a column for each property. Think of it as a traditional table on steroids. Typical database client functionality is as follows (and it's feasible to achieve each of these in an Ajax interface):

Sorting: Each column can be usually be sorted by clicking on the header. To let the user sort by more than one category, use a sorting algorithm that preserves the order of equal values. Then, users can click on one header followed by the other.
Filtering: The user can filter in to retain data matching certain criteria, or filter out to exclude that data.
Grouping: Data can be grouped by similarity. A large table is essentially broken into smaller tables, in which each item in a smaller table is similar to the other items in that table.
Aggregate calculations: Calculations can be performed across the whole tablefor example, a sum or average for a column, or a sum of the products of two columns in each row.
Editing: Some Data Grids allow fields to be edited.

With the magic of Web Remoting (Chapter 6), the grid can become a Virtual Workspace (Chapter 15, giving the illusion of holding a massive set of data. In reality, the data lives on the server, and each User Action leads to a new server call. Most queries lead to a structured response, such as an XML Message or a JSON Message, containing a list of results for JavaScript rendering, or alternatively an HTML Message with the specific view the user will see. The semantic style has the advantage of encouraging performance optimizations such as Predictive Fetch, which are essential if you want the grid to feel responsive.

14.4.5. Decisions

14.4.5.1. Will the Grid be Read-Only or Mutable?

Editing a table directly can be more productive for experts, though it's often more difficult for novices than editing a single record in a form, since a form usually has a more verbose interface. Grid mutability adds a couple of extra considerations:

You need to validate the data. In a live context, this might mean showing a Progress Indicator during validation, then Highlighting invalid columns.
Cells should ideally morph into input widgets when a user begins editing them. For instance, create a drop-down when the user begins to change a constrained field.

14.4.6. Real-World Examples

14.4.6.1. OpenRico Data Grid example

The OpenRico Data Grid example (http://openrico.org/rico/livegrid.page) shows a list of movies, each with several fields: ranking, title, genre, rating, votes, and year (Figure 14-12). You can scroll down the list and sort each column header. You can also bookmark a position in the table (an example of Unique URLs).

Figure 14-12. OpenRico Grid Movie demo

14.4.6.2. NumSum

NumSum (http://NumSum.com) is a spreadsheet, and spreadsheets are a special, powerful case of Data Grids; they are to Data Grids what Data Grids are to conventional tables (Figure 14-13).

Figure 14-13. NumSum

14.4.6.3. Oddpost

Oddpost (http://oddpost.com/learnmore) is an Ajax mail client with a very similar look and feel to desktop clients like Outlook. The subject headers table is a form of Query-Report Table. As with the OpenRico example, you can scroll through the table and sort by column.

14.4.6.4. Delta Vacations

Delta Vacations (http://www.deltavacations.com/destination.aspx?code=MONTEGOBAY) appears as a Live Search example, but it also serves as an example of filtering. Initially, all hotels in a destination are shown, and typing a search term retains only matching results.

14.4.7. Code Example: OpenRico Data Grid

The OpenRico Data Grid example (http://openrico.org/rico/livegrid.page) uses OpenRico's LiveGrid API. In the initial HTML, there are two tables: one for the column headers and one for the data. Note that the data table declares all the visible table rows, initialized with the values in the first few rows. This is an example of a Virtual Workspace (Chapter 15)the rows will always stay fixed, but their data will change as scrolling occurs:

   <table   cellspacing="0" cellpadding= "0" style="width:560px">     <tr>       <th  style="width:30px;text-align:center"> #</th>       <th  style="width:280px">Title</th>       <th  style="width:80px">Genre</th>       <th  style="width:50px">Rating</th>       <th  style="width:60px">Votes</th>       <th  style="width:60px">Year</th>     </tr>   </table>   <table   cellspacing="0"           cellpadding="0" style="width:560px; border-left:1px solid #ababab">       <tr>         <td  style="width:30px;text-align:center">1</td>         <td  style="width:280px"> Bend of the River</td>         <td  style="width:80px">Western</td>         <td  style="width:50px">7.3</td>         <td  style="width:60px">664</td>         <td  style="width:60px">1952</td>       </tr>       ...   </table>

The grid is initialized on page load, with some configurable options passed in:

   var opts = { prefetchBuffer: true, onscroll: updateHeader };   onloads.push( function( ) {     tu = new Rico.LiveGrid(               'data_grid', 10, 950, 'getMovieTableContent.do', opts )}     );

The grid will then handle all user events. Notice the getMovieTableContent.do argument in its construction. That's the URL that will feed the grid with further data. It must be capable of accepting in certain parameters, such as initial position and number of rows to retain. For example, to load the page initially and scroll all the way to the end (rows 940 and on), the XMLHttpRequest Call (Chapter 6) goes to the following URL: getMovieTableContent.do?id=data_grid&page_size=10&offset=940&_=. What's retrieved are 10 movies starting at 941 in an XHTML format, as shown in the following example. The grid component then updates itself with those rows.

   <?xml version="1.0" encoding="ISO-8859-1"?>   <ajax-response>     <response type="object" id='data_grid_updater'>     <rows update_ui='null' >     <tr>       <td>941</td>       <td convert_spaces="true"> El Dorado</td>       <td> <span style="font-weight:bold"> Western </span> </td>       <td>7.4</td>       <td>2421</td>       <td>1966</td>     </tr>     ...   </rows>   </response>   </ajax-response>

14.4.8. Related Patterns

14.4.8.1. Virtual Workspace

As explained earlier in the "Solution," Data Grids are usually Virtual Workspaces (Chapter 15).

14.4.8.2. Browser-Side Cache

To improve performance, retain data in a Browser-Side Cache (Chapter 13).

14.4.8.3. Predictive Fetch

To improve performance, consider Predictive Fetches (Chapter 13) that preload nearby results and aggregation functions.

14.4.8.4. Progress Indicator

Longer updates should be accompanied by a Progress Indicator (see earlier).

14.4.9. Metaphor

A Data Grid is the natural sequel to the traditional HTML table.

14.4.10. Acknowledgments

Christian Romney (http://www.xml-blog.com/) suggested the idea of a sort-and-filter pattern from which this pattern evolved, and also pointed out the Delta Vacations example.