APEX 4.2.3 is officially out. In addition to the standard assortment of bug fixes, we’ve added two new packaged applications and updated several others. Today’s post is about a change that we’ve made to several of the applications: Filter Controls (present in Sample Reporting, Sample Forms and Grid Layout, and the new Data Reporter).

Filter Controls are really powerful, giving the users a simple, easy-to-understand option for, well, filtering their data. Interactive reports give some of the same functionality, but a) the filters are kind of buried (especially bad for people who aren’t used to them, but still out of the way for power users), and b) it’s really difficult to set up an “or” filter. Oh, and filter controls can manipulate all sorts of regions on the page–not just reports.

Of course, the issue with filter controls (now, at least) is that setting them up is a fairly long process. I’ll walk through the details in this post, so hopefully others will be able to start using them as well.

I have built a sample application using the steps below; you can download the application export (and all other files) here.

First, a quick overview: Filter controls are implemented by a collection, which holds all of the filter information (grouping, active/inactive status, column associated with each filter, its data type, and so on). This collection is established by a before header process. There’s a PL/SQL region which renders the filters based on this collection, and dynamic actions and hidden items to handle interaction with the users. Optionally, an “active filters” region can be displayed (think of the active filters part of interactive reports). Finally, any impacted SQL (report region source, etc.) needs to be modified to use the active filters. That’s a lot of moving parts, so let’s take them one at a time.

Note: we’re working on improving the implementation for drop-down filter controls right now; rather than wait and present a unified design, I’m making the decision to post the design for sidebar filter controls now, and will post the drop-down implementation once I believe it is ready.

Underlying all of the parts listed above is the filter framework package, which provides several important pieces of functionality, from creating the collection to modifying SQL queries to apply the filters. There are still some rough edges/areas I want to improve, but here’s the current version.

You’ll also need to add this CSS file to your page template. (Note that this file is designed to work with theme 25; if you’re using something else, you may need to modify it). And you’ll need to place this sprite in your images directory (you may need to modify the CSS file to point to the appropriate location).

Next, we need to set up the page we want filters on. As mentioned above, filters can be applied to any APEX component which uses a SQL source, and can work with multiple regions on the page (we have an internal app which filters six charts). Because it allows us to use a function returning SQL source, rather than having to alter the SQL ourselves, the Classic Report gives the best functionality, so I recommend starting with that. Once the page is created, add the following components:

• HTML Region for hidden items – Technically not needed; you could add the hidden items to the report region, but I like to keep things organized.
• Hidden Items: PXX_FILTER, PXX_FILTER_SEQUENCE, and PXX_TEXT_SEARCH_LABEL – The first two are for the dynamic actions, and should have the “Value Protected” flag set to No (otherwise, you’re likely to run into a Session state protection error). I also like to set PXX_FILTER to Maintain Session State Per User so that filters will persist across user sessions, but that’s a design decision. The third page item is only necessary if you’re planning on including the ability for the user to enter arbitrary text searches; its value will be displayed in the list of active filters. I default to “Row Contains”.
• Before Header Process – As mentioned, this sets up the collection. It does so by declaring a PL/SQL Nested Table, adding a record for each column the user can filter on (setting flags as appropriate), and passing that to the framework package’s build_collection procedure. It also has logic which handles existing filters (set through session state or passed through the URL). Here is a stripped-down sample process.
• A PL/SQL Region to display the filters – Once the collection is populated, we need a way to display it to the user. The framework package provides two options: a drop-down menu, suitable for use in a button bar, or an always-visible list, suitable for use in a sidebar (similar to many e-commerce sites). As mentioned, this post will focus on the sidebar implementation. This is generated by the framework’s render_sidebar procedure. In theme 25, this region is placed in Position 02.
• Button and Process – I highly recommend providing the user with a button to reset the filters. This button should fire a process like this:

  :PXX_FILTER := null;
  filter_fw.reset_collection();

• (Optional) A Report Region for the active filters – For the sidebar implementation, this is only necessary if you’re allowing text searches; it’s redundant for the other filters. I used a Named Column template for this report to allow for grouping filters. Unfortunately, APEX doesn’t currently allow for importing a single report template from another application, so the simplest way to get the correct filter is to install one of the packaged applications which use filter controls (noted at the beginning of this post) and copy the Active Filters report template to your application. Here’s the source for the report:

  with tf as (
      select filter_sequence,
          nvl(filter_group,:P1_TEXT_SEARCH_LABEL) filter_group,
          filter_display
      from table( filter_fw.get_active_filters() )
      where nvl(filter_group,:P1_TEXT_SEARCH_LABEL) = :P1_TEXT_SEARCH_LABEL
  )
  select tf.filter_sequence,
      tf.filter_group,
      tf.filter_display,
      row_number() over (order by tf.filter_sequence) row_num,
      (select count(*) from tf) total_rows
  from tf
  order by tf.filter_sequence

