This discussion is archived
1 Reply Latest reply: May 20, 2010 11:47 AM by 455683 RSS

How to pass bind variable in report

user11974118 Newbie
Currently Being Moderated
I'm trying to add tables and chart based of the value selected by the user using the filters.

l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(13);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'SELECT TO_DATE(TO_CHAR (alloc.timestamp, ''MON RR''),''MON RR'') AS CALENDAR_MONTH,ROUND(AVG(alloc.avg_size_mb/1024),2) AS SIZE_GB,ROUND(AVG(used.avg_used_mb/1024), 2) AS USED_GB,
ROUND (AVG ((alloc.avg_size_mb - used.avg_used_mb )/1024), 2) AS FREE_GB
FROM
(SELECT m.metric_column AS metric_column,m.rollup_timestamp AS timestamp,sum (m.average) AS avg_size_mb
FROM
mgmt$metric_daily m, mgmt$target_type t
WHERE t.target_name LIKE ??EMIP_BIND_PARAMNAME??
AND (t.target_type = ''rac_database''
OR (t.target_type = ''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST''))
AND m.target_guid = t.target_guid AND m.metric_guid = t.metric_guid AND t.metric_name = ''tbspAllocation''
AND (t.metric_column = ''spaceAllocated'')
and m.rollup_timestamp >= ??EMIP_BIND_START_DATE??
and m.rollup_timestamp <= ??EMIP_BIND_END_DATE??
GROUP BY m.rollup_timestamp, m.metric_column) alloc,
( SELECT m.metric_column AS metric_column, m.rollup_timestamp AS timestamp, sum (m.average) AS avg_used_mb
FROM mgmt$metric_daily m, mgmt$target_type t
WHERE t.target_name = ??EMIP_BIND_PARAMNAME??
AND (t.target_type = ''rac_database''
OR (t.target_type = ''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST''))
AND m.target_guid = t.target_guid AND m.metric_guid = t.metric_guid AND t.metric_name = ''tbspAllocation''
AND (t.metric_column = ''spaceUsed'')
and m.rollup_timestamp >= ??EMIP_BIND_START_DATE??
and m.rollup_timestamp <= ??EMIP_BIND_END_DATE??
GROUP BY m.rollup_timestamp, m.metric_column) used
WHERE alloc.timestamp = used.timestamp
GROUP BY TO_CHAR (alloc.timestamp, ''MON RR'') order by 1');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterNames','NAME');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterPromptNAME','Database Name');
l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterTipNAME','Filter on the target names');
l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterPromptTYPE','Target Type');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterTipTYPE','Filter on the target types');
l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterStartEmpty', 'yes');
l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterEmptyTableHeaders','Calender Month, Size GB,Used GB,Free GB');
l_param_values(9) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterSqlNAME',
'select distinct t.target_name from mgmt$target t where (t.target_type = ''rac_database'' OR (t.target_type = ''oracle_database'' AND t.TYPE_QUALIFIER3 != ''RACINST'')) order by 1');
l_param_values(10) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.numberOfColumnsShowed','4');
l_param_values(11) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterHeaderText','DB Filter');
l_param_values(12) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterTipText','My Tip Text');
l_param_values(13) := MGMT_IP_PARAM_VALUE_RECORD (
'oracle.sysman.eml.ip.render.elem.TableRender.filterButtonText','Get Growth');

l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'user_table_from_sql',
p_element_type_nlsid => 'any_target_type',
p_header_nlsid => 'Select DB for 6 Month Growth Summary',
p_element_order => 1,
p_element_row => 1,
p_parameters => l_param_values,
p_targets => null);

..

Is there a way to pass the EMIP_BIND_PARAMNAME to a new element added to the report ?
  • 1. Re: How to pass bind variable in report
    455683 Explorer
    Currently Being Moderated
    The report framework dev response was that the code was not designed for this use case (e.g. a report filter bind parameter used in multiple report elements). They said it is possible that this may work by chance (go ahead and try to pass the same filter parameters into other report elements and see what happens). If that does not work then you can work with your Oracle support contact to file an enhancement request.

    Edited by: cfowler on May 20, 2010 2:46 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points