HOW DO I Add Facet Searches in ORACLE APEX

Facet searches in Oracle APEX provide an intuitive and interactive way for users to filter data across multiple dimensions without writing any SQL or form input. Whether you're building a catalog, directory, product list, or data dashboard, facet searches help users drill down to the information they want with just a few clicks. Oracle APEX makes it easy to add facets to reports, maps, or cards—enhancing usability while keeping performance optimized. This blog post walks you through how to add facet searches to your APEX pages, step-by-step.Step-by-Step: How to Add Facet Searches in Oracle APEX

1. Create or Identify a Report Region

   Start with a table or SQL query that serves as the data source for your report. You can use Classic Reports, Interactive Reports, Cards, or Maps. Make sure the table or view has multiple columns that could be used as filter dimensions, like status, category, region, date_created, etc.

2. Create a Faceted Search Page (Optional)

   To add facets to a new page:

   • Go to App Builder → Create → Page.

   • Choose Faceted Search from the report options.

   • Select your table or write a SQL query as the data source.

   • Choose your report type: Classic Report, Cards, or Map.

   This automatically creates a region for facets on the left and the results on the right.

3. Add Facets to an Existing Page

   If you want to enhance an existing page:

   • Open your page in Page Designer.

   • Add a new Faceted Search region (under Regions).

   • Set the Source Type to your table or SQL query.

   • Under the Facets node, click ➕ to add a new facet.

4. Choose Facet Types

   Oracle APEX supports different types of facets:

   • Checklist – for categories, tags, or statuses

   • Range – for numeric or date columns

   • Search – for keyword filtering

   • Select List – for dropdown-style facets

   • Group By – for grouping and summarizing filters

   Example: Add a checklist facet for status, a range facet for salary, and a date range for hire_date.

5. Map Facets to Columns

   For each facet:

   • Set the Column to match the dataset (e.g., department_id, region).

   • Optionally add display text, order by, or null display values.

   • You can also define custom LOVs if the column values are not descriptive enough.

6. Test and Refine

   • Run the page and interact with the facets.

   • Verify that selecting a facet updates the report dynamically.

   • You can combine multiple facets—APEX handles AND/OR logic automatically.

   • Add Show Count to help users understand how many results match.

Best Practices for Faceted Search in APEX

• Keep facet categories meaningful and limited to 5–10 options where possible.

• Use indexing on frequently filtered columns for performance.

• Avoid redundant facets that confuse users (e.g., category and subcategory when one is sufficient).

• Use Null Display Value to handle missing data gracefully.

• Disable unused facets when no data matches using Hide if No Values.

• Use cards or maps for better visual feedback when working with images or geo-data.

 

Step 1.  Add the facet search on the left hand side, in the Search region.

Step 2.  Add the type as “Checkbox Group” and  Give the label a name for the Region.

Step 3. List of value type: “Distinct Values”

Step 4. Set the Database Column that we want to use for searching.

Results:

Adding another Facet Search

Lets add another faceted search for States where the earthquake occurred. For this we will use the “PROPERTIES_NET” field from the database table.

Step 1. Add the facet

Step 2. Change the Label to “State

Step 3. Ste the List of Values to “distinct Values”

Step 4.  Set the Source to the column “PROPERTIES_NET”

Step 5. Save and Run

Results:

Official Oracle APEX Documentation

• Faceted Search Pages in APEX

• Oracle APEX Faceted Search Concepts

• APEX Facet Types and Settings

Conclusion

Adding facet searches in Oracle APEX improves your application's interactivity and empowers users to explore data on their own terms. With just a few clicks, you can create dynamic filters that work across multiple columns, support keyword searching, and integrate seamlessly with reports, cards, or maps. Whether you’re building dashboards, catalogs, or GIS apps, APEX’s Faceted Search region offers a clean and scalable way to improve UX without heavy development. Use it early in your design process to turn raw data into smart, discoverable content.