From Ajax Patterns

Evidence: 1/3

Tags: Database Query Report Summary Table

Contents 1 In A Blink 2 Goal Story 3 Problem 4 Forces 5 Solution 6 Decisions 6.1 Will the Grid be Read-Only or Mutable? 7 Real-World Examples 7.1 Ext JS Livegrid extension 7.2 EJS TreeGrid 7.3 JustAjax Table 7.4 Ext Grid 7.5 OpenRico Data Grid Example 7.6 NumSum 7.7 Oddpost 7.8 Delta Vacations 7.9 Nitobi Ajax Data Grid Component 7.10 EditGrid 7.11 DHTMLX Grid 7.12 Zapatec Grid 7.13 r.a.d. grid 7.14 ComponentArt Grid 7.15 Backbase Grid 8 Alternatives 8.1 Alternative 9 Related Patterns 9.1 Virtual Workspace 9.2 Browser-Side Cache 9.3 Predictive Fetch 9.4 Progress Indicator 10 Visual Metaphor 11 Want to Know More? 12 Acknowledgements

Ajax/Javascript Programming and Usability in "Ajax Design Patterns" Book

In A Blink

Diagram A query-report interface

Goal Story

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

Problem

How can you help users explore data?

Forces

• Ajaxian systems have their own databases, as well as access to external systems.
• Users need a way to explore all that 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 that data.

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 Ajaxian context:

• Sorting: Usually, each column can be sorted by clicking on the header. To let the user sort by more than one category, use a sorting algorithm which 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, where each item in a smaller table is similar to the other items in that table.
• Aggregate Calculations: Calculations can be performed across the whole table. For 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.
• Locking or Panelling: Some grids allow the locking of some columns, such as a description field, so that one can see what is being edited or compare far-apart columns. Variations are two-panel grids where a slider allows you to create two panels with independent vertical scrolling (but linked horizontal).
• Column-Sizing - Dragging the border between column headings allows one to resize a column as displayed.
• Arrow-based Navigation: - Heavy users of grids prefer that arrow keys can be used to navigate to adjacent cells. Left and right arrows can be tricky because they are also used for in-field movement. Generally cells go in and out of edit mode such that once you type characters or place the cursor in a cell, it changes to cell-edit mode. Up/down arrows should take one out of cell-edit mode. Marking the edit cell with a different color when in cell-edit mode is also a nice touch.
• Active Cursor - In this approach when one scrolls up or down, the grid can automatically populate itself with the requested data from the server. This way it does not have to upload the entire table, but rather the portions being viewed. However, implementation of this can be tricky, especially for complex queries. In such a case, Query By Example, tree-based selection based on regions, first letter of name, etc. may be more appropriate. Active cursing is thus more of a bonus than a requirement.
• Data Dictionary Relationship - Ties to existing Data Dictionary to avoid reinventing the "column wheel".

With the magic of Web Remoting, the grid can become a Virtual Workspace, 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 Semantic Response containing a list of results for Javascript rendering, or alternatively an HTML Response with the specific view the user will see. The semantic style has the advantage of encouraging performance optimisations such as Predictive Fetch, which are essential if you want the grid to feel responsive.

Decisions

See above for implementation options.

Will the Grid be Read-Only or Mutable?

Some grids are read-only and others are mutable. Editing a table can be more productive for experts, though often more difficult for novices than editing a form. A form is more descriptive and judicious layout helps provides a better snapshot of a single record. Mutability adds some extra considerations:

• You need to validate the data. In a live context, this might mean showing a Progress Indicator during loading and Highlighting invalid columns.
• Ideally, cells should morph into input widgets when a user begins editing them. For instance, create a dropdown when the user begins to change a constrained field.

Real-World Examples

Ext JS Livegrid extension

Ext JS Livegrid extension The Ext JS Livegrid extension is a user extension which allows for scrolling through large datasets without the need of paging. Data is buffered in an underlying store and displayed as requested. A special selection model is capable of storing selections which are not in the current buffer. An adjustable predictive fetch algorithm let's you control the behavior of how frequent new data will be requested and how much data will always be available in the buffer.

