Interactive Report filtering in Apex

Interactive Report filtering in Apex

I like interactive reports, and how they can put some serious query potential in a user’s hands with filters. But there are still some places where it falls a bit flat on its face. One such place is the popup you get when you click on the header of a column and search is enabled for that column.

For example, this report I have here is based on a table with only 27k records, and is meant as a replacement for an old Forms form. Users are used to working with IDs, and it is sometimes astonishing how they know these series of numbers by heart, or pick up new ones. So before, they went in the form, entered query mode, typed in the ID they had to look for (these IDs are everywhere!) and hit query: boom, here are your results. Find all addresses starting with 10? (which is a piece of the code referring to a country code) Enter ‘10%’.

What is the issue?

Now how do users like to work with the interactive reports? Well, probably the same way I do: least amount of clicking and follow the most intuitive path. Clickable headers? Yes please! So, I click this ID column header, and am presented the best things: sorting, hiding, breaking, and searching.

Great! Let’s start finding some things.

 

User: “What the …? Where is my id starting with 1054? Is this right? It IS right there isn’t it? Why is it not in the box? How do I have to search for it?”
Let’s scroll down when nothing is typed in; maybe it’ll be more downwards…


User: “Eh? I scrolled all the way to the bottom and my values aren’t there? HELP!”
Let’s take a look at another report.



Notice the warning up top ‘more than 10k rows’.
Now I click on the “Station” header:


*BONK*(Sound of user falling from chair or hitting head on the desk)

Which is when I swoop in and take care of this by telling them: “Yes I know, sorry. You can’t actually use the box when there is a certain amount of unique records trespassed. You’ll need to go through Actions > Filter > select your column and operator, enter your search value and then click Apply (no, don’t hit Enter because Enter doesn’t work, I know, sorry).”
And they usually hang up when they see the Filter screen, or at the very least the operator dropdown. No matter they’ve worked their whole life with a LIKE operator in forms, when you don’t actually ever write SQL you wouldn’t know nor care either.


For the record: yes, the filter screen works great. But I consider it a workaround in this case. My users don’t want to go through that filter step each time they need to look up a record. I can make them of course; say it is within the limitations of this tool. But I feel like a tool each time I need to explain this.
So the actual problem here is the amount of retrieved values, and even distinct values when an amount has been surpassed! I checked out what happened through Firebug and this lead me to the interactive reports javascript file. When the header is clicked, an ajax call is done to retrieve the settings for the selected column: sorting, hiding, breaking, filtering. If filtering is enabled, values are fetched and returned, and kept in the DOM. But not ALL values; and the retrieved values are STATIC. STATIC!

Breaking out my toolbox, I set out to change this. Thankfully, I had a large piece of my work done already thanks to my form record navigation based on an IR query. Since the searchbox has to find the distinct values of the selected column within the IR query, I could reuse some of that code.


Now when a popup is shown, I automatically hide the values scroll list since it is useless. The search box I then turn into a jQuery Autocomplete widget, with an Ajax source. This ajax callback searches the IR query for the distinct values, and narrows down the values to the entered value. I wrapped this all up in a nice plugin too! Let me point out one thing though: I can’t stop the ajax call that apex does from getting its own list of values since it is too baked in for me to do anything about it.

Demo Application

Found here: http://apex.oracle.com/pls/apex/f?p=54687:32
Log in with apex_demo / demo. For example, click dname and select “Sales”. Then click ename: you will only see the names for dname=sales here. Also note the hiredate column: no range selection.
My settings: search behaviour: contains, filter behaviour: take existing filters into account, value fetching delay: 750, min length before fetching: 1

So how does it work now?

When the popup is opened, the scroll list will no longer be shown.


When the user types in some values, the autocomplete kicks in after a short delay, and a loading icon will be shown during the fetching of the values.



