5 Replies Latest reply: Jul 2, 2009 5:49 AM by Simon Gadd RSS

    Looking for a Package or Function to copy saved interactive reports

    629628
      I am looking for an Oracle package, function or whatever to copy saved interactive reports from one user to another. If someone has the code to do so or can list the tables involved so that I can create one, I would appreciate it.

      Thanks,
        • 1. Re: Looking for a Package or Function to copy saved interactive reports
          dmcghan
          All,

          Actually, I have to second this nomination. I don't really need the package or procedure - I just need a clue as to which tables in the flows schema are holding the information. Any help on this would be great.

          Regards,
          Dan

          http://danielmcghan.us/
          http://sourceforge.net/projects/tapigen/
          • 2. Re: Looking for a Package or Function to copy saved interactive reports
            515357
            You may want to research the Apex Views in Utilities tab.

            Go to Utilities, click APEX views, and select "APEX_APPLICATION_PAGE_IR" view. There is a column called "NUMBER_OF_SAVED_REPORTS", which holds the count of saved reports. There are a whole bunch of views, and one of them may have the data that you are looking for.

            In case you find it, please post back here, so that others can benefit.

            Thanks.

            Ravi
            • 3. Re: Looking for a Package or Function to copy saved interactive reports
              629628
              Below are my findings.

              The table FLOWS_030100.wwv_flow_worksheet_rpts appears to store all the information regarding the reports.
              The ID column is populated in the trigger when inserting a new record.
              The column application_user stores the user that owns the saved report.
              When session_id and base_report_id are null, it indicates that it is a saved report.
              The column name stores the name of the saved report.

              It appears as if we could just copy the specific row (saved report) from one user to another inserting a new record in FLOWS_030100.wwv_flow_worksheet_rpts for the new user.

              Does anyone know if it is that simple? What issues could this present?

              I am currently receiving an insufficient priviledges error when trying to insert record.

              Below is my sample SQL:

              insert into FLOWS_030100.wwv_flow_worksheet_rpts
              (WORKSHEET_ID, FLOW_ID, PAGE_ID, SESSION_ID, BASE_REPORT_ID,
              APPLICATION_USER, NAME, DESCRIPTION, REPORT_SEQ, REPORT_TYPE,
              STATUS, CATEGORY_ID, AUTOSAVE, IS_DEFAULT, DISPLAY_ROWS,
              PAGINATION_MIN_ROW, REPORT_COLUMNS, SORT_COLUMN_1, SORT_DIRECTION_1,
              SORT_COLUMN_2, SORT_DIRECTION_2, SORT_COLUMN_3, SORT_DIRECTION_3,
              SORT_COLUMN_4, SORT_DIRECTION_4, SORT_COLUMN_5, SORT_DIRECTION_5,
              SORT_COLUMN_6, SORT_DIRECTION_6, BREAK_ON, BREAK_ENABLED_ON,
              CONTROL_BREAK_OPTIONS, SUM_COLUMNS_ON_BREAK, AVG_COLUMNS_ON_BREAK,
              MAX_COLUMNS_ON_BREAK, MIN_COLUMNS_ON_BREAK, MEDIAN_COLUMNS_ON_BREAK,
              MODE_COLUMNS_ON_BREAK, COUNT_COLUMNS_ON_BREAK, FLASHBACK_MINS_AGO,
              FLASHBACK_ENABLED, CHART_TYPE, CHART_3D, CHART_LABEL_COLUMN,
              CHART_LABEL_TITLE, CHART_VALUE_COLUMN, CHART_AGGREGATE,
              CHART_VALUE_TITLE, CHART_SORTING, CALENDAR_DATE_COLUMN,
              CALENDAR_DISPLAY_COLUMN, SECURITY_GROUP_ID)
              select WORKSHEET_ID, FLOW_ID, PAGE_ID, SESSION_ID, BASE_REPORT_ID,
              'USER2', NAME, DESCRIPTION, REPORT_SEQ, REPORT_TYPE,
              STATUS, CATEGORY_ID, AUTOSAVE, IS_DEFAULT, DISPLAY_ROWS,
              PAGINATION_MIN_ROW, REPORT_COLUMNS, SORT_COLUMN_1, SORT_DIRECTION_1,
              SORT_COLUMN_2, SORT_DIRECTION_2, SORT_COLUMN_3, SORT_DIRECTION_3,
              SORT_COLUMN_4, SORT_DIRECTION_4, SORT_COLUMN_5, SORT_DIRECTION_5,
              SORT_COLUMN_6, SORT_DIRECTION_6, BREAK_ON, BREAK_ENABLED_ON,
              CONTROL_BREAK_OPTIONS, SUM_COLUMNS_ON_BREAK, AVG_COLUMNS_ON_BREAK,
              MAX_COLUMNS_ON_BREAK, MIN_COLUMNS_ON_BREAK, MEDIAN_COLUMNS_ON_BREAK,
              MODE_COLUMNS_ON_BREAK, COUNT_COLUMNS_ON_BREAK, FLASHBACK_MINS_AGO,
              FLASHBACK_ENABLED, CHART_TYPE, CHART_3D, CHART_LABEL_COLUMN,
              CHART_LABEL_TITLE, CHART_VALUE_COLUMN, CHART_AGGREGATE,
              CHART_VALUE_TITLE, CHART_SORTING, CALENDAR_DATE_COLUMN,
              CALENDAR_DISPLAY_COLUMN, SECURITY_GROUP_ID
              from FLOWS_030100.wwv_flow_worksheet_rpts
              where flow_id = 100
              and page_id = 86
              and application_user = 'USER1'
              and session_id is null
              and name = 'My Saved Report'
              • 4. Re: Looking for a Package or Function to copy saved interactive reports
                428025
                Hi Brian,

                Did you manage to make any further progress on this? I have the exact same requirement. We have a system where someone has spent literally days setting up a fantastic series of tabs etc relating to a number of interactive reports in our system and I'd love to copy them to other users and even across installations & servers...

                Can anyone else shed any light on this?

                I've seen a hint that this is available in the next ApEx release but I'm not sure our users can wait!!

                Steve
                • 5. Re: Looking for a Package or Function to copy saved interactive reports
                  Simon Gadd
                  ** BOUNCE **

                  Anyone make any headway on this?

                  Regards

                  Simon Gadd