Interactive Reports: taking back control over public reports

Some end users have been complaining to me about losing their public and private reports after installing a new version of an application. Somewhere, somehow, the connection between the application and the saved reports has been lost. This has now happened several times and something has to change. I’ve chosen to revoke the create public report privilege of all end users and convert all public reports to alternative reports.

When exporting the application from acceptance environment the following settings are met.

Public and private reports aren’t exported because all the saved reports, except primary and alternative, only exist in the production environment. The Export with Original IDs option is set to yes, this will ensure that all the component IDs are the same as of the last import. When upgrading an application through the DTAP-street the component IDs should be the same. These IDs are what APEX uses to load the correct items on a page. When an ID changes a mismatch will happen, and the component isn’t loaded.

Somewhere along the line some of the IDs have been changed and as a result the end users can no longer find the saved public reports.

First, we have to find the missing public reports. When connecting to the APEX-schema, you can find all the views which are used by APEX. The view apex_application_page_ir_rpt contains all the saved Interactive Reports, including the reports of the builder. The following query retrieves all reports excluding the APEX builder reports and the primary reports.

select
  workspace
, application_id
, page_id
, interactive_report_id
, report_id
, application_user
, report_name
, status
, report_type
from 
  <apex_user>.apex_application_page_ir_rpt
where 
  report_type != 'PRIMARY_DEFAULT'
and workspace != 'INTERNAL';

The application_user shows the user who saved the report and interactive_report_id contains the foreign-key id to the report region id. This can be found using the view apex_application_page_ir. The report_type and status columns are important, both should contain “PUBLIC” to be of interest here.

When all the (missing) public reports have been found, we could take back control over them by changing the report type and application user. We no longer allow the creation of public reports, so all reports should be default (alternative or primary) reports. This gives the control back to developers and should reduce the proliferation of reports in the application.

The following statement updates one or all public reports. By setting the application_user to “APXWS_ALTERNATIVE” all developers become owner of the reports. It’s also possible to update the interactive_report_id column with the correct IDs, but this isn’t the solution I’m after.

-- individual update
update  <apex_user>.wwv_flow_worksheet_rpts 
set     application_user = 'APXWS_ALTERNATIVE'
,       is_default = 'Y' 
where   id = < report_id >;

-- update all public reports
update  <apex_user>.wwv_flow_worksheet_rpts 
set     application_user = 'APXWS_ALTERNATIVE'
,       is_default = 'Y' 
where application_user not in ('APXWS_ALTERNATIVE','APXWS_DEFAULT')
and  r.status != 'PRIVATE';

Lastly, if you want to change filters or the selected columns of a saved report it is also possible to do this via the database. The table wwv_flow_worksheet_rpts contains multiple columns for filtering, sorting and computing the report. Updating the columns will result in a different saved report.

Conclusion

By updating the application user to “APXWS_ALTERNATIVE” in the table wwv_flow_worksheet_rpts it’s possible to change the owner of a saved report. This helped me as a developer to take back control over the saved reports.