The values will then be shown. The first item in the dropdown will always be highlighted, so when a user presses enter, that value will be selected. Handy when they narrowed the list down to one match and want to select it without having to use the arrow keys or mouse.



As you can see, I now find my value I couldn’t find with the standard scroll list!





When I select the value, the filter is added, which is identical behavior to the standard list.

Adding the plugin to a page:

Implementation

Identification

Name it something convenient, I name mine ‘IR AC on heading’

When

Select “Page Load” for event, so the plugin will run when your page has finished loading.
 True Action
Select the plugin from the actions list, you can find it grouped under ‘Execute’

As for settings:

Search Behaviour allows you to alter the way results are fetched. The standard ‘contains’ is like the default behavior, and searches for any occurrence of the search value in the values of the column. Like allows users to filter the results with “%” and “_”, see the next screen.


Filtering behavior
With Filtering on:


This also means that for instance my Ordernumbers would only display the orders available for line V22, and I can only pick a value from those. With all values shown I could pick any number, which could result in a no-data-found of course.

With filtering enabled: (542 is my first value!)


With filtering off: (3 is my first value!)


Value Fetching Delay: how long before the fetching kicks in after the first keystroke. This may be useful when the users usually type in a long series of characters, and you don’t want too many ajax calls. Useful when your data-set can be very large and lots of matches are possible.
Min length before fetch: how many characters have to be present before fetching will kick in.

Quick peek under the hood:

The javascript code which is run onload:

function create_autocomplete(ajaxIdent, fetchDelay, charAmount){
   // _Finished_Loading is called when the IR is done with a GET action
   // because the posts are synchronous in this report, and no events
   // or hooks are available, the best way to preserve functionality
   // yet extending it is to override the original function, yet 
   // keep the base code
   // apexafterrefresh cant be used since it is not triggered after
   // the widget ajax
   var or_Finished_Loading = gReport._Finished_Loading;
   gReport._Finished_Loading = function(){
       //overriden, but still have to call orinigal!
      or_Finished_Loading();
      //SORT_WIDGET is the widget containing all the header elements
      if(gReport.current_control=='SORT_WIDGET'){
         // hide the original dropdown box
         $("#apexir_rollover_content").hide();
         //let's do an initial search so the user has some initial 
         //options and doesn't have to start typing before being
         //prompted with some. Also great when there are not many
         //distinct options
         //The set minlength has to be disregarded for this though.
         var lSearchField = $("#apexir_search"), 
             lMinLength = lSearchField.autocomplete("option","minLength");
         //empty the search field, but dont trigger a search if length=0
         lSearchField.autocomplete("option","minLength", 5);
         $("#apexir_search").val('');
         lSearchField.autocomplete("option","minLength", 0);
         lSearchField.autocomplete("search",'');
         lSearchField.autocomplete("option","minLength", lMinLength);
         //the search field has to receive focus, otherwise the 
         //values list will not be hidden when the user clicks anywhere
         //but the popup. The popup would be hidden, but not the values.
         //Either case, it is good form to set focus here, a user would 
         //expect this behaviour.
         lSearchField.focus();
      };
      //alert('gReport finished loading');
   };

   //prevent the dropdown from showing up when user starts typing
   $("#apexir_search").removeAttr("onkeyup");

   //convert the item into an autocomplete item
   $("#apexir_search").autocomplete({
      source: function(request, response){         
         $.post('wwv_flow.show', 
                {"p_request"      : "PLUGIN="+ajaxIdent,
                 "p_flow_id"      : $v('pFlowId'),
                 "p_flow_step_id" : $v('pFlowStepId'),
                 "p_instance"     : $v('pInstance'),
                 "x01"            : gReport.current_col_id.substring(7),
                 "x02"            : request.term,
                 "x03"            : $v('apexir_REPORT_ID')}, 
                 function(data){
                    response($.parseJSON(data));
                }
               );
      },
      select: function(event, ui){
         //when making a selection, a filter has to be added to the IR
         //this is the same code executed when a user selects a value
         //from the original dropdown box
         //ltemp array: column id, operator, search value, -, -
         //-> array for htmldb_get action
         var lTemp = [gReport.current_col_id,'=',ui.item.value,'',''];
         gReport.get.AddArray(lTemp,1);
         gReport._Get('ACTION','COL_FILTER');
      },
      autoFocus: true, //automatically highlight first item
      delay: fetchDelay, // wait a bit before sending a request
      minLength: charAmount, // how many chars have to be present
      open: function(event, ui){
         //when the popup opens, the search is triggered and the ajax
         //will fire up. If a user however clicks somewhere so the 
         //popup is hidden again, the value list should not be 
         //displayed. Without this check, the list would be attached to
         //the document top left.
         if($("#apexir_search").is(":hidden")){
            $(this).autocomplete("close");
         };
      }
   });
}; 

