Result grid formatting on the client-side

1. Problem

Quite often it's required to modify the content and/or appearance of the client-side grid which shows the result of query execution.

Here some examples:

  • Set a format for the values in a particular column.

  • Change the content and/or style of data for certain values. For example, to show negative numbers in red, and positive - black.

  • add a column or row.

  • Turn the values in some column into links to other pages.

In this article, we will show you how to implement such tasks.

2. Tools

To set up needed grid interface, you can use special callback functions of global variable easyQueryViewSettings:

<script>

  window.easyQueryViewSettings = {
      beforeTableRendering: function(dataTable) {
      
      },

      formatColumnHeader: function(dataTable, colIndex, label) {

      },

      formatGridCell: function (dataTable, rowIndex, colIndex, value) {

      }
  }; 
  . . . . . .
</script>

The first parameter for all functions will be an object of DataTable "class" from the library Google Visualization API. You can manage grid's content, using methods of this "class". More about each callback function:

  • beforeTableRendering This one you should use before rendering the grid. You can change meanings of titles and other data, add columns or rows, set up different formats for certain columns.

  • formatColumnHeader This tool is the best one for editing titles of columns. colIndex determines the index of the column, and then label determines actual meaning of the title. This function puts the new title of the column back.

  • formatGridCell This function is to be used before rendering every cell at the grid. rowIndex and colIndex determine the index of the row and column, and valuedetermines actual meaning of the cell. Function puts the new meaning back (even with the HTML settings)

Please note that the script section listed above (an assignment of easyQueryViewSetting variable must be placed before the inclusion of eq.all.min.js and eq.view.grid.js files in your HTML (.cshtml) page.

3. Use-cases

3.1 Formatting values in result grid

To set format values in result grid, you can use callback function beforeTableRendering, which is evaluated in global variable window.easyQueryViewSettings. To set needed format in a separate column you can use a special class formatters, described in detail here.

Here is an example of the format for columns with data type date and number. Formatting other data types is implemented in a similar way.

window.easyQueryViewSettings = {
    beforeTableRendering: function (dataTable) {
        var dateFormatter = new google.visualization.DateFormat({ pattern: "yyyy-MM-dd" }); //setting special pattern for date value
        var intFormatter = new google.visualization.NumberFormat({ groupingSymbol: "", fractionDigits : 0}); //removing grouping symbol and decimal part for integer values

        var colCount = dataTable.getNumberOfColumns();
        for (var i = 0; i < colCount; i++) {
            var colType = dataTable.getColumnType(i);
            var colProps = dataTable.getColumnProperties(i); 
            if (colType == "date") { //using DataTable column type for choosing formatter
                dateFormatter.format(dataTable, i);
            }
            else if (colProps.dataType == "Int32" || colProps.dataType == "Int64") { //using C# data type for decision
                intFormatter.format(dataTable, i);
            }
        }
    },
  . . . . . . . .
}

Sometimes it's necessary to render the values in some column of result grid as links to some "Details" page. You can do it using formatGridCell callback function. This function is called for each cell in result data grid. The list of parameters contains:

  • dataTable - a reference to DataTable object (from Google Visualization library)
  • rowIndex - row's index
  • colIndex - column's index
  • value - current value for this cell. You can change that value in any way you need (e.g. wrap it into some a tag to make a hyperlink).

In the following example, we convert all values in "Order ID" column into links to "Order Details" page for a particular order:

window.easyQueryViewSettings = {
  . . . . . . . .
  formatGridCell: function (dataTable, rowIndex, colIndex, value) {
  var colId = dataTable.getColumnId(colIndex);
  var props = dataTable.getColumnProperties(colIndex);
  if (colId == "OrderID" || (props && props.table == "Orders" && props.field == "OrderID")) {
  var uri = "/Order/Details/" + value;
  value = '<a href="' + uri + '" >' + value + '</a>';
  }
  return value;
  }
};

3.3. Setting a column with checkboxes

Sometimes it's necessary to add column with checkboxes into result grid - so users will be able to select several rows and pass them for some additional processing (for example to export only selected rows). We can use beforeTableRendering and formatGridCell callbacks to get the necessary result:

<script>
  . . . . . . .
window.easyQueryViewSettings = {
    beforeTableRendering: function(dataTable) {
        dataTable.insertColumn(0, "string", " ");
    },

    formatGridCell: function (dataTable, rowIndex, colIndex, value) {
        if (colIndex == 0) {
            return '<input type="checkbox" name="selectedRows" value="' + rowIndex + '" />';
        }
        else
           return value;
        }
    }
</script>

3.4. Showing "No record" message for empty result set

It would be good to show some message (e.g. "No record found") when the query returns an empty result set. Like in the previous example we can use beforeTableRendering callback to perform this task.

The basic idea will be to define an additional panel hidden panel which shows necessary message right near the result panel and then show it when the result set is empty.

Here is the code:

<script>
  . . . . . . .
		
  window.easyQueryViewSettings = {
  beforeTableRendering: function(dataTable) {
  var resultPanel = $("#ResultPanel");
  var emptyResultPanel = $("#EmptyResultPanel");
  if (dataTable.getNumberOfRows() == 0) {
  resultPanel.hide();
  emptyResultPanel.show();
  }
  else {
  emptyResultPanel.hide();
  resultPanel.show();
  }
  }

  };
</script>

 . . . . . . .

<div id="EmptyResultPanel" style="display:none"><p>Record no found</p></div>
<div id="ResultPanel" class="result-panel-content">

 . . . . . . .