APEX

I wanted to share a solution for an issue I recently encountered while working with Oracle APEX Interactive Reports (IR). Specifically, I faced challenges due to the limitations in APEX's ability to handle pivot functions and dynamically generated columns within IRs. APEX restricts the use of pivot functions to a maximum of three, and the IR component struggles to accommodate a dynamic number of columns when the source query varies based on user input from page items.

The Problem:

Oracle APEX imposes a character limit(4000) on the SQL queries returned by PL/SQL procedures, packages, or functions. This limitation made it difficult to dynamically generate and execute complex queries that return a varying number of columns depending on the dataset. Another approach I tried was including the pivot column directly in the source query and using the IR Action to pivot the data. However, this approach is constrained by APEX's limit of three calculation functions, which further restricted its effectiveness.

Solution:

To work around these limitations, I found that the an approach involves including all potential columns that could appear in the IR within the source query:

1. Include All Possible Columns in the Source Query:

• Even if some columns might not always contain data, it's essential to ensure they are part of your query. This enables APEX to recognize all possible columns. I achieved this by hard coding all potential pivot values directly into the PIVOT function in the SQL. By doing so, I ensured that every possible column would be present in the source query, even if they occasionally return no data.

2. Use APEX Metadata Views:

• APEX provides metadata views that store COLUMN_ID for HTML elements and COLUMN_ALIAS for report columns. You can query these views to gather all necessary column information.
• Export those columns and create a JS object or KVP to use with your below js functions using Column_Id and Column_Alias
  • ColumnAlias is only needed if there are control break or grouping in your report. It's used to remove header that does not have id attribute in html and is using span element to display column header text.

select "WORKSPACE","APPLICATION_NAME","COLUMN_ID","COLUMN_ALIAS"
from "APEX_APPLICATION_PAGE_IR_COL"
where "APPLICATION_ID" = :Your_APP_ID
and "PAGE_ID" = :YOUR_PAGE_ID

3. Dynamic Column Management with JavaScript:

• Use jQuery to interact with the HTML elements corresponding to the columns. Specifically:
• Identify column headers using jQuery selectors: $('th[id="C' + ColumnID + '"]').
• Create a function to check whether any data exists in each column across all rows. If a column contains no data, remove it from the report using $('th[id="C' + ColumnID + '"]').remove(); and $('td[headers*="' + 'C' + id + '"]').remove();.

4. Handle Control Breaks or Grouping:

• If your report includes control breaks or grouping, additional headers might need removal. These headers usually don't have id attributes, so you’ll need to filter and remove them based on their content.

$('span').filter(function() {
    return $(this).text().toUpperCase() === colSpanName.toUpperCase();
}).each(function() {
    const thElement = $(this).closest('th');
    if (thElement.length > 0) {
        thElement.remove(); 
    }
});

Constraints:

• Be aware that this approach has a limitation where pagination cannot be used. However, in my experience, this hasn't caused any performance issues for the filter functionality as it's done on the client side.