Special note on gReport._Finished_Loading : this is in override of the function in interactive reports. I do this because there is no hook for an after-loading event in the case of the popup widget. The “apexafterrefresh” event is only fired in some cases, and the widget is not one of them. Finished_Loading however is, and it is safe to override it. I do keep the original function alive, as I actually just want to extend it.
 

Provided code

You’ll find 2 plugin files in the zip: 1 with and 1 without package calls. If you can, put the package in your database as it’ll save on the amount of code in the plugin. The no-package-plugin has comments stripped out in the plsql code too to save on space, but that is why the source files are there.
(The package APEX_IR contains code also found in my record navigation plugin. I plan to change the package there to so it is up to date.)

Limitations and remarks

Date columns: date columns get values retrieved by their to_char values. The original popup with the date range restriction filters are not there. As of yet I’m still unsure of how to best solve this. One way would be to simply allow the standard box to show here instead of an autocomplete. I’d love to provide 2 date picker items so a ‘between and’ filter could be put up, but i can’t find out how to provide the second date unfortunately - for now.
Other column types: if you’re using things like blob, html tags, apex_item, etc in your report, this may all fall flat on the face.
Amount fetched: I only fetch up to 500 values, which should be more than plenty as I don’t believe a user will willingly scroll through a thousand entries: that’s what the progressive searching is for.
Newlines and double quotes: these are removed since they break the JSON return parsing.
Saved reports, aka multiple versions of an ir: I haven’t really tried this out, but i believe everything should work without problem. The only thing you might need to change is the dynamic action: if the dropdown is not returning the correct values after you changed to another saved report, then try changing the DA from “page load” to “after refresh” on the IR region.

Edit: computations also break the functionality. No solution for this yet.

Debugging

Ah, this actually is a bit harder. I’d strongly suggest using Firefox + the Firebug plugin to trace the ajax call. Due to the most code being ajax calls, i can’t really put debug messages in. If the ajax call is not working (for example: the loading graphic just keeps going and going, it usually means an erroneous return), then first take a look at the response. It could be you find html for an error page there and you can glean the errorcode from there, since this’ll be the sqlcode from the plsql part of the callback.
Please note that when you click a header 2 ajax callbacks will be fired:

The first callback is the default call issued by apex when a header is clicked. This retrieves the settings for the column, and whether the sort/break/hide buttons should be shown. If you’d look at the response, you’d also see the default unique values list, which is not something i can stop since this is retrieved due to the ‘search allowed’ settings.

