Sparklines are a great way to convey a lot of information in a simple and readable way. Sparklines can be used to plot changes of metrics over time. Edward Tufte uses temperatures of patients in a hospital and share prices as examples.

Bart Maertens wrote about sparklines when Pentaho Reporting 3.5 came out. At that time, he had to do a lot of SQL magic to get data into them. With the addition of the MULTIVALUEQUERY formula function in Pentaho Reporting 3.8, this process got a lot easier.

Pentaho Reporting comes with three Sparkline type elements. Sparkline elements are minimalistic, they do not have any axis labels or legends.

1. Line Sparks: A small line chart that shows the change of values over time. Line charts are good to visualize trends over time. The last data point can be highlighted for better readability.
2. Bar Sparks: A small bar chart that allows to compare absolute values over a time scale. Bar charts should be used when you need to concentrate on the ratio between values instead of the general trends.
3. Pie Sparks: A small pie chart that visualizes a single value in relation to a total value.

Now how do you add sparklines to a report created with the Pentaho Report Designer?

Getting the data

Except for Line and Bar charts, you will need a array of numeric values to display the data. You can construct arrays from different values of the same data row with a OpenFormula function like this:

=NORMALIZEARRAY({[Value1] | [Value2] | [Value3]})

The NORMALIZEARRAY formula function converts a internal OpenFormula array into a valid Java-Array that can be used by other components.

Alternatively, you can get a array of values from a data-source by using the “MULTIVALUEQUERY” formula function.

With a SQL datasource using the sample data you can use a query like this to get the sales numbers for each year:

SELECT
     SUM("ORDERFACT"."TOTALPRICE") AS "Value",
     "ORDERFACT"."YEAR_ID",
     "ORDERFACT"."PRODUCTCODE"
FROM
     "ORDERFACT"
WHERE 
          "ORDERFACT"."PRODUCTCODE" = ${PRODUCTCODE}
GROUP BY
     "ORDERFACT"."PRODUCTCODE",
     "ORDERFACT"."YEAR_ID"

This creates a result-set similar to this:

Value                   YEAR_ID PRODUCTCODE
-------------------------------------------
57363.100000000006 2003 S18_1749
19656.739999999998 2003 S18_2248
33451.850000000006 2003 S18_4409
12768.21 2003 S24_3969
39983.85999999999 2003 S18_2325
52395.560000000005 2003 S18_2795
11024.69 2003 S24_1937
14770.83 2003 S24_2022
38895.38 2003 S18_1342
22257.399999999998 2003 S18_1367
72913.26999999999 2003 S10_1949
40103.54 2003 S10_4962
46714.39 2003 S12_1666

You can then reference this data from a formula with the MULTIVALUEQUERY formula function.

=MULTIVALUEQUERY(“SparklineQuery”, “Value”)

Pie sparks simply require a single value in the range of 0 (zero) to 1 (one). Zero represents an empty pie (0% of the total), 0.5 a pie filled to the half (50% of the total), and 1 represents a fully filled pie (100% of the total).

Formatting the sparklines

Sparklines get formatted via the styles properties.

Bar-Sparks:

• last-color: The color of the last bar
• high-color: The color of the bar with the largest value.
• text-color: The color of all other bars

Line-Sparks:

• last-color: The color of the marker dot at the end of the spark
• text-color: The color of the line

Pie-Spark:

• low-color: The color of the slice if the value is below the “low-slice” threshold.
• medium-color: The color of the slice if the value is below the “medium-slice” threshold.
• high-color: The color of the slice if the value is below the “high-slice” threshold.
• text-color: The color of the pie that is not filled by the slice.