How Do I Use Data Tables and Reports – Faceted Report in Oracle APEX

Faceted Reports in Oracle APEX offer users an intuitive and powerful way to explore and filter large data sets using dynamic, sidebar-based controls known as “facets.” These facets act like filters that help users narrow down the data based on criteria such as categories, dates, or ranges—without writing any SQL or using complex interfaces. Faceted Reports are ideal for dashboards, product catalogs, customer records, and any interface where quick, user-friendly data exploration is needed.

How to Use Faceted Reports in Oracle APEX

1. Create a Faceted Report Page

   • Open your APEX application in App Builder.

   • Click Create Page > Faceted Search.

   • Choose Faceted Search with Report.

   • Select your data source, such as a table or SQL query.

   • Define your base query or table, for example:

     SELECT product_id,
            product_name,
            category,
            price,
            in_stock,
            created_date
       FROM products
     

   • Click Next, name the page, and finish.

2. Configure the Facets

   After the page is created, go to Page Designer.

   • In the Facets section, you’ll see default facets based on your columns.

   • You can configure each facet:

     • Type: List of Values, Checkbox, Range Slider, Date Picker, etc.

     • Source: Table Column or custom SQL.

     • Display Type: Control how the user sees the filter (e.g., checkboxes, links).

     • Sort Order, Count Display, Show Nulls: Customize how values appear.

   Example Facets:

   • Category → List of categories using checkboxes.

   • Price → Range Slider for selecting a price range.

   • In Stock → Yes/No toggle.

   • Created Date → Date range with a date picker.

3. Configure the Report Region

   • Below the facets, the standard report displays matching results.

   • You can choose the report type: Classic Report, Interactive Report, or Interactive Grid.

   • Format your report columns, define column links, or add dynamic actions.

4. Enhancing with Dynamic Actions and Custom Logic

   • You can add Dynamic Actions that respond to changes in facet values.

   • Use PL/SQL conditions or JavaScript for advanced interactivity.

   • Create default facet selections for specific use cases.

Example Use Cases

Example 1: Product Catalog

• Facets: Category, Price Range, Brand, In Stock

• Report: Grid layout showing product image, name, and price

• Users can quickly find products with specific attributes

Example 2: Employee Directory

• Facets: Department, Job Title, Hire Date Range, Location

• Report: List view with profile photos and contact info

• Managers filter by team and role to view organizational layout

Example 3: Support Ticket System

• Facets: Status, Priority, Created Date, Assigned To

• Report: Interactive Report with ticket details and links

• Users can drill down into open or overdue tickets quickly

Best Practices

• Use facet types that match your data:

  • Use Range Slider for numeric data.

  • Use Date Picker for date filtering.

  • Use Checkbox or List of Values for categorical data.

• Keep the number of facets manageable to avoid overwhelming users.

• Disable facets for columns with too many distinct values unless necessary.

• Use a combination of visible and collapsible facet groups for organization.

• Predefine default facet selections for common use cases.

• Ensure data is indexed properly for performance.

• Use "Count Display" sparingly for large datasets to avoid slow queries.

Faceted search allows you to filter different columns on the left side of the page and report on the right side of the page, to get only the data that you need.

Common Settings

• Remote Body Padding

• Show Maximize Button

• Show region Icon

Body Height	Header	Accent	Style
240px	Visible- default	Default	Default
320px	Hidden	Accent 1	Remove Border
480px	Hidden but Accessible	Accent 2	Stack Region
640px		Accent 3	Remove UI decoration
		Accent 4	Text Content
		Accent 5
		Accent 6
		Accent 7
		Accent 8
		Accent 9
		Accent10
		Accent 11
		Accent 12
		Accent 13
		Accent 14
		Accent 15

Styles

Heading Level	Item Spacing	Item Size	Item Width	Label Alignment	Label Position
H1	Default	Default	Default	Right	Inline - Default
H2	Standard	Large	Stretch Form Fields	Left	Show form Labels Above
H3	Slim	X-Large
H4	None
H5
H6

Sort Order Alignment	Top Margin	Bottom Margin	Left Margin	Right Margin
Center	Default	Default	Default	Default
End	None	None	None	None
	Small	Small	Small	Small
	Medium	Medium	Medium	Medium
	Large	Large	Large	Large

Details

Oracle APEX Documentation

To explore more about Faceted Search in Oracle APEX, visit the official documentation:
https://docs.oracle.com/en/database/oracle/apex/
Search for "Faceted Search" for a detailed guide and options.

Conclusion

Faceted Reports in Oracle APEX offer a streamlined, user-friendly approach to exploring data with powerful filtering capabilities. They enable end users to discover patterns, identify subsets, and interact with complex datasets in a visually intuitive way. By using a combination of well-designed facets and responsive reports, you can enhance the overall user experience of your application. Whether for e-commerce, internal dashboards, or enterprise data portals, Faceted Reports can greatly improve usability and engagement.