RWB Reports
Description
Categories: RWB
Polaris Reporting Workbench reports, folders, data objects, database views and the data object folders that these views reside in
select x.*, count(*) over (partition by x.view_ids, x.hash) dupl_count from ( select distinct rr.report_name, &folder rr.report_short_name, decode(rr.report_type,'USER','Adhoc','SEEDED','Seeded Adhoc','DASH_BOARD','BI Publisher','VAR','PL/SQL Custom','FLEX','Dashboard','SEEDED_VAR','Seeded PL/SQL Custom',rr.report_type) report_type, &execution_count &data_object listagg(rv.view_id,'; ') within group (order by rv.view_id) over (partition by rr.report_id) view_ids, rrp.parameter_count, xxen_util.user_name(rr.owner_person_id) owner, decode(rr.distinct_flag,'Y','Y') distinct_flag, rrc.aggregator, nvl2(rrc3.report_id,'Y',null) custom_aggregation, nvl2(rr.group_clause,'Y',null) custom_group_clause, nvl2(rr.having_clause,'Y',null) custom_having_clause, nvl2(rr.cond_string,'Y',null) custom_where_clause, nvl2(rr.order_clause,'Y',null) custom_order_clause, (select 'Y' from rx_sorts rs where rr.report_id=rs.report_id and rownum=1) sort_exists, rrc.pivot_field, rrc.calculation_column, rr.group_clause, rr.having_clause, rr.order_clause, dbms_lob.substr(rr.cond_string,4000,1) where_clause, count(distinct rfa.folder_id) over (partition by rr.report_id) folder_count, xxen_util.user_name(rr.previous_owner_person_id) previous_owner, rr.description, xxen_util.user_name(rr.created_by) created_by, xxen_util.client_time(rr.creation_date) creation_date, xxen_util.user_name(rr.last_updated_by) last_updated_by, xxen_util.client_time(rr.last_update_date) last_update_date, rr.output_html_flag, rr.output_xls_flag, rr.output_csv_flag, rr.output_xml_flag, rr.output_pdf_flag, rr.output_rtf_flag, rr.template_name, rr.multithread_enable, rr.data_source, rr.max_row_count, rr.validate_flag, rr.query_time_out, rr.regenerate_flag, rr.max_pdf_count, rr.conc_prog_flag, rr.runnow_max_row_count, rr.email_enabled_flag, rr.email_cf, rr.moac_flag, rr.runnow_enabled_flag, dbms_lob.substr(rr.grid_output_config,4000,1) grid_output_config, rr.del_opt_flag, rr.cache_flag, rr.report_id, ora_hash(rrc2.condition_text||rrp.parameter_text||dbms_lob.substr(rr.cond_string)) hash from rx_reports rr, rx_folder_association rfa, rx_folders rf, (select distinct rrc.report_id, rvc.view_id from rx_report_columns rrc, rx_view_columns rvc where rrc.view_column_id=rvc.view_column_id and rrc.enabled_flag='Y') rvc, rx_views rv, rx_folder_association rfa2, rx_folders rf2, (select distinct count(*) over (partition by rrs.report_id) count, count(distinct rrs.user_id) over (partition by rrs.report_id) user_count, rrs.report_id from rx_report_stats rrs where rrs.start_time>sysdate-:days and rrs.report_id>0) rrs, ( select distinct rrc.report_id, max(rrc.aggregator) over (partition by rrc.report_id) aggregator, nvl2(max(rrc.pivot_field) over (partition by rrc.report_id),'Y',null) pivot_field, nvl2(max(rrc.calculation_column) over (partition by rrc.report_id),'Y',null) calculation_column from rx_report_columns rrc where rrc.enabled_flag='Y' ) rrc, ( select distinct rrc.report_id, listagg(rrc.parameter1_id||rrc.parameter2_type||decode(rrc.parameter2_type,'P',rrp2.parameter_name||rrp2.lov_id,rrc.parameter2_id)) within group (order by rrc.cond_operator) over (partition by rrc.report_id) condition_text from rx_report_conditions rrc, rx_report_parameters rrp2 where rrc.parameter1_type='C' and decode(rrc.parameter2_type,'P',rrc.parameter2_id)=rrp2.parameter_id(+) and rrc.enabled_flag='Y' ) rrc2, (select distinct rrp.report_id, count(*) over (partition by rrp.report_id) parameter_count, listagg(rrp.parameter_name||rrp.lov_id) within group (order by rrp.sequence_number) over (partition by rrp.report_id) parameter_text from rx_report_parameters rrp where rrp.enabled_flag='Y') rrp, ( select distinct x.report_id from ( select rrc.report_id, lower(regexp_substr(rrc.calculation_column,'((\w+\.)?\w+)\s*\(',1,rowgen.column_value,null,1)) function_name from rx_report_columns rrc, table(xxen_util.rowgen(regexp_count(rrc.calculation_column,'(\w+\.)?\w+\s*\('))) rowgen where rrc.view_column_id=-1 and rrc.enabled_flag='Y' ) x where x.function_name in ('count','sum','avg','min','max') ) rrc3 where 1=1 and rr.report_id=rfa.report_id(+) and rfa.folder_id=rf.folder_id(+) and rr.report_id=rvc.report_id(+) and rvc.view_id=rv.view_id(+) and rvc.view_id=rfa2.view_id(+) and rfa2.folder_id=rf2.folder_id(+) and rr.report_id=rrs.report_id(+) and rr.report_id=rrc.report_id(+) and rr.report_id=rrc2.report_id(+) and rr.report_id=rrp.report_id(+) and rr.report_id=rrc3.report_id(+) ) x order by 1, x.report_name, 6 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Report Name |
|
LOV | |
Data Object |
|
LOV | |
View Name |
|
LOV | |
Report Id |
|
Number | |
View Id |
|
Number | |
Report Folder |
|
LOV | |
Data Object Folder |
|
LOV | |
Execution Count within Days |
|
Number | |
Active only |
|
LOV | |
Expand Folders |
|
LOV Oracle | |
Expand Data Objects |
|
LOV Oracle |