🚀 Implementing Dynamic Report in Oracle APEX (Classic Report)

-

🚩Introduction 

  • In real-world Oracle APEX applications, report structures are rarely static. Business requirements often demand reports where columns change dynamically based on user selection, metadata configuration, or underlying data. Hardcoding columns in Classic Reports quickly becomes unmanageable and limits scalability.
  • To solve this challenge, Oracle APEX allows us to implement Dynamic Reporting using Classic Reports by generating SQL queries and column definitions at runtime. This blog explains how to design such a solution and why it is valuable in enterprise applications.

📑 Why Dynamic Reports Are Needed

Traditional Classic Reports work well when the column structure is fixed. However, dynamic reports are essential when:

  • Column names come from configuration tables

  • Data is pivoted dynamically

  • Users select which attributes to display

  • Reports are metadata-driven

  • Report layouts differ per role or module

Dynamic reporting ensures flexibility, maintainability, and better adaptability to changing business needs.

👉 Steps to create Dynamic Report

Dynamic Reports can be achieved in the Oracle APEX using the Classic Report, So we will be using the classic report in this tutorial to create the dynamic report.

Step 1: 

In the page designer, create one page item of type select one / select list and under the list of values give some static values like one , two , three.



Step 2: 

Now, create one region and select the type as Classic Report. 

Step 3: 

Select the Classic Report source as Function Body Returning Sql Query and paste this below code and select the page item which is created in the above step in the page items to return.

Step 4: 

In the Classic Report properties,under the source section, you will find the option Use Generic Column Names, check the check box and enter the generic column count in numerical value (ex: 10).



Mininum Value : 1 and Maximum value : 999

Step 5: 

create the dynamic action for the page item and select the event type as change and in the true action select refresh and the selection type as region and select the classic report which was created in the above step.

Step 6:

Now save the page and run the application, you will see the select list with report. when you select any value from that select list, then the report will be automatically refreshed with number of columns you selected. 



 
You can watch the live demo in my youtube channel 👉 Thanigai Solutions

🔥 Conclusion

Thus the Dynamic Reporting in the Oracle APEX can be achieved using the classic report by using the native properties without any plugins. This dynamic reporting in Oracle APEX using Classic Reports provides a powerful mechanism to build flexible, enterprise-ready applications. By leveraging PL/SQL and metadata-driven design, developers can eliminate rigid report structures and deliver highly adaptable reporting solutions. This approach not only improves maintainability but also enhances the overall user experience.

Location: Chennai, Tamil Nadu, India

Comments

Post a Comment

Popular posts from this blog

💡 Designing Dynamic QuickPicks in Oracle APEX

-
Image
🚩Introduction  Oracle APEX provides Quick Picks as a convenient way to allow users to populate page items with predefined values using a single click. While static Quick Picks are useful, real-world applications often require these values to be generated dynamically based on business rules, user roles, or underlying data. This blog explores how to implement Dynamic Quick Picks in Oracle APEX page items , enabling developers to build more intelligent, flexible, and user-friendly forms by driving Quick Pick values directly from database queries and runtime logic.  📑 Why Dynamic Quick Picks Are Needed   Static Quick Picks in Oracle APEX are useful for simple filtering, but Dynamic Quick Picks become essential when: Filter values must be generated at runtime based on current data, user role, or context Applications require data-driven filters instead of hardcoded values The same Quick Picks must adapt across multiple pages or regions Filter options depend ...
Read more

🔍 Extending Smart Search Filter for Multiple Regions

-
Image
🚩Introduction  In Oracle APEX, the Smart Search filter is a convenient feature that allows users to quickly search data in a Classic Report or Cards region without writing custom code. However, this functionality is limited to a single region , which can be restrictive when a page contains multiple regions that should respond to the same search input. In this tutorial, you will learn how to extend the Smart Search filter to work with multiple regions on the same page . We will implement a solution that allows one Smart Search input to drive filtering across multiple Classic Report ,Cards regions etc. By the end of this tutorial, you’ll have a clear, reusable approach for coordinating search behavior across regions, improving both usability and consistency in your Oracle APEX applications without breaking the declarative nature of APEX. 📑 Why Extending the Smart Search Filter Is Needed The built-in Smart Search filter in Oracle APEX works well when a page contains a single sear...
Read more

📒Designing Dynamic Inline Help Text

-
Image
🚩Introduction  Oracle APEX provides Inline Help Text as an effective way to guide users by explaining page items, validations, and actions directly within the application interface. While static help text is useful, real-world applications often require help content to change dynamically based on user input, application state, or business logic. This blog explores how to implement Dynamic Inline Help Text in Oracle APEX page items, enabling developers to deliver more contextual, responsive, and user-friendly guidance by driving help content from database queries, conditions, and runtime behavior. 📑 Why Dynamic Inline Help Text Is Needed Static inline help in Oracle APEX is helpful for basic guidance, but Dynamic Inline Help Text becomes essential when: Help content must change based on user input, selected values, or form state Different user roles require different levels of guidance or instructions Business rules determine what information or warnings should be shown ...
Read more