Skip Headers
Oracle® Enterprise Manager Extensibility
10g Release 2 (10.2) for Windows or UNIX

Part Number B16246-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

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:


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 (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 follows
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 follows
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 follows
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:

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

  2. Create a test report interactively from the Enterprise Manager console.

  3. 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:

  • 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

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

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.add_element_to_report_def

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:

When Using Chart from SQL and Table from SQL elements

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.