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 :
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 seqIn the Formatting window, select from the Display report as drop-down list.
For the Caption field, select from the drop-down list. Color sequence formatter must be selected on the 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 , enter a report name and click .
When you run this report, the output will look something like the following: