We’re steadily progressing on the ExtJS integration into APEX and we’ve probably hit the hardest challenge thus far trying to enable grid filtering (see the following Ext example) based simply on setting values in the “Reference Table Owner”, “Reference Table Name”, and “Reference Column Name” attributes in the Column Attributes settings under the “Report Attributes” tab. However APEX has proved once again that it’s up to the challenge and we have conquered this task with only mild levels of frustration.

We’ve added the filter plugin to our grid (which already contained the summary plugin) simply using “plugins: [summary, filtering]” within the grid and pagingToolbar widgets defined in the report region template, to ensure that the Store is refreshed on any change.

plugins:     [summary#REGION_ID#,filter#REGION_ID#],

In order to setup our JSON object at page load time with the correct filter settings (which feeds the grid widget with all the required settings) we needed to use the above mentioned settings to interrogate the Database Data Dictionary to work out the data type of the column, as we need to set the appropriate data type for the Ext filter, i.e. as it will choose the right filter to display, i.e. Date, Numeric, String filters etc. Here’s an extract of the JSON filter object..

"filter" : [
{ "type" : "string", "dataIndex" : "NAME" },
{ "type" : "int", "dataIndex" : "GEONAME_ID" },
{ "type" : "date", "dataIndex" : "MODIFICATION_DATE", "dateFormat" : "d-M-Y" },
{ "type" : "int", "dataIndex" : "POPULATION" }
]

When working with dates we needed to perform a translation of Oracle date formats into Ext date formats, see the following URL for a full documented set.

We then setup the Grid column and JsonReader mapping settings with the correct renderer and type respectively, another JSON example…

"columns" : [
{ "name" : "NAME", "dataIndex" : "NAME", "header" : "Name", "sortable" : true, "align" : "LEFT", "width" : 100 },
{ "name" : "GEONAME_ID", "dataIndex" : "GEONAME_ID", "header" : "Geoname ID", "sortable" : true, "align" : "LEFT", "width" : 100 },
{ "name" : "MODIFICATION_DATE", "dataIndex" : "MODIFICATION_DATE", "header" : "Modification Date", "sortable" : true, "align" : "CENTER", "width" : 100, "renderer" : Ext.util.Format.dateRenderer('d-M-Y') },
{ "name" : "LONGITUDE", "dataIndex" : "LONGITUDE", "header" : "Longitude", "sortable" : true, "align" : "RIGHT", "width" : 100 },
{ "name" : "POPULATION", "dataIndex" : "POPULATION", "header" : "Population", "sortable" : true, "align" : "RIGHT", "width" : 100 },
{ "name" : "REGION_ID", "dataIndex" : "REGION_ID", "header" : "Region Id", "sortable" : false, "align" : "LEFT", "width" : 100 } ],

"mapping" : [
{ "name" : "NAME", "mapping" : 0 },
{ "name" : "GEONAME_ID", "mapping" : 1, "type" : "int" },
{ "name" : "MODIFICATION_DATE", "mapping" : 2, "type" : "date", "dateFormat" : "d-M-Y" },
{ "name" : "LONGITUDE", "mapping" : 3 },
{ "name" : "POPULATION", "mapping" : 4, "type" : "int" },
{ "name" : "REGION_ID", "mapping" : 5 }
]

We then pass all the parameter arguments in the URL and set session state accordingly with a number of “Hidden & Protected” items on the page, here’s an example of the parameters that will be passed via the AJAX Http post…

filter0_data_comparison
filter0_data_type		string
filter0_data_value		wal
filter0_field			NAME
filter1_data_comparison		eq
filter1_data_type		date
filter1_data_value		30-Mar-2009
filter1_field			MODIFICATION_DATE
filter2_data_comparison		gt
filter2_data_type		numeric
filter2_data_value		50000
filter2_field			POPULATION

We simply then add the filters to the report SQL definition and thats basically it from a high level perspective…

SELECT ....
FROM   geoname
WHERE ....
AND (modification_date = to_date(:P9_EXT_MODIFICATION_DATE_EQ, 'DD-MON-YYYY') OR  9_EXT_MODIFICATION_DATE_EQ IS NULL)
AND instr(upper("NAME"),upper(nvl(:P9_EXT_NAME,"NAME"))) > 0
AND (geoname_id =  9_EXT_GEONAME_ID_EQ OR  9_EXT_GEONAME_ID_EQ IS NULL)
AND (geoname_id >=  9_EXT_GEONAME_ID_GT OR  9_EXT_GEONAME_ID_GT IS NULL)
AND (geoname_id <=  9_EXT_GEONAME_ID_LT OR  9_EXT_GEONAME_ID_LT IS NULL)
AND modification_date BETWEEN to_date(nvl(:P9_EXT_MODIFICATION_DATE_GT,'01-JAN-0001'), 'DD-MON-YYYY') AND to_date(nvl(:P9_EXT_MODIFICATION_DATE_LT,'01-JAN-2999'), 'DD-MON-YYYY')
AND (population >  9_EXT_POPULATION_GT OR  9_EXT_POPULATION_GT IS NULL)

We’ve encountered a number of Ext bugs with the fixes posted here which you may find useful:

1. GridFilters.js - Support for float and int data types

   getFilterClass: function(type){
   /*customizations to handle data types*/
   if( type=='int' || type=='float' ) {
   type = 'numeric';
   }
   return Ext.grid.filter[type.substr(0, 1).toUpperCase() + type.substr(1) + 'Filter'];
   }
   

2. StringFilter.js - comparison was orignally not set which gave mixed filtering results as arrays are not cleared, i.e. comparison would contain a value from a disabled date filter which would cause an error in our implementation.

   serialize: function() {
   var args = {comparison: '', type: 'string', value: this.getValue()};
   this.fireEvent('serialize', args, this);
   return args;
   },
   

3. GridFilters.js - override “buildQuery” function to replace brackets with underscores as mod_plsql does not accept braces within parameter arguments

   buildQuery: function(filters) {
   var p = {};
   for(var i=0, len=filters.length; i<len; i++) {
   var f = filters[i];
   var root = [this.paramPrefix, '', i, ''].join('');
   p[root + '_field'] = f.field;
   
   var dataPrefix = root + '_data_';
   for(var key in f.data) {
   p[[dataPrefix, '', key, ''].join('')] = f.data[key];
   }
   }
   return p;
   },
   

4. EditableItem.js - on line 26 update the style to include “position:static;” in order to get the menu filter icons to render properly in Firefox and IE

   onRender: function(container){
   var s = container.createChild({
   cls: this.itemCls,
   html: '<img src="' + this.icon + '" class="x-menu-item-icon" style="position: static;margin: 3px 3px 2px 2px;" />'
   });
   

5. CSS – the following CSS was used to alter the column heading when a filter was enabled.

   .x-grid3-hd-row td.ux-filtered-column {
       font-style: italic;
       font-weight: bold;
   }
   

The end result after a bit of grind is that we can now create X many Ext Grids based on any query in APEX via the Report wizard without having to write/modify another single line of javascript and yet still be able to customize all of the grid features. We can create a new Ext grid in under 2 minutes which supports pagination, grouping, sorting, column hide/show, and filtering. I’d throw a challenge out to any other development product that can achieve this level of productivity. APEX rules!