The second callback is the callback to the plugin. x01 is the column being searched on, x02 is the search value, x03 is the report id. The response would contain the possible (or at least up to 500) values in json format.
From there, i only have one advice: use the code from the package you can find in the source code folder. Look at procedure “get_column_ac_values”, and uncomment the “dbms_output.put_line” lines. Now run the code from a sql-command window, providing the correct parameters.
This is the spec for “get_column_ac_values”:

   FUNCTION get_column_ac_values
   (
      p_app_id             IN  NUMBER,   -- application id (APP_ID)
      p_session_id         IN  NUMBER,   -- session id (APP_SESSION)
      p_column_id          IN  VARCHAR2, -- the column for which to get the next/prev vals
      p_value              IN  VARCHAR2, -- the current search value for p_column_id IF NULL THEN ALL
      p_page_id            IN  NUMBER,   -- Page number of the interactive report
      p_report_id          IN  NUMBER,   -- id of the selected IR, this can be null
      p_use_session_state  IN  BOOLEAN DEFAULT TRUE, -- true for using apex session state bind vars. If False p_binds+vals are to be filled.
      p_binds              IN  DBMS_SQL.VARCHAR2_TABLE, -- plsql table with bind variables
      p_binds_val          IN  DBMS_SQL.VARCHAR2_TABLE, -- plsql table with bind variables VALUES
      p_search_behaviour   IN  VARCHAR2 DEFAULT 'CONTAINS', -- LIKE, CONTAINS: how results are fetched
      p_filter_behaviour   IN  VARCHAR2 DEFAULT 'FILTER' -- FILTER, ALL: filtering of results
   )
   RETURN CLOB

Some of the parameters you can, again, retrieve from the plugin ajax call, but the most important parameter is the p_report_id.
Here is an example plsql call to the procedure. One issue though: if an error is thrown then this won’t work in an Apex SQL Command window. it will only show the sql error message, and no dbms_output at all. I suggest running this code in a sql sheet in for example sql developer (which you can get at oracle.com, it is a free tool).
Take note of parameter p_column_id: this is the name of the column searched for in the IR query. The actual name of the column in the query, so if you have aliased that column, then that alias will need to be provided, and not the base column name and neither the heading you can alter in the report attributes!
p_use_session_state: if you run this code from a sql command window, you won’t have session state for your items. If your query contains bind variables, you will need to provide the value for those! Set this parameter to FALSE, and provide the name of the bind vars in your query through parameter p_binds, and the values in p_binds_val. The relatation of binds and values in both these arrays is a 1-on-1 relation: the bind in position 1 in p_binds has the value stored in position 1 in p_binds_val.

DECLARE
  binds_table    DBMS_SQL.VARCHAR2_TABLE;
  values_table   DBMS_SQL.VARCHAR2_TABLE;
  v_retval       CLOB;
BEGIN
  -- If your query for example references a page item,
  -- then you will have to provide this to the query.
  -- If you have no bind vars in the query, you still
  -- need to provide the empty variables though.
  --
  -- binds_table(1) := 'P10_SOME_FIELD';
  -- values_table(1) := 'SOMEVALUE';
     
  v_retval :=
  apex_ir.get_column_ac_values
  (
     p_app_id              => 130,
     p_session_id          => 3214271424298960,
     p_column_id           => 'DELADR_ID',
     p_value               => '',
     p_page_id             => 11,
     p_report_id           => 3522014783654717,
     p_use_session_state     => FALSE,
     p_binds               => binds_table,
     p_binds_val           => p_binds_val,
     p_search_behaviour    => 'CONTAINS',
     p_filter_behaviour    => 'FILTER'
  );    
  dbms_output.put_line('return value: '||v_retval);
  -- the return should be a json-formatted string: [{}{}...{}]
END;

Demo Application

Found here: http://apex.oracle.com/pls/apex/f?p=54687:32
Log in with apex_demo / demo. For example, click dname and select “Sales”. Then click ename: you will only see the names for dname=sales here. Also note the hiredate column: no range selection.
My settings: search behaviour: contains, filter behaviour: take existing filters into account, value fetching delay: 750, min length before fetching: 1

Download

you can find a zip with everything you need inside, here. I might put it up on apex-plugins.com sometime.

Update: i'm working on an advanced filter for date columns, which will show 2 datepickers in the popup when a date column is selected. It's looking good so far :-)