• (Optional) Page Items for the text search – Obviously, these are only necessary if you’re including text searching. We use two page items:
  • PXX_SEARCH – A text field with a hidden label, using the Value Placeholder instead. The HTML Form Element Search Classes is set to filterSearchField, with this in the Post Element Text:

    (note the references in the JavaScript to page items; you’ll want to fix those.)

  • PXX_SEARCH_GO – A Page Item Button. Set Start New Grid, Start New Row, and New Column to No, so it appears next to the search box. Then add the CSS Class go-button, and set its Action to Defined by Dynamic Action.
• Dynamic Actions – And here’s what I consider to be the magic part, which takes the static HTML components and melds them into one smooth user experience.
  • On change of PXX_FILTER – This handles the active filters changing. Generally, it does so by refreshing the region(s) impacted via partial page refresh (this would be the main report and the active filters report, if included; if you’re controlling multiple regions, each needs its own Refresh action in this DA). If you’re applying filters to regions which don’t support Partial Page Refresh–such as PL/SQL regions–you’ll have to use a Submit Page action.
  • On click of jQuery Selector input.filterLink – This handles the user clicking on a filter. It uses two actions:
    1. Set Value: Set the value of PXX_FILTER_SEQUENCE to the JavaScript Expression

       $(this.triggeringElement).attr("data-sequence")

    2. Execute PL/SQL Code:

       declare
           l_active varchar2(1);
       begin
           if nvl(filter_fw.get_filter( p_seq_id => :PXX_FILTER_SEQUENCE ),'N') = 'N' then
               l_active := 'Y';
           else
               l_active := 'N';
           end if;
       
           filter_fw.set_filter( p_seq_id => :PXX_FILTER_SEQUENCE,
                                 p_active => l_active );
       
       	-- Set the filter page item with the active filters.
           :PXX_FILTER := filter_fw.get_active_filters_as_varchar();
       end;

       Since the user can be turning the filter on or off in this step, we need to check to see which way they’re going, and set it appropriately. Then we set the PXX_FILTER page item with the currently active filters. Note that you must set Page Items to Submit to PXX_FILTER_SEQUENCE, and set Page Items to Return to PXX_FILTER. Do not suppress the change event. Important Note: Because the Active Filters report is refreshed dynamically, the Event Scope for this DA must be set to Dynamic; otherwise, the user will not be able to turn off text filters without refreshing the page. (Of course, if you’re not using text searches, or not using partial page refresh, then you don’t need to worry about this.)

  • (Optional) On Click of Button PXX_SEARCH_GO – Handles the user entering a text search term. Only necessary if you’re including text searches. Unlike the above, this only requires one action:
    1. Execute PL/SQL Code:

       if :PXX_SEARCH is not null then
           filter_fw.add_text_search( p_text_group => :PXX_TEXT_SEARCH_LABEL,
       							   p_search_str => :PXX_SEARCH,
                                      p_multiple   => true );
       
       	-- Set the filter page item with the active filters.
           :PXX_FILTER := filter_fw.get_active_filters_as_varchar();
       end if;

       If you’re controlling anything other than a classic report, you’ll want to change the p_multiple parameter to false; doing so will make it so that a new text search term replaces the existing one. This is the main difference between implementing filter controls with a classic report and with any other APEX component. As with the other dynamic actions, set Page Items to Submit to PXX_SEARCH (I include PXX_TEXT_SEARCH_LABEL, though it’s not strictly necessary), and set Page Items to Return to PXX_FILTER. Do not suppress the change event.

Once all of that is done, you need to modify your SQL source in the region(s) being controlled by the filters. For Classic Reports: Change the Source Type to SQL Query (PL/SQL function body returning SQL query), and wrap your query in a call to fitler_fw.apply_active_filters:

return filter_fw.apply_active_filters( p_query => q'[ select * from... ]' );

Since classic reports are the only components which currently support this use of a function, others need to have their SQL modified directly; here’s an example:

with af as ( -- The active filters, other than text searches.
		select filter_group gp,
			decode(match_col,'D',filter_display,filter_value) m,
			numeric_value nv,
			min_num_value min_nv,
			max_num_value max_nv,
			min_boundary min_b,
			max_boundary max_b,
			date_value dv,
			date_min_value min_dv,
			date_max_value max_dv
		from table( filter_fw.get_active_filters() )
		where filter_group <> :PXX_TEXT_SEARCH_LABEL
	),
    tf as ( -- The active text search filter.
            select filter_value match, upper(filter_value) match_nocase
            from table( filter_fw.get_active_filters() )
            where filter_group = :PXX_TEXT_SEARCH_LABEL )
	)
select *
from ...
where
	-- Sample filter clause; need one for each filtered column.
	(   report_type_id in ( select af.nv from af where af.gp = 'Type' )
		or not exists ( select null from af where af.gp = 'Type' )
	)
	-- Sample Text Search; note that this only supports a single text filter.
    and ( upper(e.name) like '%'||( select match_nocase from tf )||'%'
		or upper(e.description) like '%'||( select match_nocase from tf )||'%'
	)

At this point, your page should be all set. As easy as that was (tongue firmly in cheek), we’re working on making it easier. Stay tuned!