Thursday, January 22, 2009

Preserving User Saved Interactive Reports

There have been several posts on the APEX Forum relating to issues with saving User's Interactive Reports (IRs).

A few examples are:
http://forums.oracle.com/forums/thread.jspa?threadID=828056
http://forums.oracle.com/forums/thread.jspa?threadID=706257
http://forums.oracle.com/forums/thread.jspa?threadID=845768
http://forums.oracle.com/forums/thread.jspa?threadID=719362

Background on how IRs are saved:
There are two distinct means of saving IRs - As "Default" reports performed by developers when running the application from the Application Builder and users saving reports at runtime.

When a developer saves a default report then that report definition is used to render the report in the runtime environment. When a user saves an IR it is saved into the APEX meta-data tables using the unique id for the report against that user. The unique id is based on the specific region id of the IR. This is critical when discussing issues being faced and their solutions below.

If the application is a PUBLIC application then Users will not be given the option to save IRs as there is no user details to associate that report with so every user would then see everyone's saved reports.


Basically there are three main issues being raised:
  1. Preserving user saved IRs when rebuilding environments
  2. Preserving user saved IRs when updating application
  3. Sharing user saved IRs between different users
1. Preserving user saved IRs when rebuilding environments
This issue relates to the ability to backup user saved IRs so that should you need to migrate your applications from one server to another or restore from backups then users will not lose their saved reports.

In APEX 3.2 we have resolved this issue by allowing you to save these reports as part of the Application Export. We have included a new select list option on the Export screen within the Application Builder - Export Saved Reports
Specify whether to include Saved Interactive Reports in the application export. This will enable you to export the customized settings of users for all Interactive Reports in the application. Options include:

  • Yes includes Saved Reports in the application export.
  • No does not include Saved Reports in the application export.
We have also updated the command-line export utility to include a new switch - expSavedReports: Export all user saved interactive reports. Updating the command-line utility was imperative given that we strongly recommend that "production" environments be configured as run-time only. Therefore, once you have installed APEX 3.2 (Once available) then you should modify your command-line batch program to also include exporting the IRs.

You may well ask why we implemented it as part of the export rather than as a stand-alone utility. The answer is related to the way the IRs are saved and the fact they are tied to the Region_Id for the IR. When an application is imported it retains the same Region_Ids providing the Application ID is the same as when it was exported. If the imported application is given a new Application ID then all the Region_Ids are changed and the saved IRs will not link to the region.

2. Preserving user saved IRs when updating application
This is direclty related to how IRs are saved against a specific Region_Id and how the Region_Id is determined when importing an application. If the Application ID on export and import are the same then the Region_ID will be preserved and any saved IRs will be available. To ensure this select "Install As Application:" option of 'Reuse Application ID xxx From Export File', not 'Auto Assign New Application ID' or 'Change Application ID'.

If the Application ID is changed during the import then new Region_Ids will be defined and none of the previous saved IRs will be shown for the users. To avoid this it is essential that the application id is not changed between environments. The best practice to avoid these sorts of issues is to develop your applications in DEV - Export the application and put into source control. Then only ever build from source control into every other environment and not from exports taken from staging / test / UAT etc. When installing into environments where you want the saved IRs preserved never assign a new Application ID during Import.

3. Sharing user saved IRs between different users
This is a very common request and one which there is no supported solution for.
We plan to investigate this issue as part of our APEX 4.0 development.

The best solution currently is to have the developer create a "default" report layout.

Hope this helps answer most questions on saving IRs

Regards,
David

20 comments:

Martin D'Souza said...

Hi David,

Thanks for clarifying this. In our current environment we develop in one application and deploy to multiple clients/schemas. For example:

Dev:
App id = 100

Prod (these are all the same application just deployed against different schemas):

Client 1 = 100
Client 2 = 200
Client 3 = 300

From the sounds of it we'd need to maintain 3 copies of this application on Dev. Is their any way around this?

Thank you,

Martin

Unknown said...

Martin,

Yes you are correct to be fully supported.

You would need to maintain 3 copies of the Application on Dev - App Id = 100, 200, and 300. You would then need to make modifications in Dev 3 times.

This sounds onerous but is far better than using unsupported techniques that could cause serious issues in production.
If you were to incorrectly re-link user's saved IRs for App Ids 200 & 300 the results would be very unpredictable with high potential for users receiving errors when they run IRs. Such techniques are not supported so you would be on your own (and not able to call support) if you didn't get the expected results.

Regards,
David

Martin D'Souza said...

Hi David,

Thanks for your response. Are their any plans in the future to support this?

I don't think we can easily support modifying the application n times (currently we deploy to 5~6 clients/schemas).

Thank you,

Martin

Unknown said...

No plans at this stage.

A possible solution may be a by-product of investigating the sharing of user saved IRs amongst users.

Regards,
David

Yet Another Mother Runner said...

David,
Does the column, flows_XXXXXX.wwv_flow_worksheet_rpts.Status have any working functionality as of now?

Also, one other requirement we had was to give users multiple Default Reports. Is that something that can be made possible too?
Thanks!!

Martin D'Souza said...

Hi David,

I just did the following test:

For my "prod" application I copied values from: wwv_flow_worksheet_rpts to a table (create table ir_temp as select...)

I imported my application (it deleted records in wwv_flow_worksheets but not in wwv_flow_worksheet_rpts).

Inserted values from ir_temp to wwv_flow_worksheet_rpts (using the new worksheet ids). I was able to derive the new worksheet_id from wwv_flow_worksheets using the flow_id and page_id.

I can see why you may not want to add this logic by default to APEX (in case we are overwriting an application with an entirely different application), but could we not have an option to specific this?

Also, I noticed that records weren't removed in: wwv_flow_worksheet_rpts that reference old worksheets. Does that get cleaned up from a back end process?

Thank you,

Martin

Carsten Cerny said...

Hello David,

thanks for this post. It is verry helpfully.

JB said...

Hi David,

I am quite fresh in Apex (but I do have an Oracle background since '86).
But as an Apex Novice maybe I ask for the obvious, in your Blog I read:

>> 3. Sharing user saved IRs between different users
>> This is a very common request and one which there is no supported solution for.
>> We plan to investigate this issue as part of our APEX 4.0 development.


I understood that the IR "Advanced Attributes" would give you these possibilities.
Disabling the Tab usage and using a LOV based on APEX_APPLICATION_PAGE_IR_RPT could give you this functionality (with or outside users scope).
Is this approach not supported?

I really like to hear this from you.

Best regards,

Joep

Stew said...

Joep,

You're right about what you read, but the IR report settings are still filtered by user when they're run. I saw a post on the Apex forum where someone was temporarily changing all users to APEX_PUBLIC_USER (or something like that) in order to use this functionality. But it sounds like a total kludge and totally unsupported.

Your mileage may vary but I've recommended against this approach on my current project.

But I would very much like to see some administrative tools developed by the Apex team to support shared reports and the restoration of existing reports to another application ID. Frankly I don't see how 3.1 was released with this lack of production support.

I respect David (and the team) very much for airing this dirty laundry, but now I think that it needs to be cleaned up.

Simon Hunt said...

Hi David,

I am still having problems with this, could you look at this thread and tell me what you think. Thanks Simon

http://forums.oracle.com/forums/thread.jspa?threadID=880990&tstart=105

Unknown said...

David,

Very infomative. I am curious if you know anything about user saved reports (using a common user account) are not able to be downloaded? When a user creates a new report and saves it to download, the only interactive report that gets downloaded is the "working report". We are on 3.1.2.

Thanks, Brad.

Simon Hunt said...

Hi David,

Can you have a look at my post again:

http://forums.oracle.com/forums/thread.jspa?threadID=880990&tstart=105

I think I have got to the bottom of the issue and could really use your advice.

Thanks

Martin D'Souza said...

Hi David,

I posted a possible solution here: http://apex-smb.blogspot.com/2009/10/saving-saved-interactive-reports-when.html

I know it is unsupported but I have to do something to support my environments. Given what I wrote, do you see any potential problems?

Thank you,

Martin

Admin said...

I think it would be very helpful if there is an option like import saved reports from (some existing application) while importing an application, in this way we can select some backup app for saved reports while moving between systems

Unknown said...

Hi David (and hi Martin, great job the one you posted!)
We share the same needs Martin has: one DEV application but multiple customers in hosting. Are there any news around? New workarounds? We tried using one workspace per client but unfortunately app ID are apex wide and so cannot be reused among different workspaces...
any hint for us?
thank you
Virgilio

Peter Clamp said...

Hi David,

I discovered today that the IR attribute “Maximum Row Count” applies to IRs in chart formats as well as in report format. I created a bar chart and noticed that the counts that I was seeing were less than I expected. When I increased “Maximum Row Count” to a number greater than the rows in the underlying table, the counts increased to the expected values. It seems to me intuitively that the concept of rows, and limiting the number of rows read, should apply only the report format. Is there a reason why it applies to chart formats also?

If it is necessary to apply the “Maximum Row Count” to IR charts, then the warning “This query returns more than rows, please filter your data to ensure complete results” should be displayed for IRs in chart formats as well as in report form. Currently it is not displayed. In fact, the warning is even more important in the context of charts because charts appear to show arbitrary data depending on the order in which rows are read up to the maximum number. Whereas the displayed rows in report format are individually correct, the charts are simply wrong.

Thank you,

Peter

trupti salvi said...

Hi David,
I imported my application through database on production environment as APEX development instance is not available on production. Upon importing I obeserved that "search bar" in all the Interactive reports is not functioning.
I cant select more number of rows, if i press go button writing some criteria in serach bar-nothing happens, cant scroll down wheel for options like select columns, filters, highlighter etc. The environment from where this script was exported, everything works fine in it. I dont know where to look out to resolve this issue as I dont have APEX development instance at prod.
Please suggest.

Thanks,
Trupti

Unknown said...

Trupti,

Please log a Service Request with Oracle Support. They will be able to help you diagnose the issue.

Regards,
David

Stew said...

Trupti,

Not to point you in the wrong direction, but this sounds like the old issue of importing an application where the IR button template selection got dropped. This was in Apex 3.2.1. Go to the Report Attributes page and check the Search Bar settings for the Button Template. Make sure a template is selected.

I hope this helps.

Unknown said...

3. Sharing user saved IRs between different users
This is a very common request and one which there is no supported solution for.
We plan to investigate this issue as part of our APEX 4.0 development.

The best solution currently is to have the developer create a "default" report layout.

Just a quick comment incase anyone is still reading this. The changes to saving IR report in Apex 4 are good, but you have unleashed the power of SaaS Rapid Development like no one else. A useful feature in a future release would be the ability for a user to be able to give access to their Private reports to other users of their choosing rather than have to create public reports that all can see.
The issue being the list could get quite large over time, and usually users only want half a dozen reports in total.