EJS TreeGrid

EJS TreeGrid is cross-browser DHTML (AJAX) component written in pure JavaScript to display and edit data in table, grid, tree or grid with tree. Automatic calculations, sorting, filtering, grouping. Printing and export to Excel. Able to display 1 000 000 rows.

JustAjax Table

JustAjax Table The JustAjax PHP Grid performs all operation with tables "on-fly" using Ajax requests. Key features:

   * Supports all popular web browsers.
   * Populates the data into the cells automatically.
   * On-fly scrolling, sorting, editing, adding, filtering.
   * PHP/MySQL backend.
   * Any number of records.
   * Flexible design.
   * Easy to Use.

JustAjax Table MS Office 2007 Example JustAjax Grid Simple Demo

Ext Grid

Jack Slocum's Ext library includes a grid component with sorting, resizable columns, drag and drop column reorder, column locking and AJAX loading of various data sources including XML and JSON.

OpenRico Data Grid Example

Notes: Application error (Rails)

The OpenRico Data Grid example shows a list of movies, each with several fields: ranking, title, genre, rating, votes, and year. 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).

NumSum

NumSum is a spreadsheet, and a spreadsheet is a special case of a Data Grid; it does to data grids what data grids do to conventional tables.

Oddpost

Oddpost was acquired by Yahoo. Link is broken. Keeping prior info for historical reasons.

Oddpost is an Ajaxian 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.

Delta Vacations

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

Nitobi Ajax Data Grid Component

Nitobi Ajax Grid provides a rich Ajax Grid Control with many features. It allows viewing and editing of tabular data in web page. One interesting and unique feature is the ability to cut-and-paste content from Excel, and when you do this, it's possible to retain the original formatting (boldfacing, etc.). Nitobi Grid also has other powerful Ajax features such as column resizing, keyboard navigation, inline editing, live scrolling and auto-saving.

EditGrid

EditGrid is an online spreadsheet service that allows you to embed a both editable and readonly spreadsheets in any website. EditGrid has most spreadsheet features you can find in Microsoft Excel and OpenOffice.org, e.g. multiple-worksheet, formulas, merge cells, etc. You can also make changes to the spreadsheet through Web API and Grid API. Spreadsheet data are stored in the EditGrid website by default, and optionally exported to any WebDAV-enabled server using Remote Workbook API.

DHTMLX Grid

DHTMLX Grid Ajax-enabled JavaScript grid control designed to represent, edit and sort tabular data. Grid content can be imported from XML data source or created using JavaScript methods. Advanced functionality includes paging support, cells merging (colspan), frozen columns, multiline headers, CSV import/export, drag-and-drop and extended server-side support.

To allow greater flexibility, dhtmlxGrid uses unique mechanism called eXcell (extended Cell) to define data format and way of editing data for each cell. The grid already supports a wide variety of cell formats such as text, image, combo box, list box, checkbox, radio button, calendar, etc.

Zapatec Grid

Zapatec Grid With an large array of features the grid is a versatile tool that lets your users sort, slice, dice and search your data. Additionally, you can configure the grid as an editable dataset where your users can update their data. You can also add flash charts generated from the grid data that change as a result of users' actions.

r.a.d. grid

r.a.d. grid

ComponentArt Grid

ComponentArt Grid

Backbase Grid

Backbase Grid

Alternatives

Alternative

Related Patterns

Virtual Workspace

As explained in the Solution, Data Grids are usually Virtual Workspaces.

Browser-Side Cache

To improve performance, retain data in a Browser-Side Cache.

Predictive Fetch

To improve performance, consider Predictive Fetches that preload nearby results and aggregation functions.

Progress Indicator

Longer updates should be accompanied by a Progress Indicator.

Visual Metaphor

A Data Grid is the natural sequel to the traditional HTML table. It's concepts are also familiar to spreadsheet users.

Want to Know More?

Acknowledgements

Christian Romney suggested the idea of a sort-and-filter pattern from which this pattern partly evolved, and also pointed out the Delta Vacations example.