Faceted Search

Oracle first introduced the exciting new Faceted Search feature in 19.2 and has continued to improve it in subsequent releases. I have spent some time looking through the features and it has 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.

Faceted Search

Edit Faceted attributes – Faceted Search attributes affect all facets.

Faceted Search

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.

Faceted Search

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 is enabled you will see the Apply button under each facet as soon as you change the filter. Once you click the Apply button the report will be refreshed.

Faceted Search

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 –

Facet 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

Facet Source

  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.

Faceted Search

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.

Facets Chart

Toggle between Bar/Pie-chart:

Faceted Search

Faceted Search

Facets Chart

  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.

Faceted Search


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.


Priyanka Choudhary Contributor
Oracle APEX Development Consultant , Explorer UK Ltd

Priyanka is a Development Consultant at 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 Explorer she is part of a highly skilled development team providing APEX development solutions and training to both UK and international businesses.