Monday, October 26, 2009

Saving Saved Interactive Reports when Updating Application

Their is now a supported method to preserve saved interactive reports. Please read the following post which explains how to do this in APEX 4.0: http://joelkallman.blogspot.com/2010/07/where-did-my-saved-interactive-reports.html



When updating existing APEX applications that contain Interactive Reports (IR) you may, not knowningly, delete users saved IRs. The only supported way to prevent this from happening is to ensure that your Application ID is the same when you move it from Dev, to Test, to Production. David Peake wrote a full explanation of this issue here: http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html. I suggest you read his post before continuing.

What if you develop a single application that needed to be deployed to multiple clients/instances? I.e. you develop your application in DEV (100) and deploy to PROD (200), PROD (300), and PROD (400). Currently there's no supported way of doing this while maintaining your saved interactive reports.

The following script can be run after updating your production applications to ensure that your saved IRs don't get lost. Please note that this is not supported by Oracle and can put your application in an unsupported state. If you are not an advanced APEX developer I do not suggest using this as it may result in unexpected results.

Besides preserving saved IRs, users who are currently on the system will retain their current IR configurations, otherwise they will be lost. For example a user is working on an IR and applys some filters to it, you then update the application which will cause the interactive_report_id to change. The next time the user refreshes the page they won't see their filters any more (i.e. they'll have the default IR again). Running this script will prevent this from happening.

Note: this must be run as SYSTEM or a user with SYSTEM level access

BEGIN
FOR x IN (SELECT a.ID, a.name, a.session_id,
b.interactive_report_id
FROM apex_030200.wwv_flow_worksheet_rpts a, -- This could also be flows...
apex_application_page_ir b
WHERE a.flow_id = :app_id
AND a.page_id = b.page_id -- Linking is done via the page so please be aware of any IR page changes
AND b.application_id = a.flow_id
AND a.worksheet_id != b.interactive_report_id
and a.status = 'PRIVATE'
)
LOOP
UPDATE apex_030200.wwv_flow_worksheet_rpts
SET worksheet_id = x.interactive_report_id
WHERE ID = x.ID;

END LOOP;
END;

8 comments:

  1. Martin,

    I too have an app that I deploy several times after the initial development. I tried running your query (just the select statement) as the system user. But I didn't get any rows returned?

    I tried setting the bind variable "app_id" to both my developed app and the various deployed numbers and it still didn't work.

    1) Which should app_id be set to?
    2) Do you run the full script after the import of the each of the deployed aps?
    3) Any ideas overall?

    ReplyDelete
  2. Hi Justin

    Can you please run the following query for each of your applications:

    SELECT a.ID, a.NAME, a.session_id, b.interactive_report_id
    FROM apex_030200.wwv_flow_worksheet_rpts a, apex_application_page_ir b
    WHERE a.flow_id = :app_id
    AND a.page_id = b.page_id
    AND b.application_id = a.flow_id
    AND a.worksheet_id = b.interactive_report_id
    AND a.status = 'PRIVATE'

    Do you get any rows back? This could mean that all your saved IRs for the application are linked properly.

    To answer your questions:

    1- App_id should be the application that you just deployed. For example if you deploy the same application as 200, 300, and 400 (for each of your clients) you'd need to run the script for all 3 of your clients using each of the app_ids.

    2- To be honest I haven't used this in production yet. I'm trying to hold off as long as possible in hopes that APEX 4.0 resolves this issue. The plan would be to run this script each time I've installed/imported the application. I tried to include it as part of the install/upgrade scripts directly in the application and that didn't work.

    3- Let me know the results of the above query.

    Martin

    ReplyDelete
  3. Martin,

    I ran your query from the last reply on an established app, it returned 170 rows, I then reimported that app to a new id and it returned 36 rows. So you can see the difference.

    Does this help?

    ReplyDelete
  4. Hi Justin,

    So far that sounds good. Can you please do the following (substitute the app IDs accordingly):

    -App 100 (has an IR)
    -Import App 100 as App 200.
    -Run App 200 as an end user, and save a custom IR
    -Import App 100 as App 200 (again)
    -Run the page with your IR (the saved IR should be missing).
    -Run my script
    -Refresh the page, does your saved IR come back?

    Martin

    ReplyDelete
  5. Martin,
    When I run your original query with the "AND a.worksheet_id != b.interactive_report_id"

    I get no results for my app_id (200)

    When I run your revised query "AND a.worksheet_id = b.interactive_report_id"

    I get results rows returned...

    Using your scenario that you just described have you gotten this to work? I just don't understand why I can't get this to work.

    Thanks again!

    ReplyDelete
  6. Hi Justin,

    Can you please send me an email to: (my_first_name)ATclarifit.com

    Let's discuss this offline as I may need to look at a copy of your application.

    Thank you,

    Martin

    ReplyDelete
  7. Hi Martin,

    are these different versions deployed on a single APEX instance or locally at your different clients?

    If they were deployed locally at different clients, there should be a simple solution to the problem:

    Just import your development workspace at your customer and use the exact same application id (and we use the same parsing schema, too).

    No issues then, right?

    Another option is to use the new apex_application_package in APEX 4.0: http://joelkallman.blogspot.com/2010/07/where-did-my-saved-interactive-reports.html

    Cheers,
    Dietmar.

    ReplyDelete
  8. Hi Dietmar,

    Thanks for the comment. I've updated this page to direct readers to Joel's article.

    Martin

    ReplyDelete