Chart from SQL

Use the Chart from SQL report element to render a chart in a generated report using information obtained from a SQL SELECT statement.

Usage Tips

Choose the type of chart you want to generate in the Chart Type option. Choices include Pie Chart, Bar Chart, Line Chart, Time Series Line Chart and Time Series Bar Chart. The Time Series charts should be used when graphing against time, since they provide time-scaling..

Specify a SQL statement which returns the data you want to display in the chart and indicate whether the statement is a SQL statement or PL/SQL block.

Important: You are only allowed to execute SQL statements written against the published views to the Management Repository.

You can also specify the location of the legend and the size of the chart image in pixels. The SQL Statement must be a SQL or PL/SQL statement that is compatible with the chart type selected. Below is a description of various chart types and the requirements regarding what the statement should return for each type.

For a Bar and Line charts, the SQL statement should return:

“Key Column, Value Column1, Value Column2,…,Value Column N”

The Key Column is used along the x-axis as a label.
The Names of the Value Column(s) are used in the legend.
The Value Column(s) indicates the location on the y-axis.

SQL

select distinct key_value,

(select value "disk reads" from mgmt$metric_current c1

where

c1.metric_name='DiskActivity'

and c1.metric_column = 'diskActivReadsPerSec'

and c1.target_name =cur.target_name

and c1.target_type = cur.target_type

and c1.key_value =cur.key_value) "Reads/sec",

(select value "disk writes" frommgmt$metric_current c2

where c2.metric_name='DiskActivity'

and c2.metric_column = 'diskActivWritesPerSec'

and c2.target_name = cur.target_name

and c2.target_type = cur.target_type

and c2.key_value = cur.key_value) "Writes/sec"

from mgmt$metric_current cur

where

target_name ='myhost.us.oracle.com'

and target_type='host'

and metric_name = 'DiskActivity'

and metric_column in ('diskActivReadsPerSec', 'diskActivWritesPerSec')

Output Examples:

example of bar chart

example of line chart

For a Pie Chart, the SQL statement should return:

“Key Column, Value Column “

The Key Column is used in the legend.
The Value Column contains the size of the pie slice – and is also listed in parenthesis in the legend.

SQL:

select type_display_name, count(*)

from mgmt$target

group by type_display_name

order by count(*) desc

Example:

example of pie chart

For a Time Series chart (line and bar), the SQL statement should return:

“Key Column, Date Column, Value Column” (ordered by key column, date column)

The Key column is used in the legend
The Date column is used on the x-axis
The Value column indicates the location on the y-axis

SQL:

select metric_name||':'||metric_column, rollup_timestamp, average

from mgmt$metric_hourly

where target_type = 'host'

and target_name='myhost.us.oracle.com'

and metric_name = 'Network' and metric_column = 'totalRate'

and rollup_timestamp > sysdate-7

order by rollup_timestamp

Note: This example is incomplete because it does not adjust dates in the target time zone to/from dates in the time zone of the report. Proper handling of time zones is described below.

Examples:

example of time series chart

Using a PL/SQL statement:

If you have a more complicated query, or need to use logical constructs in your SQL statement, you have the option of supplying a PL/SQL block to retrieve the result set. Select the PL/SQL option if you are supplying a PL/SQL block.

The PL/SQL block should return a result set that is compatible with the chart type option selected. The columns returned by the PL/SQL cursor should match the descriptions provided above for SQL statement.

The result set should be returned in a cursor which is bound in your PL/SQL with the following bind variable placeholder: ??EMIP_BIND_RESULTS_CURSOR??.

Information Publisher will bind an output cursor in your statement where the bind variable placeholder is located. Here is an example PL/SQL statement that is used to generate a Pie Chart:

DECLARE

TYPE CURSOR_TYPE IS REF CURSOR;

result_cursor_out CURSOR_TYPE;

BEGIN

result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;

OPEN result_cursor_out for

select type_display_name "Name", count(*) "Count"

from mgmt$target

group by type_display_name

order by count(*) desc;

END;

Dynamic Parameters

You can take advantage of a dynamically supplied target and time period by including bind variable placeholders in your SQL or PL/SQL statement.

??EMIP_BIND_TARGET_GUID?? – This bind variable placeholder will bind the unique identifier of the target for this report in your SQL or PL/SQL statement.

Additionally, your SQL or PL/SQL statement can bind the time period associated with a report. The following bind variable placeholders contain information about the time period.

??EMIP_BIND_START_DATE?? – The start date of the time period.
??EMIP_BIND_END_DATE?? – The end date of the time period.
??EMIP_BIND_TIMEZONE_REGION?? – The time zone region of the time period Here is a modified version of the time series query shown above using a dynamic binding for the Host target and time period.

Any date values returned in your result set should be returned as dates in the time zone region of the time period. This will ensure that the report viewer can interpret all dates properly as the time zone of the time period will be displayed on the report.

Here is a modified version of the time series query shown above using a dynamic binding for the Host target and time period.

SQL: Using Bind Variable Placeholders

select metric_name||':'||metric_column,

mgmt_view_util.adjust_tz(rollup_timestamp, tgt.timezone_region, ??EMIP_BIND_TIMEZONE_REGION??),

average

from mgmt$metric_hourly met, mgmt$target tgt

where met.target_guid = ??EMIP_BIND_TARGET_GUID??

and met.target_guid = tgt.target_guid

and met.metric_name = 'Network'

and met.metric_column = 'totalRate'

and met.rollup_timestamp > mgmt_view_util.adjust_tz(??EMIP_BIND_START_DATE??, ??EMIP_BIND_TIMEZONE_REGION??, tgt.timezone_region)

and met.rollup_timestamp < mgmt_view_util.adjust_tz(??EMIP_BIND_END_DATE??, ??EMIP_BIND_TIMEZONE_REGION??, tgt.timezone_region)

order by met.rollup_timestamp