Skip to main content

Oracle APEX: Dynamic Interactive Grid Filtering using Pie Chart Selection

 

Overview

Oracle APEX has powerful features for building interactive dashboards and data-driven applications.

 In this article, we discuss how a Pie Chart can pass the selected supplier value to a Hidden Page Item, which is then used to dynamically filter an Interactive Grid and display only the relevant records.

 Objective

A common requirement is to filter dashboard data based on user interaction with charts.

Prerequisites

Before implementing this, ensure the following are available.

  • An Interactive Grid region.
  • A Pie Chart region displaying Top five supplier information by QA Result.
  • A Hidden Page Item to store the selected supplier.
  • A report query that supports filtering using bind variables.

What is a Hidden Page Item?

In Oracle APEX, a Hidden Page Item stores values that are not visible to end users but remain available during page processing, validations, computations, Dynamic Actions, and PL/SQL execution

 How to Create a Hidden Page Item

   In this article we create the following Hidden page Item:
       P440_SUPPLIER

 Step 1: Open Page Designer

1.     Log into your APEX workspace

2.     Navigate to the page

Step 2: Add a New Page Item

1.     Right-click on the Items region

2.     Select Create Page Item

Step 3: Configure and Save

1.     Give the item a meaningful name

2.     Set Type to Hidden

3.     Set Value Protected to No

Note: Set Value Protected to No because the item value will be populated through chart navigation.

4.     Set Default Value if needed

5.     Click Save


How to Use Hidden Items in a Region SQL Query

Once a Hidden Page Item is created, it can be referenced in region SQL queries using Oracle APEX bind variable syntax:

:P440_SUPPLIER

The bind variable can then be used within the report query to dynamically filter records based on the value selected from the Pie Chart.

Step 4: Configure the Pie Chart

We configure the Pie Chart so that the selected supplier value is passed to the Hidden Page Item and used to filter the Interactive Grid.

 Select the Top 5 Suppliers by Failed QA Pie Chart region and configure the chart link as described below

1)    Type : Select Redirect to page in this Application


2)    Click Target and configure the following values

 a. Name

Enter the Hidden Page Item: P440_SUPPLIER

b. Value

Click the LOV icon next to Value.

&SUPPLIER.

The configuration should appear as:

Name

Value

P440_SUPPLIER

     &SUPPLIER.

c. Clear Cache

Leave blank.

d. Action

Leave as None.

5. Modify Main Region Report Query

 Add the following filter to the main report query to display records only for the selected supplier:

AND (
      :P440_SUPPLIER IS NULL
      OR ab.supplier = :P440_SUPPLIER  

    )

Note:

  • All records are displayed when no supplier is selected.
  • Only records for the selected supplier are displayed when a pie chart segment is clicked.

 6. Page Items to Submit

In the Interactive Grid region:

Locate Page Items to Submit and add:

P440_SUPPLIER

7. Testing

When a user clicks the JAMAL segment in the Pie Chart:

  • The selected supplier value (JAMAL) is passed to P440_SUPPLIER.
  • The Interactive Grid is refreshed automatically.
  • The report query applies the supplier filter.
  • Only records related to JAMAL are displayed.


 How It Works

1.     The user clicks a segment in the Pie Chart.

2.     The selected supplier value is passed to P440_SUPPLIER.

3.     Oracle APEX updates the session state for the Hidden Item.

4.     The Interactive Grid submits the item value and refreshes.

5.     The report query applies the bind variable filter and displays only matching records.

Conclusion

Oracle APEX makes it easy to build interactive dashboards by linking chart selections with report filtering. By passing the selected Pie Chart segment value to a Hidden Page Item and using it within the report query, developers can create dynamic Interactive Grid reports that refresh automatically based on user selections. This approach enhances usability, improves data exploration, and delivers a more intuitive dashboard experience without requiring manual search or filter entry.

Comments

Post a Comment

Popular posts from this blog

Oracle EBS R12 General Ledger (GL) Module – Practical SQL Queries

Introduction Oracle E-Business Suite (EBS) R12 General Ledger (GL) is the core Financial module in Oracle EBS R12. It is used to manage journal entries, accounting periods, balances, and financial reporting. The GL module integrates with Payables (AP), Receivables (AR), Fixed Assets (FA), Purchasing (PO), and Inventory (INV). The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help the organization make decisions. Below are real-world SQL queries commonly used.   1) View configured Ledgers      Purpose: Fetch Ledger Configuration Details SELECT       ledger_id,      name  AS   ledger_name ,    short_name ,   currency_code FROM     gl_ledgers ORDER BY     ledger_id ;   2) Ledger Information Query Purpose: Fetch Ledger Configuration Details SELECT     gl.name   ...
Post a Comment
Read more

Exploring AI in Oracle APEX 26.1

Artificial Intelligence (AI) is transforming the way organizations and developers work. Businesses and professionals are increasingly adopting AI tools to improve productivity, automate processes, and build smarter applications. Oracle APEX introduces a significant evolution in application development by integrating governed Generative AI capabilities directly into the low-code platform. This enables developers to build intelligent, conversational applications while maintaining enterprise-grade security and control. In this article we explore AI capabilities in Oracle APEX 26.1 What is AI Agents AI Agents can be created to reason over user requests and take actions through approved AI Tools. Each tool exposes a specific application capability the agent is allowed to invoke, such as retrieving data, running server-side PL/SQL, or executing client-side JavaScript. APEX manages the execution flow by preparing context, dispatching tool calls, executing tools, handling results, and...
Post a Comment
Read more

Oracle R12 Inventory Configuration: Key points to Avoid Mistakes

Most Oracle ERP R12 inventory problems are not system failures they are process and configuration failures. The same categories of mistakes surface repeatedly across implementations: Focusing on master data control, disciplined transactions, proper OU setup, and strong internal controls prevents operational and financial discrepancies.   This article walks through the four most critical areas where Oracle R12 inventory implementations go wrong, explains why each mistake is costly, and provides recommendations to prevent them. Each section includes the relevant navigation path and configuration screenshots for hands-on reference   1.       Improper Item Master Configuration in Oracle R12   Oracle E‑Business Suite R12 , the Item Master Configuration serves as the foundation of the Inventory module. Two critical components under this configuration umbrella are: Define Master Item Setup Define Item Status Both setups control how items ...
Post a Comment
Read more