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.

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

Interactive Reports in Oracle APEX are a powerful and flexible way to allow users to explore, search, filter, sort, and manipulate data directly within a web page. Unlike Classic Reports, Interactive Reports provide end users with built-in tools for customizing how data is displayed and interacted with—without requiring any coding. This makes them ideal for business applications where users need control over how they view and analyze information.

How to Use Interactive Reports in Oracle APEX

1. Creating an Interactive Report

   • In the APEX App Builder, navigate to the desired page or create a new one.

   • Add a new region and choose Interactive Report.

   • Provide a region title, like “Customer Orders” or “Product Inventory”.

   • Write the SQL query that retrieves the data you want to display. Example:

     SELECT order_id,
            customer_name,
            order_date,
            status,
            total_amount
       FROM orders
      ORDER BY order_date DESC
     

2. Region and Column Configuration

   • In Page Designer, select the Interactive Report region.

   • Under Attributes, configure options such as:

     • Enable Search Bar

     • Show Rows Per Page Selector

     • Download Formats (CSV, PDF, etc.)

     • Highlighting, Aggregates, and Group By

   • For each column, you can:

     • Set alignment and formatting (dates, currency, percentages).

     • Choose whether a column is visible or hidden.

     • Enable sorting or column filtering.

     • Add links for navigation to detail pages.

3. User Features in Interactive Reports

   Interactive Reports offer many built-in features to end users:

   • Column Filters: Filter on values, ranges, or custom conditions.

   • Search Bar: Full-text search across visible columns.

   • Sort: Click on column headers to sort ascending or descending.

   • Control Breaks: Group rows based on column values.

   • Aggregations: Show sum, avg, min, max, or count for numeric columns.

   • Highlights: Color rows or cells based on conditions.

   • Charts: Create basic visualizations from the data.

   • Save Report Settings: Users can save custom views as private or public reports.

   • Download: Export data in various formats (CSV, Excel, PDF, etc.).

4. Interactive Report Settings for Developers

   Developers can control what features users can access:

   • Set default filters, sorting, or highlights.

   • Restrict public report saving for security or simplicity.

   • Define Authorization Schemes for different report views.

   • Customize report appearance using templates and CSS classes.

Examples

Example 1: Sales Dashboard

• Users can search by customer name or product

• Download report as Excel for offline analysis

• Add conditional formatting to highlight high-value sales

Example 2: Employee Directory

• Enable full-text search for employee name or department

• Add links on employee names to navigate to detail pages

• Enable group by department using Control Breaks

Example 3: Help Desk Tickets

• Use filters to show only open or high-priority tickets

• Highlight overdue tickets in red

• Allow users to save a default view as their favorite

Best Practices

• Keep the initial SQL query efficient to ensure fast loading.

• Limit the number of columns if users don’t need them all at once.

• Use meaningful column headings and enable sorting where useful.

• Set default filters or sorting to present the most relevant data first.

• Allow users to save their preferred views for convenience.

• Disable features like PDF export or public reports if not needed to reduce complexity.

• Sanitize and validate any links or inputs to ensure security.

• Test on different devices for responsiveness and usability.

Interactive Reports provide powerful features for customizing your report such as searching, filtering, sorting, highlighting, group-by, pivot, aggregations, calculations, charting, and more.

Available Option

• Remove Border

• Show Maximize Button

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

Example 2:

Oracle APEX Documentation

For complete information on Interactive Reports, refer to the official documentation:
https://docs.oracle.com/en/database/oracle/apex/
Search for “Interactive Report” to access in-depth guides and options.

Conclusion

Interactive Reports in Oracle APEX provide an efficient and user-friendly way to explore and analyze data. Their built-in flexibility and features empower users to customize the data view without developer intervention. Whether you’re building a reporting dashboard, an inventory list, or a support ticket tracker, Interactive Reports can adapt to a wide range of use cases. By applying best practices and tuning the options for your users, you can deliver rich, dynamic data experiences that enhance the value and usability of your applications.