4 Adding Reports

Defining new target types in Enterprise Manager via Management Plug-ins also provides you with the opportunity to add new report definitions. Plug-ins allow you to add permanent (SYSTEM) target type-specific report definitions to Enterprise Manager using the Information Publisher PL/SQL API.

This chapter covers the following:

What You Get
Report Definition File
Creating a Report Definition File
PL/SQL Application Programmer Interface
Development Guidelines

Note:

You must have a working knowledge of SQL and PL/SQL before using this API. Refer to the Oracle Database PL/SQL User's Guide and Reference for more information.

What You Get

Adding report definitions via Management Plug-in creates target type-specific SYSTEM reports. SYSTEM report definitions are handled differently than definitions created through the Information Publisher user interface. SYSTEM reports are permanent and cannot be deleted or edited by Enterprise Manager administrators. You can add multiple report definitions to a Management Plug-in, thus allowing you to associate multiple reports with a specific target type.

Adding SYSTEM report definitions using Management Plug-ins and the Information Publisher API allows users to access reports from two areas of the Enterprise Manager console:

Reports page of the target home page (optional).
Information Publisher's Report Definition page.

Reports Page (Target Home Page)

In the report definition file, you can specify whether or not a report is available from the Reports page of the target home page. Report definitions you add to the Reports page are available from the View Reports drop-down menu list. The following figure shows the Reports page for a Microsoft SQL Server target.

Figure 4-1 Microsoft SQL Server Reports Page

Description of "Figure 4-1 Microsoft SQL Server Reports Page"

Report Definitions Page

Regardless of whether you specify that a report appear in the Reports page, all report definitions added via Management Plug-ins are available from Information Publisher's Report Definitions page. As with out-of-box SYSTEM report definitions, those added via Management Plug-ins are organized according to report category and subcategory. SYSTEM report definitions cannot be deleted from the Enterprise Manager console. Figure 4-2, "Report Definitions Page" shows available report definitions for Microsoft SQL Server target types.

Figure 4-2 Report Definitions Page

Description of "Figure 4-2 Report Definitions Page"

Report Definition File

A report definition file is structured as a conventional PL/SQL block and contains code to extract pertinent information from the Management Repository and the report elements used to format and display that data. The Information Publisher PL/SQL API allows you specify the report elements and parameters that you normally specify interactively when creating a report definition from the Enterprise Manger console.

The following example shows the Reports page for an "oracle_orgchart" target type that was added as a Management Plug-in. A single report titled "Org breakdown by title (oracle_orgchart)" is shown in Figure 4-3. This report consists of three major areas: a pie chart showing the percentage distribution of people by title, a bar chart showing the top five managers having the most people reporting to them, and a table listing all managers in the organization the total number of direct reports.

Figure 4-3 Oracle Organization Report

Description of "Figure 4-3 Oracle Organization Report"

The report definition used for this example utilizes two report element types: Chart from SQL (pie and bar), and Table from SQL. In general, these two report elements will meet most reporting needs.

Creating a Report Definition File

Management Plug-ins allow you to define as many report definition files as is required for a particular target type. The content of a report definition file consists of a PL/SQL block that uses the Information Publisher PL/SQL API (part of the MGMT_IP package) to construct a report.

Report Definition File Development Process

The process of developing a valid report definition file involves three steps:

Define SQL or PL/SQL queries used to extract information from the Management Repository.
Create a test report interactively from the Enterprise Manager console.
Use the PL/SQL API to create a report definition file.

Define SQL or PL/SQL queries used to extract information from the Management Repository.

The first step in creating your report definition is to create the SQL or PL/SQL queries used to extract the requisite report information from the Management Repository. Enterprise Manager provides management views with which you can safely extract data from the Management Repository without reading from the base tables. Using repository views protects your queries from changes to the repository schema that may occur in future releases and ensures your SYSTEM report definitions remain functional. A complete listing of repository views can be found in Chapter 6, "Management Repository Views".

The following query was used to extract repository information about the distribution of employee classifications within an Oracle organization. The query uses the MGMT$METRIC_CURRENT repository view.

select 'senior mts', count(value) from mgmt$metric_current 
where metric_column = 'Title' and LOWER(value) like '%senior member%' and 
      target_guid = ??EMIP_BIND_TARGET_GUID??  
union 
select 'principal mts', count(value) from mgmt$metric_current 
where metric_column = 'Title' and LOWER(value) like '%principal member%'and   
      target_guid = ??EMIP_BIND_TARGET_GUID??  
           union 
           select 'consulting mts', count(value) from mgmt$metric_current 
           where metric_column = 'Title' and LOWER(value) like '%consulting%' and 
                 target_guid = ??EMIP_BIND_TARGET_GUID?? ;

When an administrator views a report from the Enterprise Manager console that contains this SQL query string, Information Publisher automatically binds the unique identifier for the selected target to the ??EMIP_BIND_TARGET_GUID?? placeholder in the SQL query string. The documentation for Chart from SQL and Table from SQL parameters provide information on this bind variable placeholder as well as others you can include in your SQL query string.

The data returned from the SQL query is then used by the Chart from SQL report element to render the pie chart shown in the Reports page of an oracle_orgchart target home page as shown in Figure 4-3.

Create a test report interactively from the Enterprise Manager console.

Once you have written and tested the SQL or PL/SQL query, you can use the Enterprise Manager console to generate a version of your report interactively using the Chart from SQL and Table from SQL report elements. By using the Information Publisher user-interface, you can easily prototype reports without having to create a report definition file and import Management Plug-in Archives. You can also use this method of interactive prototyping to refine your queries and ensure that the data extracted from the Management Repository and how that information is rendered in your report meets your reporting requirements. See Enterprise Manager online help for the Chart from SQL and Table from SQL 'Set Parameters' function for information and examples on how to format your queries.

