I've been busy for the past couple of weeks and I've made some great progress. I'm now able to support item types like select lists, check boxes etc. (using column LOV definitions), footer summaries based on columns with "Sum" checked under the apex report column definition, validations, custom formatting (using JSON embedded in the column comments section), and saving of data to a collection. I've run into a few issues along the way though and I'll discuss them in a bit of detail...



First was the fact that when editing rows and clicking on the next row, jqGrid performs a server side post of the row data you've just been editing. The problem with this is that we may not want to update the actual data in the backend tables at this point, and it's not exactly ideal to lock the rows whilst we wait for a commit, to be honest I don't think it's even possible in APEX to support this given it's a stateless environment... anyway so the point is I needed a way to keep track of the data changes server side, and to make life even more difficult I needed to ensure that if a grid reload/pagination is performed that we're querying this set of updated data and not the original.

I decided that using a collection was the ideal way to store these changes also given the fact that we can easily generate checksums when processing, to both identify if the row data has changed and also for concurrency checking to make sure we don't overwrite someone else's updates. Problem was though.... how would I get the jqGrid to report off the collection and not the actual query report I've written. Note: the key to this integration is that we simply set the region/report template for a standard APEX report and we automatically get an jqGrid, I want to ensure there is minimal/nothing else you need to do, otherwise what's the point? We use APEX because we want to be productive, same goes for integration, we don't want to have to write the integration every single time we need a grid.

The solution to report off the collection was to automatically create an application process which builds the collection based on my query source and also create a secondary report region (conditional display set to "Never") which copies the original and makes a few tweaks to the query source which reports off the auto created collection. These are both created by using calls from the wwv_flow_api and I gained inspiration from APEXGEN to get an idea of what I needed to do. I've got an application level process which creates this report region and process, it runs on every page "Before Header" and queries the data dictionary to determine if they need to be created.



The trick for getting the report to read off the collection for grid load/reload/pagination/sorting/filtering etc. is handled by my wrapper application process "jquery.widget" which hands of the AJAX calls to a package procedure which is a wrapper around the APEX PPR routine. Within this wrapper I query the current page and check for this secondary report region, if it exists I simply change the URL and swap the REGION_ID with the region id of my collection report along with calculating any additional information for query rowcount, filtering, summing etc. You may think it's a complicated PLSQL routine but it's only 350 lines of code (including comments), APEX still performs the majority of the work as I'm simply re-using the PPR operation, and updating session state to cater for the other requirements. The beauty is that I can reference application items in my report template so that's how I'm able to get all this information into a single JSON object.



I'm aiming to have this packaged for release and documented by end of March/April (The reason for the time lag is that I'm doing this in my own time which I have less and less of these days with a demanding GF and two kids). However if you've stumble across this blog in the interim and would be keen on using this integration before then I'd be happy to provide you the integration package (with a few rough edges) if your happy to conduct some testing and provide some feedback to me.