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;

5 comments:

Angeline said...
This comment has been removed by a blog administrator.
Suhas said...

Hi,

I have an interactive report generatet this way but tables having more no. of columns (say 500) face error as: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
while browsing the report. How can we avoid this. Please suggest a solution.

Peter de Boer said...

Hi Suhas,

You probably need to limit the number of columns displayed in your report.

Probably if you open the shuttle control in the IR with the columns and choose less columns to be displayed, your report may run properly again.

In your generator you can specify the displayed columns with parameter p_report_columns in procedure wwv_flow_api.create_worksheet_rpt

Regards,
Peter

Suhas said...
This comment has been removed by the author.
Suhas said...

Hi Peter,
In my application dynamic IRs are generated for all database tables
using method that you have posted. I am not supposed to hide the columns from simply because I am getting an error. And APEX doesn't have column display limitation (it can display upto 1000 columns). It cant dispaly data in IR beyond 32K. Tables having just 400-450 columns face this error because of this limitation.

Is there any way by which we can probably adjust columns via pagination i.e. first 200 columns on first page and rest on next...as we adjust rows in IR?


or atleast can we inform users via some pop up or user understandable error message to choose desired columns via "FILTER" option to view the report?

oracle error as ORA-06502: PL/SQL: numeric or value error: character string buffer too small
is so not happening for users.

Please suggest.

Thanks,
Suhas