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.
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.
Edit Faceted attributes – Faceted Search attributes affect all facets.
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.
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.
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 –
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
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 |
|
||||||||
| List Entries | |||||||||
| Zero Count Entries | Disable | ||||||||
| Source | |||||||||
| Database Column | order_status | ||||||||
| Data type | Number | ||||||||
Save and run the page, new range Facet is ready to use.
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.
Toggle between Bar/Pie-chart:
When you click on any chart now you will see the dashboard which can include multiple 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.
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.