Kopiowanie zapisanego raportu interaktywnego do innego użytkownika How to copy saved interactive report to another user.

(English text below polish)

Witam

Po długiej przerwie wracam z nowym tematem.

Czy kiedyś zastanawialiście się nad kopiowaniem zapisanego raportu interaktywnego do innego użytkownika aplikacji ? Wyobraźmy sobie sytuację w której pracowaliśmy kilkadziesiąt minut nad tym aby nasz raport interaktywny podawał wszystkie dane potrzebne do aktualnej analizy. Po czym raport ten chce zobaczyć nasz kolega/szef. Nie damy mu przecież własnego loginu i hasła jeszcze namiesza w naszej konfiguracji. Można by przekopiować raport interaktywny. Ale ta funkcjonalność będzie możliwa dopiero w wersji 4.0 (wg zapowiedzi Oracle).

Jak wiec przekazać zapisany raport interaktywny do innego użytkownika ?

Po pierwsze zaznaczam, że aby to wykonać trzeba pracować na tabelach użytkownika FLOWS (APEX w wersji 3.2) dlatego operacje te należy wykonywać ostrożnie.

Zaczynamy

1. Wszystkie zapisane raporty są przechowywane w tabeli wwv_flow_worksheet_rpts użytkownika FLOWS (APEX w wersji 3.2) . Aby móc pracować na tej tabeli należy dać dostęp właścicielowi aplikacji na którym będzie przeprowadzana operacja. Czyli:

GRANT INSERT,REFERENCES,SELECT,UPDATE ON flows_030100.wwv_flow_worksheet_rpts TO your_user;

2. Kolejnym krokiem jest stworzenie procedury która będzie kopiowała raport interaktywny.

CREATE PROCEDURE IR_TRANSFER (
v_ir_id IN NUMBER,
v_user_login IN VARCHAR2,
v_type IN NUMBER DEFAULT 0
)AS
BEGIN
-- procedura kopjuje lub zamienia wasiciela zapisanego raportu interaktywnego. if v_type = 1 then
/* zamień wasiciela */
update flows_030100.wwv_flow_worksheet_rpts
set application_user = upper(v_user_login)
where id = v_ir_id;
elsif v_type = 2 then
/* kopjuj raport */
insert into flows_030100.wwv_flow_worksheet_rpts
(id, 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, created_on,
created_by, updated_on, updated_by,
security_group_id)
(SELECT null, a.worksheet_id, a.flow_id, a.page_id, a.session_id,
a.base_report_id, upper(v_user_login), a.name || ' kopia' , a.description || ' Kopia od ' || upper(v_user_login),
a.report_seq, a.report_type, a.status, a.category_id, a.autosave,
a.is_default, a.display_rows, a.pagination_min_row,
a.report_columns, a.sort_column_1, a.sort_direction_1,
a.sort_column_2, a.sort_direction_2, a.sort_column_3,
a.sort_direction_3, a.sort_column_4, a.sort_direction_4,
a.sort_column_5, a.sort_direction_5, a.sort_column_6,
a.sort_direction_6, a.break_on, a.break_enabled_on,
a.control_break_options, a.sum_columns_on_break,
a.avg_columns_on_break, a.max_columns_on_break,
a.min_columns_on_break, a.median_columns_on_break,
a.mode_columns_on_break, a.count_columns_on_break,
a.flashback_mins_ago, a.flashback_enabled, a.chart_type,
a.chart_3d, a.chart_label_column, a.chart_label_title,
a.chart_value_column, a.chart_aggregate, a.chart_value_title,
a.chart_sorting, a.calendar_date_column,
a.calendar_display_column, a.created_on, a.created_by,
a.updated_on, a.updated_by, a.security_group_id
FROM flows_030100.wwv_flow_worksheet_rpts a
where a.id = v_ir_id);

end if;

END;

W procedurze tej jest przeprowadzany proces prostego kopiowania/aktualizowania danych w zależności od wartości parametru v_type.

3. Teraz wystarczy podłączyć procedurę na stronie APEX'a i Voilà, moduł gotowy !

Na stronie warto pamiętać aby stworzyć walidacje sprawdzające czy dany raport (o takiej nazwie) już istnieje u użytkownika docelowego. APEX nie pozwala na zapisywanie dla jednego użytkownika raportów o tej samej nazwie.
Skrypt do validacji (NOT Exist)

select 1
FROM flows_030100.apex_application_page_ir ir,
flows_030100.apex_application_page_regions r,
flows_030100.apex_application_page_ir_rpt ir_s
where ir.application_id = :APP_ID
and r.region_id = ir.region_id
and ir_s.report_name = (select report_name || ' kopia' from
flows_030100.apex_application_page_ir_rpt where report_id =
nvl(:P33_REPORT, 0) )
and ir_s.interactive_report_id = ir.interactive_report_id
and ir_s.report_type = 'USER SAVED'
and ir_s.application_user = upper(:P33_USER)

Należy także odpowiednio wybrać raporty jakie dany użytkownik może kopiować:

