Enrich your report with sparklines

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:


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.


  • 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


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


  • 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.
This entry was posted in Basic Topic, Tech-Tips on by .

About Thomas

After working as all-hands guy and lead developer on Pentaho Reporting for over an decade, I have learned a thing or two about report generation, layouting and general BI practices. I have witnessed the remarkable growth of Pentaho Reporting from a small niche product to a enterprise class Business Intelligence product. This blog documents my own perspective on Pentaho Reporting's development process and our our steps towards upcoming releases.

One thought on “Enrich your report with sparklines

  1. sam

    Hi Thomas,

    i really appreciated for such a nice blog ….i need your assistance regarding to creating Bar spark-line chart in report .you can say tiny Bar spark line chart ..i am facing problem in that ..if u don’t mind can u please help me on that ..or if possible can u plz provide a such example om that ..it will be great help ful for me..

    Once again thanx a lot for such information..


Comments are closed.