Example of a Pie Chart SQL Template: Overall Job Status Summary

The following example creates a pie chart that represents the various status' for a job - such as, success, warning and failure - as sectors (slices) in a pie chart.

In the SQL Template Designer, in the Template Designer window, choose Date range and Host groups and client scope.

In the Query window, enter the following select statement and click Validate Query:

WITH t1 AS (
SELECT
0 seq, 'Success' status, count(job_id) status_count
FROM apt_v_job j
WHERE j.client_id IN (${hosts})
AND j.start_date BETWEEN  ${startDate} AND ${endDate}
AND j.summary_status=0
UNION
SELECT
1 seq, 'Partial' status, count(job_id) status_count
FROM apt_v_job j
WHERE j.client_id IN (${hosts})
AND j.start_date BETWEEN  ${startDate} AND ${endDate}
AND j.summary_status=1
UNION
SELECT
2 seq, 'Failed' status, count(job_id) status_count
FROM apt_v_job j
WHERE j.client_id IN (${hosts})
AND j.start_date BETWEEN  ${startDate} AND ${endDate}
AND j.summary_status=2
)
SELECT status, status_count 
FROM t1
ORDER BY seq

In the Formatting window, select Pie Chart from the Display report as drop-down list.
For the Caption field, select status from the drop-down list. Color sequence formatter must be selected on the Caption field.
Select all the fields to be displayed. For a pie chart, you need at least one field to be the caption and another field to be the sector.
Assign custom colors for your pie sectors. Specify the order and first few colors for pie chart sectors by choosing the Color Sequence as the Formatter. Enter the color in the associated Pattern field. Choose from Red, Yellow, Green, Blue, Black, White or Grey. For example, you can make a chart which depicts Success as Green, Partial as Yellow, and Failed as Red.
Color sequence formatter must be selected on the Caption field. The color values are case-insensitive. If a non-supported color is entered, that color will be ignored. If more sections are present in the pie chart than the colors entered, a random color will be assigned to the additional sections.
For a pie chart, you need at least one field to be the caption and another field to be the sector.
Click Next, enter a report name and click Finish.
When you run this report, the output will look something like the following:

Example of a Pie Chart SQL Template: Overall Job Status Summary

Feedback

Feedback