Wednesday, 12 November 2008

"Generating" interactive reports

I was recently working on an Apex application where user can define views on a bunch of technical tables. It works like this: a user chooses the columns that he or she wants to see (based on some meta information) and a view can be generated and installed on the fly.
Of course the user wants to browse the data in the view immediately. Using a Classic Report based on a procedure that returns the query would be an option. But then I would miss all the nice features of an interactive report. However, you can not define an interactive report based on a procedure.
The solution: the Apex API.

The Apex API offers you a set of procedures where you can create new pages and -this was where I was looking for- new interactive reports. I created a very simple application with only one interactive report and exported it to a sql-file. If you then look at the sql-file, you can see how easy it is to add new pages to your application.

Below is a sample pl/sql procedure that creates a new page with a new interactive report based on a custom query. Of course you probably need to adjust it to your own Apex application, but it should not be very difficult to get it to work.

Have fun with it !


declare
--
-- Cursor to check if the report already exists
--
cursor c_ape (b_page_id in apex_application_pages.page_id%type) is
select 1
from apex_application_pages ape
where ape.application_id = v('APP_ID')
and ape.page_id = b_page_id
;
--
l_page_id apex_application_pages.page_id%type;
l_dummy pls_integer;
l_region_id number := wwv_flow_id.next_val;
l_worksheet_id number := wwv_flow_id.next_val;
l_table varchar2(30);
l_query varchar2(32767);
l_report_columns varchar2(32767);
--
begin
--
wwv_flow_api.set_security_group_id(p_security_group_id=>979127191492671);
wwv_flow.g_flow_id := 129;
l_page_id := 1000;
--
open c_ape(b_page_id => l_page_id);
fetch c_ape into l_dummy;
if c_ape%found
then
--
-- Delete the page if it already exists
--
wwv_flow_api.remove_page(p_flow_id => v('APP_ID')
,p_page_id => l_page_id
);
end if;
close c_ape;
--
-- Create a new page
--
wwv_flow_api.create_page(p_id => l_page_id
,p_flow_id => v('APP_ID')
,p_tab_set => 'TS1'
,p_tab_name => 'Browse Data'
,p_alias => 'BROWSE'
,p_name => 'My Query'
,p_step_title => 'My Query'
,p_step_sub_title => 'My Query'
,p_step_sub_title_type => 'TEXT_WITH_SUBSTITUTIONS'
,p_include_apex_css_js_yn => 'Y'
,p_page_is_public_y_n => 'Y'
,p_last_upd_yyyymmddhh24miss => to_char(sysdate, 'YYYYMMDDHH24MISS')
,p_page_comment => 'Inserted with script'
);
--
l_query := 'SELECT EMPNO, ENAME, SAL FROM EMP';
l_report_columns := 'EMPNO:ENAME:SAL';
--
wwv_flow_api.create_page_plug (p_id => l_region_id
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_plug_name => 'My Query'
,p_plug_template => 0
,p_plug_display_sequence => 10
,p_plug_display_column => 1
,p_plug_display_point => 'AFTER_SHOW_ITEMS'
,p_plug_source => l_query
,p_plug_source_type => 'DYNAMIC_QUERY'
,p_plug_display_error_message => 'Unable to show report.'
,p_plug_query_row_template => 1
,p_plug_query_headings_type => 'COLON_DELMITED_LIST'
,p_plug_query_row_count_max => 500
,p_plug_display_condition_type => ''
,p_plug_caching => 'NOT_CACHED'
,p_plug_comment => ''
);
wwv_flow_api.create_worksheet(p_id => l_worksheet_id
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_region_id => l_region_id
,p_name => 'My Query'
,p_alias => ''
,p_report_id_item => ''
,p_max_row_count => '10000'
,p_max_row_count_message => 'This query returns more then 10,000 rows, please filter your data to ensure complete results.'
,p_no_data_found_message => 'No data found.'
,p_max_rows_per_page => ''
,p_search_button_label => ''
,p_page_items_to_submit => ''
,p_sql_query => l_query
,p_status =>'AVAILABLE_FOR_OWNER'
,p_allow_report_saving =>'Y'
,p_allow_report_categories =>'Y'
,p_show_nulls_as =>'-'
,p_pagination_type =>'ROWS_X_TO_Y'
,p_pagination_display_pos =>'BOTTOM_RIGHT'
,p_show_finder_drop_down =>'Y'
,p_show_display_row_count =>'Y'
,p_show_search_bar =>'Y'
,p_show_search_textbox =>'Y'
,p_show_actions_menu =>'Y'
,p_report_list_mode =>'TABS'
,p_show_detail_link =>'Y'
,p_show_select_columns =>'Y'
,p_show_filter =>'Y'
,p_show_sort =>'Y'
,p_show_control_break =>'Y'
,p_show_highlight =>'Y'
,p_show_computation =>'Y'
,p_show_aggregate =>'Y'
,p_show_chart =>'Y'
,p_show_calendar =>'Y'
,p_show_flashback =>'Y'
,p_show_reset =>'Y'
,p_show_download =>'Y'
,p_download_formats =>'CSV'
,p_allow_exclude_null_values =>'Y'
,p_allow_hide_extra_columns =>'Y'
,p_owner =>v('APP_USER')
);
wwv_flow_api.create_worksheet_column(p_id => wwv_flow_id.next_val
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_db_column_name => 'empno'
,p_display_order => 1
,p_group_id => null
,p_column_identifier => 'A'
,p_column_label => 'EMPNO'
,p_report_label => 'EMPNO'
,p_sync_form_label =>'Y'
,p_display_in_default_rpt =>'Y'
,p_is_sortable =>'Y'
,p_allow_sorting =>'Y'
,p_allow_filtering =>'Y'
,p_allow_ctrl_breaks =>'Y'
,p_allow_aggregations =>'Y'
,p_allow_computations =>'Y'
,p_allow_charting =>'Y'
,p_others_may_edit =>'Y'
,p_others_may_view =>'Y'
,p_column_type =>'NUMBER'
,p_display_as =>'TEXT'
,p_display_text_as =>'ESCAPE_SC'
,p_heading_alignment =>'CENTER'
,p_column_alignment =>'LEFT'
,p_rpt_distinct_lov =>'Y'
,p_rpt_show_filter_lov =>'D'
,p_rpt_filter_date_ranges =>'ALL'
,p_help_text =>'The employee''s unique number'
);
wwv_flow_api.create_worksheet_column(p_id => wwv_flow_id.next_val
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_db_column_name => 'ENAME'
,p_display_order => 2
,p_group_id => null
,p_column_identifier => 'B'
,p_column_label => 'ENAME'
,p_report_label => 'ENAME'
,p_sync_form_label =>'Y'
,p_display_in_default_rpt =>'Y'
,p_is_sortable =>'Y'
,p_allow_sorting =>'Y'
,p_allow_filtering =>'Y'
,p_allow_ctrl_breaks =>'Y'
,p_allow_aggregations =>'Y'
,p_allow_computations =>'Y'
,p_allow_charting =>'Y'
,p_others_may_edit =>'Y'
,p_others_may_view =>'Y'
,p_column_type =>'STRING'
,p_display_as =>'TEXT'
,p_display_text_as =>'ESCAPE_SC'
,p_heading_alignment =>'CENTER'
,p_column_alignment =>'LEFT'
,p_rpt_distinct_lov =>'Y'
,p_rpt_show_filter_lov =>'D'
,p_rpt_filter_date_ranges =>'ALL'
,p_help_text =>'The employee''s name'
);
wwv_flow_api.create_worksheet_column(p_id => wwv_flow_id.next_val
,p_flow_id => v('APP_ID')
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_db_column_name => 'SAL'
,p_display_order => 3
,p_group_id => null
,p_column_identifier => 'C'
,p_column_label => 'SAL'
,p_report_label => 'SAL'
,p_sync_form_label =>'Y'
,p_display_in_default_rpt =>'Y'
,p_is_sortable =>'Y'
,p_allow_sorting =>'Y'
,p_allow_filtering =>'Y'
,p_allow_ctrl_breaks =>'Y'
,p_allow_aggregations =>'Y'
,p_allow_computations =>'Y'
,p_allow_charting =>'Y'
,p_others_may_edit =>'Y'
,p_others_may_view =>'Y'
,p_column_type => 'NUMBER'
,p_display_as =>'TEXT'
,p_display_text_as =>'ESCAPE_SC'
,p_heading_alignment =>'CENTER'
,p_column_alignment =>'LEFT'
,p_rpt_distinct_lov =>'Y'
,p_rpt_show_filter_lov =>'D'
,p_rpt_filter_date_ranges =>'ALL'
,p_help_text =>'The employee''s salary'
);
--
wwv_flow_api.create_worksheet_rpt(p_id => wwv_flow_id.next_val
,p_flow_id => wwv_flow.g_flow_id
,p_page_id => l_page_id
,p_worksheet_id => l_worksheet_id
,p_session_id => null
,p_base_report_id => null
,p_application_user => 'APXWS_DEFAULT'
,p_report_seq =>10
,p_status =>'PUBLIC'
,p_category_id =>null
,p_is_default =>'Y'
,p_display_rows =>15
,p_report_columns =>l_report_columns
,p_flashback_enabled =>'N'
,p_calendar_display_column =>''
);
--
end;