From Ajax Patterns
Tags: Database Query Report Summary Table
In A Blink
Diagram A query-report interface
This pattern should be called a *table*, not a grid.
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.
How can you help users explore data?
- 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.
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".
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.
JQGrid PHP Component
JQGrid based PHP CRUD Component : Free to use (code obfuscated), You have to buy full source code after little payment. Based on Jquery/PHP. It allows you to have major functionalities in very few lines:
- Multilevel Subgrid option (n-level)
- Events for Add/Update/Delete Custom implementation
- Multiple Themes (ThemeRoller)
DrasticGrid : opensource (GPL) grid based on PHP and MooTools' JSON AJAX. It allows users to edit a MySQL database. ComboBox are supported.
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.
Main advantages to other grids:
- tree capability, including dragging rows and downloading children by AJAX
- advanced editing, including editing masks, runtime and postprocess validation, multiline editing, editing types and formats, automatic uploading by AJAX
- interactive Gantt chart creating, including dependencies and dynamic updating, like in MS Project
- advanced cell formulas, expressions and calculations like in MS Excel
- extended filters like in MS Excel or by any custom expression
- searching like by Google
- printing capabilities, export to Microsoft Excel
- very fast sorting, by one or more columns
- various paging types to display nearly unlimited count (1 000 000) of rows using AJAX and pager component
- automatic grouping rows to tree according to column values (like PivotTable in MS Excel)
- fixed (frozen, locked) columns and rows on all sides
- moving columns, freezing or unfreezing columns by mouse, resizing, hiding/showing columns
- selecting rows, cells or cell ranges by key or mouse
- various cell types like text, number, date, check box, radio, textarea, combo box, image, link or any other HTML
- synchronization data with server
- master / detail relationship
- dynamic displaying of external objects like Adobe Flash, Microsoft SilverLight
- fully customizable CSS styles and key and mouse navigation action schemas
- multilanguage, also RTL support for Arabic languages, including Hirji calendar
* 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.
Jack Slocum's Ext library includes a grid component with sorting, resizable columns, drag and drop column reorder and AJAX loading of various data sources including XML and JSON.
OpenRico Data Grid Example
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 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 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.
They became part of mail.yahoo.com
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 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.
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 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.
Sigma Grid Here is a list of sigma grid features.
* IE 6.0+ \ FireFox 2.0+ \ Safari 3.0+ \ Opera9.0+ \Chrome supported. * Online printing, to make it easy to print tabular data in WYSIWYG way. * Built in button, no coding. Bar diagram, line diagram and pie diagram built in. * Translation tabular data into diagram without any server-side coding. * Extensibility enable you to replace built-in cell editor with custom external component, present cell data as your customers' wish. * Data filter enables grid to present the data users are interested in only. * Ability to enable developer to work out as complex header as they wish. * Nested header, image header, dropdown list control embeded header are all supported. * Export to PDF, XML, CVS & Excel files * LGPL license and commercial license both available.
As explained in the Solution, Data Grids are usually Virtual Workspaces.
:: Browser-Side Cache ::
To improve performance, retain data in a Browser-Side Cache.
To improve performance, consider Predictive Fetches that preload nearby results and aggregation functions.
Longer updates should be accompanied by a Progress Indicator.
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?