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:
. Sparklines are embedded in a table cell.
. The series of values can be derived from: start_date between ${startDate} AND ${endDate} in the query.
. 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 and .
In the Query window, enter the following select statement and click :
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,2In the Formatting window, select all the fields to be displayed and display the report as a .
In the Formatting window, for failed_count, select the formatter and for the failed_count_area, select the formatter.
Click , enter a report name and click .The output will look something like this:
