Sharing Interactive Report Layouts in Oracle APEX: Solving the Saved Report Problem

Interactive Reports are one of the main reasons Oracle APEX still beats far more expensive tooling. Out of the box, users get column selection, filtering, sorting, control breaks, highlighting, and aggregation, and they can save the whole arrangement as a report to return to later. For most applications, that is more than enough.

But on a recent client project we hit the edge of what saved reports can do, and the solution turned out to involve a genuinely interesting piece of APEX internals. This blog covers the problem, the model we landed on, and the handful of undocumented behaviours in APEX_IR that we had to work around to get there.

The Challenge: A Report That Means Something Different to Everybody

The application in question is a field data capture platform. Field teams complete visits against a project, answer a project-specific questionnaire, and record project-specific attributes about the asset they are looking at. All that data is reviewed through a single ‘Visit List’ Report.

The key design decision, made long before we arrived at this problem, was a pivot. Because every project asks different questions (which need to be viewed as columns to compare answers store-by-store), the report source exposes a fixed set of generic slots - ATTR_1 through ATTR_20 - and a configuration screen decides what goes into each slot for a given project. We called that configuration a Report View. Pick a View, and the twenty columns of the Interactive Report take on meaningful headings driven by page items.

That works well. The problem is what happens when a user arranges those columns and saves the result.

An APEX saved report does not store your columns by meaning. It stores them by name, in a colon-delimited string, against the page. Here is a real one from the application:

ATTR_1 and ATTR_2 mean whatever the currently selected View happens to put in those slots. Load that same saved report while a different View is active and you get six unrelated answers sitting under six unrelated headings. Worse, save it as a public report, and it appears for every user on every project, because saved reports belong to the page, not the project.

So, we had two settings that look like one thing to the user:

• The Report View decides which attributes (columns) are available.
  
  

• The column arrangement is set through the Interactive Report's own Actions menu and saved as a report. That report has no notion of the project or the View it was built against, which is the whole problem, and what the rest of this blog is about.

And critically, the second is only meaningful when paired with the first. Fixing the visibility problem alone (locking down public reports) would have stopped the leakage but left users with no way at all to share an arrangement with a colleague. That sharing was exactly what the client wanted.

The Model: Layouts as Children of Views

The fix was to stop treating the arrangement as an APEX concept and start treating it as an application concept.

• A Report View stays as it is. It owns what the slots mean and what the headings say.
  
  

• A Layout is new. It is a child of a View: a saved arrangement of that View's columns - which are shown, in what order, with what sorting and control breaks. A View can have many.

One View with ten available columns can now carry three Layouts - "Compliance Check", "Client Handover", "Photo Audit" - all sharing the same headings, each one shared with everybody on the project and invisible to everybody who is not.

Because a Layout can only ever be reached through the View it belongs to, the slot mismatch problem disappears by construction. There isn’t a way to apply an arrangement built against one View while a different View is active.

The schema is small:

That function-based unique index is a neat trick worth stealing on its own: it enforces at most one default Layout per View, while leaving all the non-defaults completely unconstrained.

The Mechanism: Canonical Reports and a Sentinel Owner

We still wanted APEX to do the actual work of storing and rendering column arrangements. Reimplementing control breaks and highlighting ourselves would have been madness. So, a Layout does not store the arrangement itself - it stores a pointer to one real APEX saved report, which we call the canonical.

That canonical is owned by a sentinel APEX user, LAYOUT_OWNER, who never logs in. Because saved reports only appear in the Reports list of the user who owns them, the canonicals are invisible to everybody.

Each real user then has exactly one working saved report on that page, always with the same name. Choosing a Layout copies the canonical over that working report; saving a Layout copies the working report back onto the canonical.

On the page, apply_layout runs in a Before Header process off a cascading select list, and a "Save Layout" button sits to the right of the Interactive Report search bar. The IR's own "Save Public Report" is disabled by an authorisation scheme that only administrators pass - that is what stops the cross-project leakage at source, and it is what makes Layouts the one supported route for sharing.

Conceptually that is the whole design, and on paper it is a couple of hundred lines of PL/SQL. In practice, APEX_IR had opinions.

Learnings: What APEX_IR Actually Does

Everything below was confirmed live against the running application. Several of these contradict the reasonable reading of the API.

CLONE_REPORT with p_replace_report => TRUE does not replace the content. This one cost us the most time, because it fails silently. Point it at an existing target report, and it keeps the target's old columns rather than taking the source's. An early test appeared to prove it worked, but the source and target happened to hold the same arrangement, so nothing ever exercised a real content change. The fix is to delete the target and clone fresh every time, and never trust REPLACE.

Delete and clone in the same transaction deadlocks. Having switched to delete-then-clone, we started getting ORA-00060 under normal single-user use. APEX has its own internal bookkeeping on the Interactive Report tables, and it deadlocks against an uncommitted delete of those same rows. A COMMIT between the delete and the clone fixes it. The same class of problem bites after CHANGE_REPORT_OWNER too, where the symptom is not an error at all - the clone silently produces a generic full column list instead of the source's real columns.

You cannot clone a private report you do not own. APEXIR_CANNOT_CLONE_PRIVATE_REPORT. Our canonical is deliberately private and owned by the sentinel, so applying a Layout must borrow ownership, clone, then hand it straight back:

Note the exception handler. Ownership is reverted even when the clone fails; otherwise one bad run leaves a canonical stranded in a real user's Reports list.

Not every row in APEX_APPLICATION_PAGE_IR_RPT is a saved report. APEX also creates transient rows with REPORT_TYPE = 'SESSION' for in-progress browsing state, with the same owner and the same name as the real thing. CLONE_REPORT refuses to treat a SESSION row as owned by the caller even though APPLICATION_USER matches. Every lookup needs REPORT_TYPE = 'PRIVATE'.

There is no supported way to switch which report a session is viewing from PL/SQL. The documented route is the IR[region]_alias URL request, but APEX only assigns aliases to primary and alternative default reports, never to a normal named private report. REPORT_ALIAS is blank on every one of ours. The workaround is to write the internal per-user preference directly:

Undocumented, and I would not build on it lightly. But it is the same preference APEX_IR.GET_LAST_VIEWED_REPORT_ID reads on render, so writing it means the region shows the right report next time it renders - no redirect, no branch, no region refresh.

Never hardcode region or Interactive Report IDs. These are internal surrogate IDs, and they regenerate the moment somebody creates an APEX working copy or a UAT clone of the application. Ours broke with IR_REGION_NOT_EXIST the first time a colleague opened a working copy. Resolve them at runtime off the region's static ID, which survives any clone:

Deleting is your job. Foreign key cascades will tidy your own tables, but APEX's saved reports know nothing about them. delete_layout has to call APEX_IR.DELETE_REPORT on the canonical and on every user clone, or orphaned reports quietly pile up in users' Reports lists.

One Last Detail: Naming the Canonical

Do not use the user's own name as the APEX report name. "Compliance Check" is a perfectly sensible Layout name to reuse across every project, and our unique constraint only requires it to be unique per View. But all the canonicals share one page and one owner underneath, so passing the typed name straight to CLONE_REPORT collides across projects. We generate a random internal name for APEX's bookkeeping and keep what the user typed in our own table, where it belongs.

Conclusion

The lesson here is not that Interactive Reports are the wrong tool. They did all the heavy lifting. The entire column, sort and break engine came for free, and that is exactly why the Interactive Report remains probably the best component in APEX.

The lesson is about the boundary. Saved reports are scoped to a page and owned by a user, and once your application scopes data by something else - a project, a tenant, a configurable view - that model stops fitting, quietly, in ways that only show up when a second user opens a second project. Recognising that early and wrapping the APEX component in a thin application-owned layer rather than fighting it, turned a confusing bug into a feature the client now uses every day.

Generally, I would advise against building on undocumented behaviour like this. But it was a deal-breaker for the client, so we had to work around it.

If you are wrestling with a similar problem in Oracle APEX, or want to talk about getting more out of Interactive Reports, contact us today and one of our expert developers will be in touch.