select r.region_name || ' / ' || ir_s.report_name as name,
ir_s.report_id as id
FROM flows_030100.apex_application_page_ir ir,
flows_030100.apex_application_page_regions r,
flows_030100.apex_application_page_ir_rpt ir_s
where ir.application_id = :APP_ID
and r.region_id = ir.region_id
and ir_s.interactive_report_id = ir.interactive_report_id
and ir_s.report_type = 'USER SAVED'
and ir_s.application_user = upper(:APP_USER)

Na koniec strona Przykład
Na stronie można zalogować się na jednego z 2 użytkowników: user1 i user2
Hasło jest takie samo jak login.
Zaznaczę tylko ze mogą pojawić się różne błędy ze względu na jednoczesne logowanie się na tych samych kontach :/

English text

Hello

After long break I'm back with new Topic.

Did you ever thought about coping Interactive Report to another user of the application ? Lets say you've worked on your report several dozen minutes to put all your needed data together. Then your boss or colleague want to see this report. You cand add them your pass and login to the application. So you could copy this report but this functionality will be available in 4.0 version (as Oracle folks has claimed).

So how copy interactive report to another user ?

At first you need to know that this involves working on FLOWS (APEX in 3.2 version) tables so all operations need to be done with caution.

Lets Begin

1. All saved IR are stored in wwv_flow_worksheet_rpts table of FLOWS (APEX in v.3.2) user. Owner of the application need access to this table. So:

GRANT INSERT,REFERENCES,SELECT,UPDATE ON flows_030100.wwv_flow_worksheet_rpts TO your_user;

2. Next step is to create procedure with will copy/move IR.

CREATE PROCEDURE IR_TRANSFER (
v_ir_id IN NUMBER,
v_user_login IN VARCHAR2,
v_type IN NUMBER DEFAULT 0
)AS
BEGIN
-- this procedure copies or moves IR report. if v_type = 1 then
/* move report */
update flows_030100.wwv_flow_worksheet_rpts
set application_user = upper(v_user_login)
where id = v_ir_id;
elsif v_type = 2 then
/* copy report */
insert into flows_030100.wwv_flow_worksheet_rpts
(id, 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, created_on,
created_by, updated_on, updated_by,
security_group_id)
(SELECT null, a.worksheet_id, a.flow_id, a.page_id, a.session_id,
a.base_report_id, upper(v_user_login), a.name || ' copy' , a.description || ' Kopia od ' || upper(v_user_login),
a.report_seq, a.report_type, a.status, a.category_id, a.autosave,
a.is_default, a.display_rows, a.pagination_min_row,
a.report_columns, a.sort_column_1, a.sort_direction_1,
a.sort_column_2, a.sort_direction_2, a.sort_column_3,
a.sort_direction_3, a.sort_column_4, a.sort_direction_4,
a.sort_column_5, a.sort_direction_5, a.sort_column_6,
a.sort_direction_6, a.break_on, a.break_enabled_on,
a.control_break_options, a.sum_columns_on_break,
a.avg_columns_on_break, a.max_columns_on_break,
a.min_columns_on_break, a.median_columns_on_break,
a.mode_columns_on_break, a.count_columns_on_break,
a.flashback_mins_ago, a.flashback_enabled, a.chart_type,
a.chart_3d, a.chart_label_column, a.chart_label_title,
a.chart_value_column, a.chart_aggregate, a.chart_value_title,
a.chart_sorting, a.calendar_date_column,
a.calendar_display_column, a.created_on, a.created_by,
a.updated_on, a.updated_by, a.security_group_id
FROM flows_030100.wwv_flow_worksheet_rpts a
where a.id = v_ir_id);

end if;

END;

In this procedure there is coping or moving (changing the owner) of the report witch is determined by the parameter v_type.

3. At the end we only need to create site with this procedure and Voilà, module ready!

On page you cannot forget about validation checking if the copied report all ready exists in target user repository. APEX doesn't allow to create 2 reports of the same name for one user.
Script for validation (NOT Exist)

select 1
FROM flows_030100.apex_application_page_ir ir,
flows_030100.apex_application_page_regions r,
flows_030100.apex_application_page_ir_rpt ir_s
where ir.application_id = :APP_ID
and r.region_id = ir.region_id
and ir_s.report_name = (select report_name || ' kopia' from
flows_030100.apex_application_page_ir_rpt where report_id =
nvl(:P33_REPORT, 0) )
and ir_s.interactive_report_id = ir.interactive_report_id
and ir_s.report_type = 'USER SAVED'
and ir_s.application_user = upper(:P33_USER)

And of course you need to select proper reports that user can copy/move:

select r.region_name || ' / ' || ir_s.report_name as name,
ir_s.report_id as id
FROM flows_030100.apex_application_page_ir ir,
flows_030100.apex_application_page_regions r,
flows_030100.apex_application_page_ir_rpt ir_s
where ir.application_id = :APP_ID
and r.region_id = ir.region_id
and ir_s.interactive_report_id = ir.interactive_report_id
and ir_s.report_type = 'USER SAVED'
and ir_s.application_user = upper(:APP_USER)

Finally Example Site
You can log in on one of two users: user1 i user2
Password for both of them is the same as the login.
Please have in mind that there can be some complication as there can be more than one user logged in at this logins :/