The problem with Interactive Reports

Migrating APEX applications between environments can be a real pain, especially when it comes to Interactive Reports.

Oracle gurus Joel, David & Martin have previously commented on this, links in references below - but I'd like to describe a situation we found that may help others.

Our problem

We have an APEX application that migrated periodically, and we didn't maintain a specific application ID during the migration process - a method that I find makes migration simpler.

At one point we had users begin to use the IR saved reports feature, so we had to now make sure these reports were preserved during migration.

When migrating an application to prod considering behaviours described in David's post, our save reports were still lost. This was ultimately due to the fact the application in development was copied (snapshots of old versions) since users started using saved reports - so our application ID and hence region IDs were different.

We considered the issues raised in Joel's post regarding the apex_application_install API and offsets for metadata IDs, but it didn't help in our case.

We got a little inspiration from Martin's post, but our situation seemed a little different.

Our solution

We imported the production application with the saved private reports to development.

Then I updated the core table to align the flow_id to match, similar to Martin's suggestion - and in hindsight I see were I crossed the wires.

update apex_040100.wwv_flow_worksheet_rpts
set flow_id   = 108 -- current version in dev
where flow_id = 200 -- from production
and page_id   = 301 -- IR page
and session_id is null
and report_alias is null;

Note: updating tables owned by the APEX user not supported by Oracle

When we migrated the application we were still losing the saved reports, so my SAGE colleague Kate Marshall found solution inspired by this OTN Forum post

Just like Martin said, we still had to match the interactive report region_id to the worksheet children.
Hopefully this screenshot paints the picture, where the IDs in green needed to match, as do the application ID.

Saved IR meta-data

We initially missed the region_id -> worksheet_id, and if I recall that left us with orphaned records after the migration.

Once updated, we then migrated the application from development, including the saved reports we copied from production.

So now when migrating subsequent updates to the application, we don't include private IR reports from development - and it doesn't overwrite/lose whatever is in production.

Wish list

I would like to see the APEX development team improve this process regarding saved reports, perhaps similar to the way questions are asked during import/export regarding supporting objects.

Our users would also like the ability to share saved reports to selected groups of people. I wonder if logistics would allow this for either authorisation schemes, APEX user groups, or some other mechanism.

If you have your own feature requests - there's an app for that

Good references

David Peake - Preserving saved interactive reports
Joel Kallman - Where did my saved interactive reports go?
Martin Giffy D'Souza - Saving saved interactive reports when updating application