Example of a Sparkline Query in a SQL Template

A sparkline presentation of the data can be added as a column to a tabular report to plot a series of values. Sparklines enable an at-a-glance view of data spikes that may indicate an issue that requires attention.

The basic requirements for a sparkline chart in a SQL template include:

Table template. Sparklines are embedded in a table cell.
Data over time. The series of values can be derived from: start_date between ${startDate} AND ${endDate} in the query.
Comma-separated list of string values. Use the pipelined function to achieve this.
See collectString.

The following example graphs a series of client failure values as a sparkline in a tabular report.

In the SQL Template Designer, check Date Range and Host Groups and Client Scope.

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

with spark as (
select trunc(start_date), client_id, client_name, product_type_name,count(job_id) failed_count
FROM apt_v_job
WHERE client_id in(${hosts})
AND start_date between ${startDate} AND ${endDate}
AND summary_status = 2
group by client_id, client_name, product_type_name, trunc(start_date)
)
select display_name, product_type_name, rtd.collectString(cast(collect(TO_CHAR(failed_count)) as StringListType), ', ') failed_count, rtd.collectString(cast(collect(TO_CHAR(failed_count)) as StringListType), ', ') failed_count_area
from apt_v_server h, spark s
where h.server_id = s.client_id
group by display_name, product_type_name
order by 1,2

In the Formatting window, select all the fields to be displayed and display the report as a Table.
In the Formatting window, for failed_count, select the Column Sparkline formatter and for the failed_count_area, select the Area Sparkline formatter.
Click Next, enter a report name and click Finish.The output will look something like this:

Example of a Sparkline Query in a SQL Template

Feedback

Feedback