Introduction
In Oracle APEX, form fields (page items) can be used effectively with Interactive Reports and Classic Reports to create dynamic and user-driven data views. By linking form field values to report queries, you enable users to filter, search, and customize the data displayed without needing complex coding. This makes reports more flexible and responsive to user input, enhancing the overall application experience.
In Oracle APEX, you can use form fields (page items) to filter and customize data shown in Interactive Reports and Classic Reports. This approach lets users control what data they want to see by entering values or selecting options in form fields, making reports dynamic and user-friendly.
Step 1: Create or Identify Form Fields
Create page items (such as text fields, select lists, or date pickers) that users will use to enter filter criteria. For example, a text item :P1_EMPLOYEE_NAME or a select list :P1_DEPARTMENT_ID.
Step 2: Reference Form Fields in the Report SQL Query
Modify the SQL query of your Interactive Report or Classic Report to include the page items as bind variables. For example:
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE (:P1_EMPLOYEE_NAME IS NULL OR LOWER(first_name) LIKE '%' || LOWER(:P1_EMPLOYEE_NAME) || '%')
AND (:P1_DEPARTMENT_ID IS NULL OR department_id = :P1_DEPARTMENT_ID)
This query filters employees by name and department only if those page items have values.
Step 3: Ensure Page Items Are Submitted
To make sure the report query sees the current values of page items, add a Dynamic Action on the form fields that submits those items and refreshes the report region.
-
Create a Dynamic Action on the page item’s Change event.
-
Add a True action to Submit Page Items and list the form fields.
-
Add another True action to Refresh the report region.
Step 4: Set Default Values and Null Handling
To avoid filtering out all data when a form field is empty, write SQL to handle null values appropriately (as shown above). This way, if the form field is blank, the filter is ignored.
Step 5: Test the Filtering
Run your page, enter values in the form fields, and verify that the Interactive Report or Classic Report updates accordingly with the filtered results.
Best Practices
-
Use bind variables (
:P1_ITEM_NAME) to avoid SQL injection and improve performance. -
Handle null or empty values in your SQL to prevent unintended filtering.
-
Submit only necessary page items to optimize performance.
-
Use Dynamic Actions to provide immediate feedback without full page reloads.
By integrating form fields with Interactive and Classic Reports, Oracle APEX enables you to build interactive, efficient, and user-friendly reporting pages that respond instantly to user input.
Reports can be dynamically filtered based on form field values.
Example: Filter an Interactive Report Based on a Form Field
Create a form field (P1_EMPLOYEE_NAME).
In Interactive Report Attributes, set the query as:
SELECT emp_id, emp_name, department, salary
FROM employees
WHERE emp_name LIKE '%' || :P1_EMPLOYEE_NAME || '%';
When a user enters text in P1_EMPLOYEE_NAME, the report updates to show matching employee names.
Example: Filter a Classic Report Based on a Form Field
Create a Classic Report and use this query:
SELECT order_id, customer_name, total_amount
FROM orders
WHERE customer_name = :P1_CUSTOMER_NAME;
Assign a Dynamic Action to refresh the report when P1_CUSTOMER_NAME is changed.
Conclusion
Using form fields in Interactive and Classic Reports allows you to build powerful, customizable data displays in Oracle APEX. By connecting page items to report queries, users can interactively control what data they see, improving usability and relevance. Implementing this technique is key to creating dynamic applications that adapt seamlessly to user needs.