Use the PL/SQL API to create a report definition file.

Once you are satisfied with the way your report is being rendered by Information Publisher, you are ready to create the report definition file. As previously mentioned, the report definition file consists of a PL/SQL block defining the report elements and the SQL or PL/SQL queries used by the elements to extract repository information and render the report. Example 4-1 shows the content of the report definition file associated with the oracle_orgchart target type.

Example 4-1 Oracle Organization Report Definition File

BEGIN
        
         DECLARE
           l_target_types  MGMT_IP_TARGET_TYPES;
           l_param_values  MGMT_IP_PARAM_VALUE_LIST;
           l_target_type   MGMT_IP_TARGET_TYPES;
           l_report_guid   RAW(16);
           l_element_guid  RAW(16);
           l_report_title_nlsid  VARCHAR2(128);
           l_report_owner VARCHAR(100);
           l_curr_order    NUMBER;
           l_curr_row      NUMBER;
        
           BEGIN
        
        -- specify the target type associated with this report
        -- in this case the target type is 'oracle_orgchart'
           l_target_type := MGMT_IP_TARGET_TYPES();
           l_target_type.extend(1);
           l_target_type(1) := 'oracle_orgchart';
           l_report_title_nlsid := 'Org breakdown by title (oracle_orgchart)<>';
           l_report_owner := mgmt_user.get_repository_owner;
        
        -- create a report definition for the report
           l_report_guid := mgmt_ip.create_report_definition (
             p_title_nlsid                => l_report_title_nlsid,
             p_description_nlsid          => 'Charts showing the breakdown by title',
             p_owner                      => l_report_owner,
             p_category_nlsid             => 'Test Org Chart Reports',
             p_sub_category_nlsid         => 'Interesting Org Data',
             p_late_binding_target_types  => l_target_type,
             p_show_table_of_contents     => 0,
             p_system_report              => 1,
             p_component_name             => l_target_type(1)
             );
        
           -- Add report so that it shows up under "reports" tab in default home page
           mgmt_mp_homepage.add_report (
             p_target_type        => l_target_type(1),
             p_report_title       => l_report_title_nlsid,
             p_report_owner       => l_report_owner,
             p_report_order       => 1
             );
        
        -- create the first element in the report
        -- it will be a text element with a description of the report contents
        
        -- set the parameters for the styled text element
        -- we?ll provide a message and a display style
           l_param_values := MGMT_IP_PARAM_VALUE_LIST();
           l_param_values.extend(2);
           l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
             'Test Org Chart Monitoring');
           l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
             'OraInstructionText');
        
        -- initialize the sequencing variables
        -- l_curr_order should sequentially increase from 1 to the number of elements in
-- the report
        -- l_curr_row indicates the row on which to display an element
            l_curr_order := 1;
            l_curr_row := 1;
        
           l_element_guid := mgmt_ip.add_element_to_report_def (
             p_report_guid        => l_report_guid,
             p_element_name_nlsid => 'IPMSG_STYLED_TEXT',
             p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
 
             p_header_nlslid      => null,
             p_element_order      => l_curr_order,
             p_element_row        => l_curr_row,
             p_parameters         => l_param_values,     
     p_targets            => null
        
             );
        
        -- the second element in the report is a pie chart showing
        -- the count of employees by title
                
           l_param_values := mgmt_ip_param_value_list();
           l_param_values.extend(3);
           l_param_values(1) := mgmt_ip_param_value_record(
             'oracle.sysman.eml.ip.render.elem.sqlStatement',
             'select ''senior mts'', count(value) 
                from mgmt$metric_current where metric_column = ''Title'' and LOWER(value)
                  like ''%senior member%'' and target_guid = ??EMIP_BIND_TARGET_GUID??
 union 
                select ''principal mts'', count(value) 
                  from mgmt$metric_current 
                where metric_column = ''Title'' and LOWER(value) 
                  like ''%principal member%'' and target_guid = ??EMIP_BIND_TARGET_GUID??
                  union 
                  select ''consulting mts'', count(value) 
                    from mgmt$metric_current where metric_column = ''Title'' and
                    LOWER(value) like ''%consulting%'' and 
                    target_guid = ??EMIP_BIND_TARGET_GUID?? '
             );
           l_param_values(2) := mgmt_ip_param_value_record(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType',
             'pieChart');
           l_param_values(3) := mgmt_ip_param_value_record(
        'oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels',
 'true');
        
           l_curr_order := l_curr_order + 1;
           l_curr_row := l_curr_row + 1;
        
        -- add pie chart to report definiton
        -- 
           l_element_guid := mgmt_ip.add_element_to_report_def (
             p_report_guid        => l_report_guid,
             p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL',
             p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
             p_header_nlslid      => 'people by title',
             p_element_order      => l_curr_order,
             p_element_row        => l_curr_row,
             p_parameters         => l_param_values ,
             p_targets            => null
        
             );
        
        --add a bar chart showing number of reports by manager
        --
        
           l_param_values := MGMT_IP_PARAM_VALUE_LIST();
           l_param_values.extend(3);
           l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.sqlStatement',
             'select * from (select key_value, to_number(value) "Number of Reports" from
              mgmt$metric_current where target_type = ''oracle_orgchart'' and 
              metric_column = ''Reports'' and value is not null  and 
              target_guid = ??EMIP_BIND_TARGET_GUID?? order by to_number(value) DESC )
              where rownum < 6'
             );
           l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType',
             'barChart');
           l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
             '800');
        
            l_curr_order := l_curr_order + 1;
            l_curr_row := l_curr_row + 1;
        
           l_element_guid := mgmt_ip.add_element_to_report_def (
             p_report_guid        => l_report_guid,
             p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL',
             p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
             p_header_nlslid      => 'Top 5 Number of Reports By Manager',
             p_element_order      => l_curr_order,
             p_element_row        => l_curr_row,
             p_parameters         => l_param_values,     
     p_targets            => null
        
             );
        
        -- the next element is a table the manager name and number of reports per manager
                
           l_param_values := MGMT_IP_PARAM_VALUE_LIST();
           l_param_values.extend(1);
           l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.sqlStatement',
             'select key_value "Person", to_number(value) "Number of Reports" from
              mgmt$metric_current where target_type = ''oracle_orgchart'' and 
              metric_column = ''Reports'' and value is not null  and 
              target_guid = ??EMIP_BIND_TARGET_GUID?? order by to_number(value) DESC '
             );
        
           l_curr_order := l_curr_order + 1;
           l_curr_row := l_curr_row + 1;
        
           l_element_guid := mgmt_ip.add_element_to_report_def (
             p_report_guid        => l_report_guid,
             p_element_name_nlsid => 'IPMSG_USER_TABLE_FROM_SQL',
             p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
             p_header_nlslid      => 'Number of Reports By Manager',
             p_element_order      => l_curr_order,
             p_element_row        => l_curr_row,
             p_parameters         => l_param_values ,
             p_targets            => null
             );
        
           END;
        
        END;

Report Lifecycle: Updating Report Definitions

With the ability to add report definitions to Enterprise Manager comes the responsibility of maintaining and updating the report definitions. Familiarity with the way in which Enterprise Manager handles report definitions will allow you to anticipate system behavior and plan for backwards compatibility.

When report definitions are deployed via Management Plug-in, Enterprise Manager only allows newer versions of the report definitions to be installed. Older report definitions are deleted and deregistered so as not to appear on the Reports subtab of a target home page. These actions eliminate potential version conflicts by ensuring that updated report definitions are deployed to clean systems. Enterprise Manager will not install older versions of a report definition.

Report definitions, as with Management Plug-ins in general, should be designed with backwards compatibility in mind. Future versions of report definitions should support previous versions of the target type metadata. Report definition-metadata version incompatibility will be most apparent in the following situations:

Report definitions included with Management Plug-in version 1 AND not included with Management Plug-in version 2 will disappear when version 2 is deployed, even if version 1 deployments remain on some Management Agents.
If version 1 and version 2 of a Management Plug-in are both deployed to the system, Management Agents will collect data based on the metadata of the version installed at that Agent; some will collect for version 1 metadata and some for version 2 metadata. Only the version 2 report definitions will be installed (appear in the Enterprise Manager console). For this reason, version 2 report definitions must support both versions of the metadata.

PL/SQL Application Programmer Interface

The Information Publisher PL/SQL API allows you to create a report definition file.

PL/SQL Methods for Creating Report Definitions

Use the following PL/SQL methods to create and/or manipulate report definitions when creating report definition files.

mgmt_ip.create_report_definition
mgmt_ip.create_report_definition
mgmt_mp_homepage.add_report

mgmt_ip.create_report_definition

Call this method to create a new report definition. Once a report definition is created, elements can be added. The create_report_definition method is part of the MGMT_IP PL/SQL package.

Note:

All of the Management Plug-in reports should set p_system_report to 1. This parameter defines the report definition as a SYSTEM report, which cannot be deleted or edited by Enterprise Manager administrators. p_owner should be set to mgmt_user.get_repository_owner for all Management Plug-in reports.

The p_component_name must be set to the target type of the management plug-in.

Input

Parameter	Description
p_title_nlsid	report title.
p_description_nlsid	description
p_owner	owner name (should be the value returned from mgmt_user.get_repository_owner for Plug-In reports)
p_category_nlsid	category name
p_sub_category_nlsid	subcategory name
p_late_binding_target_types	target type for late binding, or null if not late binding
p_show_table_of_contents	1=show 0=hide
p_system_report	1=system report, 0=end user report. This must be set to 1 for Management Plug-in reports.
p_show_navigation	Show navigation headers in report (tabs, etc) 1=show, 0=hide
p_product_name	Product name, 'EM'(default)
p_component_name	Product component. This must be set to the Management Plug-in target type.
p_version	Version, '10.2' (default)
p_parameters	Parameters for this report definition

Output

Returns the GUID for this report definition.

Code

FUNCTION create_report_definition (     p_title_nlsid                       IN VARCHAR2,     p_description_nlsid                 IN VARCHAR2,     p_owner                             IN VARCHAR2,     p_category_nlsid                    IN VARCHAR2,     p_sub_category_nlsid                IN VARCHAR2,                     p_late_binding_target_types         IN MGMT_IP_TARGET_TYPES DEFAULT NULL,     p_show_table_of_contents            IN NUMBER DEFAULT 0,     p_system_report                     IN NUMBER DEFAULT 1,     p_show_navigation                   IN NUMBER DEFAULT 1,     p_product_name                      IN VARCHAR2 DEFAULT 'EM',     p_component_name                    IN VARCHAR2 DEFAULT '',     p_version                           IN VARCHAR2 DEFAULT '10.2.0.1.0',     p_parameters                        IN MGMT_IP_PARAM_VALUE_LIST DEFAULT NULL     ) RETURN RAW;

mgmt_ip.create_report_definition

Call this method to add a new report element to an existing report definition. The add_element_to_report_def method is part of the MGMT_IP PL/SQL package.

Input

Parameter	Description
p_report_guid	GUID to identify the report definition.
p_element_name_nlsid	The element name.
p_element_type_nlsid	The element type name.
p_header_nlslid	The element header or null.
p_element_order	The order of this element, 1 based.
p_element_row	The row for this element, 1 based.
p_parameters	The parameters for this element.

Output

Returns the GUID for this element instance.

Code

FUNCTION add_element_to_report_def(          p_report_guid                       IN RAW,          p_element_name_nlsid                IN VARCHAR2,          p_element_type_nlsid                IN VARCHAR2,          p_header_nlsid                      IN VARCHAR2 DEFAULT NULL,          p_element_order                     IN NUMBER,          p_element_row                       IN NUMBER,          p_parameters                        IN MGMT_IP_PARAM_VALUE_LIST,          p_targets                           IN MGMT_IP_TARGET_LIST          ) RETURN RAW;

mgmt_mp_homepage.add_report

Call this method to register a report for display in the Reports subtab on the target home page for a report. The add_report method is part of the MGMT_MP_HOMEPAGE PL/SQL package.

The input parameters p_target_type, p_report_title, and p_report_owner MUST be identical to the report definition being registered.

IMPORTANT:

The value returned from mgmt_user.get_repository_owner must be specified as the report owner in order for the report to appear on the Reports subtab of a target home page.

Input

Parameter	Description
p_target_type	The target type.
p_report_title	The report title.
p_report_owner	The report owner.
p_order	The order the report shows up in the homepage.

Output

None.

Code

PROCEDURE add_report(p_target_type IN VARCHAR2, p_report_title IN VARCHAR2,
                     p_report_owner IN VARCHAR2, p_report_order IN NUMBER);

mgmt_view_util.adjust_tz

Call this function to convert the time zone of v_date_in from the v_from_tz time zone to the v_to_tz time zone.

Input

Parameter	Description
v_date_in	Date to be converted to a different time zone
v_from_tz	time zone of date being converted.
v_to_tz	time zone into which date will be converted.

Output

Returns the adjusted date in the new time zone.

Code

FUNCTION ADJUST_TZ(v_date_in       DATE,
                   v_from_tz  VARCHAR2,
                   v_to_tz    VARCHAR2)
RETURN DATE;

PL/SQL Type Definitions

Three PL/SQL types are required to use the PL/SQL methods documented in Chapter4, "PL/SQL Methods for Creating Report Definitions" . The definitions for these types are shown below.

MGMT_IP_TARGET_TYPES

Use MGMT_IP_TARGET_TYPES type to pass the target type your report definition supports to the create_report_definition API as the p_late_binding_target_types parameter.

CREATE OR REPLACE TYPE MGMT_IP_TARGET_TYPES 
AS TABLE OF VARCHAR(64);

Example 4-2 MGMT_IP_TARGET_TYPES

DECLARE
 l_target_type   MGMT_IP_TARGET_TYPES; 
BEGIN
        -- specify the target type associated with this report
        -- in this case the target type is 'oracle_orgchart'
           l_target_type := MGMT_IP_TARGET_TYPES();
           l_target_type.extend(1);
           l_target_type(1) := 'oracle_orgchart';
END;

MGMT_IP_PARAM_VALUE_LIST and MGMT_IP_PARAM_VALUE_RECORD

Use the MGMT_IP_PARAM_VALUE_LIST type to pass parameter values to the create_report_definition API as the p_parameters parameter and to the add_element_to_report_def API as the p_paremeters parameter.

CREATE OR REPLACE TYPE MGMT_IP_PARAM_VALUE_LIST AS TABLE OF MGMT_IP_PARAM_VALUE_RECORD;

Use the MGMT_IP_PARAM_VALUE_RECORD type to create a named parameter value pair to add to an object of type MGMT_IP_PARAM_VALUE_LIST.

CREATE OR REPLACE TYPE MGMT_IP_PARAM_VALUE_RECORD 
AS OBJECT (PARAM VARCHAR2(100),  VALUE CLOB);

Example 4-3 MGMT_IP_PARAM_VALUE_RECORD and MGMT_IP_PARAM_VALUE_LIST

DECLARE
           l_param_values  MGMT_IP_PARAM_VALUE_LIST;  
BEGIN
           l_param_values := MGMT_IP_PARAM_VALUE_LIST();
           l_param_values.extend(2);
           l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
             'Test Org Chart Monitoring');
           l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
             'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle',
             'OraInstructionText');
END;

Element Parameters

Parameters used by some report elements dictate the operational behavior of those elements. This section lists the parameters associated with specific report elements.

Table Element Parameters

The Table Element is used to show a tabular view of query results. The queries must be made against management views.

Element Name: IPMSG_USER_TABLE_FROM_SQL
Element Type: IPMSG_ANY_TARGET_TYPE

Time Period

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TimePeriodParam"
Required	No
Default Value	Null
Valid Values	"0:0" for last 24 Hours "0:1" for last 7 Days "0:2" for last 31 Days
Summary	Encoded time period.

Sort Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn"
Required	No
Default Value	The first column in result set.
Valid Values	Any valid column name.
Summary	If this parameter is set, the sort column indicator will be shown for the column with this column name. If not set, the sort column indicator is shown on the first column. The SQL query should include an 'order by' clause that sorts by this column.

Sort Order

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder"
Required	No
Default Value	"ascending"
Valid Values	"ascending" or "descending"
Summary	If this parameter is set, the sort column indicator will be shown either as ascending or descending, according to the value. If not set, the sort column indicator is shown as ascending.

Name Value Pair Display

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.nameValueDisplay"
Required	No
Default Value
Valid Values	Positive integer value.
Summary	If this parameter is set and only one row is returned from the query, the results are displayed in a vertical list of name-value pairs. This value should be set to the number of name/value columns that should be displayed, normally "1".

Number of Rows to Show

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow"
Required	No
Default Value	"10"
Valid Values	Positive integer value.
Summary	Number of rows to display at one time in the generated table. The user can scroll through additional rows using the UI controls..

Is PL/SQL Statement

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql"
Required	No
Default Value	"false"
Valid Values	"true" or "false"
Summary	Whether a SQL statement is PL/SQL.

SQL or PL/SQL Statement

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.sqlStatement"
Required	No
Default Value	<None>
Valid Values	Any valid SQL SELECT statement.
Summary	SQL statement can optionally bind values for targets, locale information, and start/end date. The format of the SQL statement should include a bind variable placeholders for the options to be bound. Bind Placeholders ??EMIP_BIND_RESULTS_CURSOR?? For use with PL/SQL statement to bind a return cursor containing results for display. ??EMIP_BIND_TARGET_GUID?? For use with SQL or PL/SQL to bind a target GUID. ??EMIP_BIND_START_DATE?? For use with SQL or PL/SQL to bind a start date. ??EMIP_BIND_END_DATE?? For use with SQL or PL/SQL to bind an end date. ??EMIP_BIND_TIMEZONE_REGION?? For use with SQL or PL/SQL to bind a time zone region. ??EMIP_BIND_LOCALE_COUNTRY?? For use with SQL or PL/SQL to bind a locale country. ??EMIP_BIND_LOCALE_LANGUAGE?? For use with SQL or PL/SQL to bind a locale language. There should be no semi-colon (;) appended to the end of the SQL statement unless it is a PL/SQL statement.

Example 4-4 Specifying an Anonymous PL/SQL Block as a Parameter to an Element Definition

l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
  'true');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
  'oracle.sysman.eml.ip.render.elem.sqlStatement',
  'BEGIN
    DECLARE
        rep_tz          VARCHAR2(200);
        user_tz_in          VARCHAR2(200);
        TYPE CURSOR_TYPE IS REF CURSOR;
        result_cursor_out CURSOR_TYPE;
        start_date_in DATE DEFAULT NULL;
        end_date_in  DATE DEFAULT NULL;
        query_string   VARCHAR(6000);            
    BEGIN
        result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
        start_date_in := ??EMIP_BIND_START_DATE??;
        end_date_in := ??EMIP_BIND_END_DATE??;
        user_tz_in := ??EMIP_BIND_TIMEZONE_REGION??;
        select TIMEZONE_REGION into rep_tz from mgmt$target where TARGET_TYPE = ''oracle_emrep'';
        query_string := ''WITH dates AS (SELECT 
      mgmt_view_util.adjust_tz(:1,:2,:3) as start_date, 
      mgmt_view_util.adjust_tz(:4,:5,:6) as end_date  
   from dual)
  SELECT 
       label, 
       time,
       sum(violations) --) as violations
   FROM(
       (
        SELECT
           ''''''||''NEW_LBL''||'''''' as label,
           mgmt_view_util.adjust_tz(rollup_timestamp, :7, :8) as time,
           average as violations --new violations
        FROM
           MGMT$METRIC_HOURLY
        WHERE
           key_value in (''''18'''',''''20'''',''''25'''') AND 
           ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND
           ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND
           target_type = ''''oracle_emrep'''' AND 
           metric_name = ''''TARGET_SECURITY_NEW_VIOLATIONS'''' AND
           metric_column = ''''NEW_VIOLATIONS'''' )
       UNION 
       (
        SELECT
           ''''''||''FIXED_LBL''||'''''' as label,
           mgmt_view_util.adjust_tz(rollup_timestamp,:9,:10) as time,
           average as violations --cleared violations
        FROM 
           MGMT$METRIC_HOURLY
        WHERE 
           key_value in (''''18'''',''''20'''',''''25'''') AND
           ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND
           ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND
           target_type = ''''oracle_emrep'''' AND 
           metric_name = ''''TARGET_SECURITY_CLEARED_VIOLATIONS'''' AND 
           metric_column = ''''CLEARED_VIOLATIONS''''  
         )
       )
   GROUP BY time, label
   ORDER BY time ASC'';
   OPEN result_cursor_out for query_string using start_date_in, user_tz_in, rep
_tz, end_date_in, user_tz_in, rep_tz, user_tz_in, rep_tz, user_tz_in, rep_tz;
   END;
END;');

Maximum Number of Rows

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.maxNumberOfRowsAllowed"
Required	No
Default Value	"2000"
Valid Values	Any scalar numeric value.
Summary	Set the maximum number of rows retrieved for display in the table. For example, show the top 10 xyz's element would set the value to "10".

Null Data String Substitute

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.nullDataStringSubstitue"
Required	No
Default Value	""
Valid Values	A string.
Summary	A string that will be substituted for null values returned.

Split Table into Multiple Tables by Column

Attribute	Description
Parameter Name	TableRenderBean.TABLE_SPLIT_COLUMN
Parameter String	"oracle.sysman.eml.ip.render.elem.TableRender.tableSplitColumn"
Required	No
Default Value	null
Valid Values	Any valid column name.
Summary	If this parameter is set, the table will be split into separate tables with subheaders as the value in this column changes. The data should be ordered by this column.

Column Group Header

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.columnGroupHeader"n
Required	No
Default Value	null
Valid Values	Header string to use for a column group.
Summary	This parameter provides a column header string. This column group header will span columns between the columns specified in "oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStart Col"n and oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol"n. The n suffix is a numeric value starting with 1 for the first column group, sequentially ascending for subsequent column groups.

Column Group Start Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.columnGroupStartCol"n
Required	No
Default Value	null
Valid Values	Any valid column name.
Summary	Specifies the first column for a given column group. The n suffix is a numeric value starting with 1 for the first column group, sequentially ascending for subsequent column groups.

Column Group End Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.columnGroupEndCol"n
Required	No
Default Value	null
Valid Values	Any valid column name.
Summary	Specifies the last column for a given column group. The n suffix is a numeric value starting with 1 for the first column group, sequentially ascending for subsequent column groups.

Use Separate Rows for Values within a Cell

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.useSeparateRowsColumns"
Required	No
Default Value	null
Valid Values	Comma separated list of valid column names.
Summary	If this parameter is set, the delimited values of the column with the given name specified will be displayed on separate rows within a containing row cell. More than one column can be designated for this treatment by adding comma-separated column names.

Use Separate Rows as Delimiters

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.useSeparateRowsDelimiter"
Required	No
Default Value	, (comma)
Valid Values	Any string.
Summary	A character used to delimit tokens within a string.

Severity Icon in Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.severityColumn"
Required	No
Default Value	null
Valid Values	Any valid column names.
Summary	A severity icon will be substituted for valid severity values returned. To omit an icon, your result set can contain null values in this column.

Availability Status Icon in Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.availabilityStatusColumn"
Required	No
Default Value	null
Valid Values	Any valid column names.
Summary	An availability status icon will be substituted for valid values returned. To omit an icon your result set can contains null values in this column.

Render Image in Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.imageFilenameColumns"
Required	No
Default Value	null
Valid Values	Comma separated list of column names.
Summary	Optional parameter to display the given image filename in the indicated columns. Indicate for which columns the given image should be rendered. Specify a comma separated list of column names. The image filename returned should contain a relative path starting with '/images' such as '/images/xyz.gif'. Normally, a SQL decode function would be used to translate a numeric value into the appropriate image filename.

Target Type Column

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TableRender.targetTypeColumns"
Required	No
Default Value	null
Valid Values	Comma separated list of column names.
Summary	Optional parameter to indicate for which columns the value returned should be used as an internal target type to be translated into a display string for that type. Specify a comma separated list of column names.

Chart Element

The Chart Element is used to show a graphical view of query results. The queries must be made against Management Repository views.

Element Name: IPMSG_USER_CHART_FROM_SQL
Element Type: IPMSG_ANY_TARGET_TYPE

Chart Type

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.chartType"
Required	No
Default Value	"pieChart"
Valid Values	"barChart" or "lineChart" or "pieChart" or "timeSeriesChart" "timeSeriesBarChart"
Summary	Chart type to display.

Time Period

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TimePeriodParam"
Required	No
Default Value	Null
Valid Values	"0:0" for last 24 Hours "0:1" for last 7 Days "0:2" for last 31 Days
Summary	Encoded time period.

Fill

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.fill"
Required	No
Default Value	"none"
Valid Values	"none", "absolute", or "cumulative"
Summary	Indicates if a line chart should fill the area under the lines. "none": no fill under lines. "absolute": lines are identical to "none" setting but with the area under the lines filled. "cumulative": causes the values for the lines to be added or stacked, then the areas underneath the lines are filled. Use caution when using the fill attribute to ensure there is no confusion for the report user as to whether the data in the chart is cumulative or absolute.

Height

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.height"
Required	No
Default Value	"200"
Valid Values	n, where n is any String that will correctly parse to a positive integer.
Summary	Sets the display height of the chart in pixels.

Horizontal or Vertical

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.visualOrientation"
Required	No
Default Value	"horizontal"
Valid Values	"horizontal" or "vertical"
Summary	Visual orientation of the chart. This attribute is only valid with the chartType attribute set to barChart or timeSeriesChart. The attribute does not affect the pieChart.

Legend Position

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.legendPosition"
Required	No
Default Value	"east"
Valid Values	"default", "east", "south"
Summary	Specifies where the legend should be placed relative to the chart.

Is PL/SQL Statement

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql"
Required	No
Default Value	"false"
Valid Values	"true" or "false"
Summary	Set to "true" to indicate that the SQL statement is a PL/SQL statement.

SQL or PL/SQL Statement

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.sqlStatement"
Required	No
Default Value	<None>
Valid Values	Any valid SQL SELECT statement or PL/SQL block.
Summary	SQL or PL/SQL statement can optionally bind values for targets, locale information, and start/end date. The format of the statement should include a bind variable placeholders for the options to be bound. Bind Placeholders ??EMIP_BIND_RESULTS_CURSOR?? For use with PL/SQL statement to bind a return cursor containing results for display ??EMIP_BIND_TARGET_GUID?? For use with SQL or PL/SQL to bind a target GUID. ??EMIP_BIND_START_DATE?? For use with SQL or PL/SQL to bind a start date. ??EMIP_BIND_END_DATE?? For use with SQL or PL/SQL to bind an end date. ??EMIP_BIND_LOCALE_COUNTRY?? For use with SQL or PL/SQL to bind a locale country. ??EMIP_BIND_LOCALE_LANGUAGE?? For use with SQL or PL/SQL to bind a locale language There should be no semi-colon (;) appended to the end of the SQL statement unless it is a PL/SQL statement.

Stacked Bar Chart

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.stacked"
Required	No
Default Value	"false"
Valid Values	"true" or "false"
Summary	Indicates if a bar chart should be stacked.

Chart Title

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.title"
Required	No
Default Value	<None>
Valid Values
Summary	Chart title to identify chart for Americans with Disabilities Act compliance.

Width

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.width"
Required	No
Default Value	"400"
Valid Values	n, where n is any String that will correctly parse to a positive integer.
Summary	Specifies the display width of the element in pixels.

Y-Axis Label

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.yAxisLabel"
Required	No
Default Value	<None>
Valid Values	String
Summary	If this parameter is supplied, it is used as the y-axis label for charts that have an y-axis.

Slices as Percentage

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.pieShowSlicePercentLabels"
Required	No
Default Value	<None>
Valid Values	"true" or "false"
Summary	If this parameter is supplied, it controls whether each slice is labeled with a percentage value. This attribute is ignored for chartTypes other than pieChart.

Show Values in Legend

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.ChartParamController.pieValuesInLegend"
Required	No
Default Value	"value"
Valid Values	"percent", "value" or "none"
Summary	For pie charts, this parameter specifies whether values for pie slices are included in the legend along with the label for the pie slice. The default value for this attributes is "value". If specified as either "percent" or "value" then the numeric value is displayed along with the pie slice label in the form, "pie slice label (numeric value)". If "percent" is specified, then the percentage out of the total of all slice values is calculated and displayed, otherwise, the raw value of the slice is displayed. To omit a value in the legend, specify "none" as a value for this parameter. This attribute is ignored for chartTypes other than pieChart.

Metric Details Element

Classes directly involving the Metric Details report element and its parameters are located in the oracle.sysman.eml.ip.render.elem package. This element also accesses parameter-related constants defined in the oracle.sysman.emSDK.eml.EmlConstants class.

Element Name: IPMSG_METRIC_DETAILS
Element Type: IPMSG_ANY_TARGET_TYPE

Target Type

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.MetDetInternalTargetType"
Required	No
Default Value	"oracle_database"
Valid Values	Any valid internal target type name.
Summary	The type of target to be shown in the graph.

Metric Name

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.MetDetSelectedMetric"
Required	Yes
Default Value
Valid Values	Valid metric name according to target type selected.
Summary	Metric to be graphed.

Metric Column Name

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.MetDetSelectedMetricColumn"
Required	Yes
Default Value
Valid Values	Valid column name according to the metric and target type selected.
Summary	Column of metric to be graphed.

Time Period

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TimePeriodParam"
Required	No
Default Value	null
Valid Values	"0:0" for last 24 Hours "0:1" for last 7 Days "0:2" for last 31 Days
Summary	Encoded time period.

Width

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.MetDetWidth"
Required	No
Default Value	300
Valid Values	n, where n is any String that will correctly parse to a positive integer.
Summary	Width of the image in pixels.

Height

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.MetDetHeight"
Required	No
Default Value	300
Valid Values	n, where n is any String that will correctly parse to a positive integer.
Summary	Height of the image in pixels.

Legend Position

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.MetDetLegendPosition"
Required	No
Default Value
Valid Values	"south" (default), "east"
Summary	Position of the legend relative to the chart.

Text Element Parameters

The Text Element is used to display any message text you wish to provide for your report.

Element Name: IPMSG_STYLED_TEXT
Element Type: IPMSG_ANY_TARGET_TYPE

Message Text

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage"
Required	No
Default Value	"" (empty String)
Valid Values	Any message
Summary	Set the message to display in the report.

Message Style

Attribute	Description
Parameter Name	"oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass"
Required	No
Default Value	"OraInstructionText"
Valid Values	"OraInstructionText" "OraTipText"
Summary	Specifies the style class for the message text to adopt when displayed.

Link Destination

Attribute	Description
Parameter Name	"oracle.sysmn.eml.ip.render.elem.TextParamBean.textDestination"
Required	No
Default Value	None
Valid Values	Any URI.
Summary	Specifies an optional link destination for this text element.

Report-Wide Parameters

The following parameters apply to all reporting elements within the report definition.

Dynamic Time Selector

You can provide a dynamic time period selector for your report definition that allows the report user to choose a specific time period with which to view the report. The dynamic time period option is available only when viewing reports from the Reports tab, is not available on a target home page report. The time period used on a target home page report is the default time period set for the report definition. To specify this using the PL/SQL API when creating your report definition file, set the following parameters for your report definition.

Example 4-5 Dynamic Time Selector Report Definition Parameters

l_param_values := MGMT_IP_PARAM_VALUE_LIST();
    l_param_values.extend(3);

    -- this report has a time period associated with it
    l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodOption',
      'true');

    -- the user can customize the time period while viewing the report
    l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust',
      'true');

    -- set the default value to last 24 hours
    l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
      'oracle.sysman.eml.ip.render.elem.TimePeriodParam',
      '0:0');

Pass l_param values to mgmt_ip.create_report_definition as the p_parameters argument.

If you are using Table from SQL or Chart from SQL report elements, you can structure your SQL statement such that the start and end dates will be bound automatically for you by Information Publisher. You achieve this by inserting placeholders (for example, ??EMIP_BIND_START_DATE??) for the start and end date values as shown in Example 4-6.

Example 4-6 Automatic Binding of Start and End Dates

'SELECT COLUMN_LABEL, ROLLUP_TIMESTAMP, AVERAGE
        FROM MGMT$METRIC_HOURLY
        WHERE TARGET_GUID = ??EMIP_BIND_TARGET_GUID??
        AND METRIC_LABEL = ''Load''
        AND KEY_VALUE = '' ''
        AND ROLLUP_TIMESTAMP > ??EMIP_BIND_START_DATE??
        AND ROLLUP_TIMESTAMP < ??EMIP_BIND_END_DATE??
        ORDER BY ROLLUP_TIMESTAMP'

See the online help documentation for Table from SQL or Chart from SQL for detailed information.

Development Guidelines

Oracle recommends adhering to the following guidelines when defining the PL/SQL for a report definition file:

Use the PL/SQL API to create your report definition.

Report definitions should be created using the PL/SQL API rather than non-standard coding constructs. Using the PL/SQL API insulates your code from any Management Repository schema changes.
Every report definition added to a Management Plug-in must be defined as a SYSTEM report.

Each call to the create_report_definition method must pass p_system_report => 1. SYSTEM report definitions are handled differently in the Enterprise Manager console. For example, SYSTEM report definitions cannot be deleted or edited by administrators.
Specifying the report owner.

You must specify the value returned from mgmt_user.get_repository_owner as the owner for each report definition. Report definitions specifying any owner other than mgmt_user.get_repository_owner will not appear in the Reports subtab of the target home page. These report definitions will, however, appear in the Information Publisher Report Definitions page.

The component name must be set to the target type.

The component name must be set to the target type in order for Enterprise Manager to associate specific report definitions with a particular Management Plug-in. For example,

BEGIN

        -- specify the target type associated with this report
        -- in this case the target type is 'oracle_orgchart'
           l_target_type := MGMT_IP_TARGET_TYPES();
           l_target_type.extend(1);
           l_target_type(1) := 'oracle_orgchart';
           l_report_title_nlsid := 'Org breakdown by title (oracle_orgchart)<>';
           l_report_owner := mgmt_user.get_repository_owner;
        
        -- create a report definition for the report
           l_report_guid := mgmt_ip.create_report_definition (
             p_title_nlsid                => l_report_title_nlsid,
             p_description_nlsid          => 'Charts showing the breakdown by title',
             p_owner                      => l_report_owner,
             p_category_nlsid             => 'Test Org Chart Reports',
             p_sub_category_nlsid         => 'Interesting Org Data',
             p_late_binding_target_types  => l_target_type,
             p_show_table_of_contents     => 0,
             p_system_report              => 1,
             p_component_name             => l_target_type(1)
             );

When Using Chart from SQL and Table from SQL elements

If your element accepts a single non-aggregate target (only), which is the case for most Management Plug-in target types, you can take advantage of automatic time zone date adjustment built into the Chart from SQL and Table from SQL elements by setting the 'oracle.sysman.eml.ip.render.elem.adjustTimes' parameter on your element to 'true'. When this parameter is set, the start and end dates bound to your SQL query will be adjusted from the report time zone to the target time zone. Conversely, dates returned from the query will be adjusted from the target time zone to the report time zone.
If your element accepts multiple targets or aggregate targets, you are responsible for handling time zone adjustment for your date values. You can obtain the report time zone from the ??EMIP_BIND_TIMEZONE_REGION?? bind variable. In order for the report viewer to understand the dates shown, dates displayed in a report must either conform to the report time zone or explicitly display the time zone associated with each date. The following examples illustrate common use cases.
Example 4-7 Adjusting a date returned in your select statement from the time zone of a given target to the report time zone.
```
select mgmt_view_util.adjust_tz(tbl.date, tgt.timezone_region,              ??EMIP_BIND_TIMEZONE_REGION??)
from mgmt$target tgt, sometable tbl
where <your where clause here>
```
Example 4-8 Adjusting a report time period start and end dates used in the WHERE clause of your SELECT statement from the report time zone to your targets time zone
```
select <your selected columns here>
from mgmt$target tgt, sometable tbl
where
  tgt.target_guid = ??EMIP_BIND_TARGET_GUID?? and
  tbl.Mydate > MGMT_VIEW_UTIL.ADJUST_TZ(
        ??EMIP_BIND_START_DATE??,
        ??EMIP_BIND_TIMEZONE_REGION??,
        tgt.TIMEZONE_REGION) 
and
  tbl.Mydate < MGMT_VIEW_UTIL.ADJUST_TZ(
        ??EMIP_BIND_END_DATE??,
        ??EMIP_BIND_TIMEZONE_REGION??,
        tgt.TIMEZONE_REGION) 
```

Recommended Coding Practice

When calling the PL/SQL API methods, you should use named notation rather than positional notation. If you have => in your call, you are using named notation. For example,

l_report_guid := mgmt_ip.create_report_definition (
     p_title_nlsid                => 'Org breakdown by title (oracle_orgchart)',
     p_description_nlsid          => 'Charts showing the breakdown by title',
     p_owner                      => mgmt_user.get_repository_owner,
     p_category_nlsid             => 'Test Org Chart Reports',
     p_sub_category_nlsid         => 'Interesting Org Data',
     p_late_binding_target_types  => l_target_type,
     p_late_binding_multi_targets => 0,
     p_show_table_of_contents     => 0,
     p_system_report              => 1,
     p_component_name             => 'oracle_orgchart'
     );

Using named notation insulates your report definition from any code changes to future releases of the PL/SQL API and also helps make your code self-documenting.