Faceted Search

Priyanka Choudhary Dec 16, 2020 9:51:00 AM
Find out how we can help you build solutions using Oracle APEX with our award winning application development and consultancy services.
Book a meeting with an Oracle APEX expert

Oracle first introduced the exciting new Faceted Search feature in 19.2 and has continued to improve it in subsequent releases. I spent some time looking through and it really impressed me. Let me talk about this in little more detail.

You would have seen this feature on many websites, especially the shopping ones where it makes it easier to set search filters on left or top of the screen. With the help of this feature it makes it possible to show all values grouped together with the count of how many times that value occurs in the data set.

So Faceted Search not only provides an easy to use interface to narrow the results of a report and search data, but also a powerful new way of reporting.

How to create a Faceted Search:

  1. Go to application builder home page and click on Create Page.
  2. Select Report -> Faceted Search and click Next.
  3. Enter Page Name - Faceted Search and click Next
  4. For Navigation Preference click on the first option – ‘Do not associate this page with a navigation menu entry’ and click Next.
  5. I am using OEHR_ORDERS here for demonstration.

Create Faceted Search

By default, the feature recommends some columns for the faceted search, you can remove any facet you don’t want to be included here. I have removed Customer ID and order Status from here. Click on the Create button and run the page.

All the Facets created are on left and the report is on right side of the page. You can add the filters on Facets to narrow down the results - number of records and filters applied are then displayed on the top.

Create Faceted Search (1)

Edit Faceted attributes Faceted Search attributes affect all facets.

Edit Faceted Attributes

Show current facets – by default, this is created as an element which means the count and selected facets will be displayed on top of the report. If changed to Yes it will be displayed on top of facets on the left column and if changed to No, filters will not be shown.

Show Current Facets

Batch Facet Changes – By default this attribute is disabled. Basically, the report is refreshed as soon as you select the filter, but if this option the enabled you will see Apply button under each facet as soon as you change the filter. Once you click Apply button the report will be refreshed.

Batch Facet Changes

Types of Facets that are available

Facets are displayed in different types, select one of the facets in page designer and check type, select the drop down and you will find all types of facets you could have –

Oracle APEX Types of Facets c

  1. Search Facet

Search Facet allows you to search for a word in all columns in the report. Under Source Property you can see a list of all searchable columns, you can remove columns here if you don’t want these to be included in your search

Oracle APEX Search Facet

  1. Range Facet

Range Facet enables you to search the database column for a range of values. Right click on facet and choose Create facet. Populate from the table below

Name Order Status
Type Range
List of Values  
Type Static Values
Static Values
Display Value Return Value
<5 |5
5-10 5|10
>10 10|
List Entries  
Zero Count Entries Disable
Database Column order_status
Data type Number

Save and run the page, new range Facet is ready to use.

Oracle APEX Range Facet

Charts (New feature in 20.2)

Notice the chart icon on each facet. Click on chart icon to display a pop-up bar chart of the counts of the data in that facet. The Chart dynamically updates as you select/deselect items.

Oracle APEX Faceted Search Chart

Toggle between Bar/Pie-chart:

Oracle APEX Faceted Search Pie Chart

Oracle APEX Faceted Search Bar Chart

Oracle APEX Faceted Search Chart (1)

  1. Select Edit Page from the developer toolbar
  2. To disable these charts, select the facet in the rendering tree
  3. Under List Entries disable the Show Chart Attribute

Customize Faceted Search

  1. Drag a new Static Content region under button bar.
  2. Enter title – Overview, Text - <div id="Overview"></div>
  3. Navigate to Attributes of Faceted Search Region and select Selector for Show Charts
  4. Enter #Overview for Charts Selector.
  5. Navigate to Overview region and change template options to –
  • General – Remove Body Padding
  • Header – Hidden
  • Style – Remove UI Decoration
  1. Save changes and run the page.

When you click on any chart now you will see the dashboard which can include multiple charts.

Oracle APEX Faceted Search Mutiple Charts


Faceted search is an easy to use reporting and searching feature which uses low code wizards to build and define search criteria and narrow down the report. This feature enables developers to provide easy faceted navigation with minimal effort and provides an enhanced experience for end users. Charts were added with version 20.2 and it is a very useful feature with the added benefit to see the chart in bar or pie form.

Need some help with your APEX applications? Speak to the experts today.
Book a meeting with an Oracle APEX expert

Author: Priyanka Choudhary

Job Title: Oracle APEX Development Consultant

Bio: Priyanka is a Development Consultant at DSP-Explorer. She has Masters in Computer Applications, in her previous role she worked for leading financial organisation developing banking solutions in PL/SQL, Oracle Forms and Reports. Here at DSP-Explorer she is part of a highly skilled development team providing APEX development solutions and training to both UK and international businesses.