Skip to main content
Pentaho Documentation

Function reference

Parent aricle

You can click the fx button in the upper left corner of the pane to add a new function. Delete a function by clicking it, then pressing the Delete key, or by right-clicking it and selecting Delete from the context menu.

Every function available in Report Designer is defined below and categorized according to the group it belongs to.

Common functions

The Common category contains functions that handle page numbering, and a generic OpenFormula feature that you can use to create your own custom function.

Function NamePurpose
Open FormulaEnables you to create your own custom OpenFormula function using the built-in Formula Editor. This function will run according to its placement in the report. If you need a custom function to run before all other report actions, use the Open Formula function in the Advanced category instead.
PageCounts the number of pages rendered thus far in a report.
Total Page CountLists the total number of pages in the rendered report.
Page of PagesPrints the current page number in comparison to the total number of pages in the rendered report.

Report functions

The Report category contains functions that modify the layout of the rendered report.

Function NamePurpose
Is Export TypeTests whether the given export type has been selected for this report.
Row BandingAlternates the background color of each item band in a group.
Hide RepeatingHides equal values in a group. Only the first changed value is printed.
Hide Page Header & FooterHides the page header and footer bands when the output type is not pageable.
Show Page FooterOnly shows the page footer on the last page rendered in the report.

Summary functions

The Summary category contains mathematical functions that count, add, and divide report data in groups.

Function NamePurpose
SumCalculates the sum of the selected numeric column. This produces a global total.
CountCounts the total number of items contained in a group. If no group is specified, all items in the entire report are counted.
Count by PageCounts the total number of items contained in a group on one rendered page. If no group is specified, all items on the entire page are counted.
Group CountCounts the total number of items in the selected groups. If no group is specified, all items in all groups are counted.
MinimumIdentifies the lowest or smallest value in a group.
MaximumIdentifies the highest or largest value in a group.
Sum QuotientPerforms simple division on the sum totals from two columns and returns a numeric value.
Sum Quotient PercentPerforms simple division on the sum totals from two columns and returns a percentage value.
CalculationStores the result of a calculation. This function can be used to convert a group of Running functions into a single total Summary function.
Count For PageCounts items on a page according to the specified criteria. This value is reset to zero when a new page is reached.
Sum For PageAdds all of the specified items on one page and produces a total. This value is reset to zero when a new page is reached.

Running functions

The Running category contains mathematical functions that deal with running totals, as opposed to global or summary totals.

Function NamePurpose
SumCalculates a running total sum of the specified column.
CountCounts the items in a group or report.
Group CountCounts the number of groups in a report.
Count DistinctCounts the distinct occurrences of a certain value in a column.
AverageCalculates the average value in a given column.
MinimumIdentifies the lowest or smallest value in a column.
MaximumIdentifies the highest or largest value in a column.
Percent of TotalCalculates the percentage value of a numeric column. The total sum is divided by the number of items counted.

Advanced functions

The Advanced category contains functions that deal with developer-centric actions.

Function NamePurpose
Message FormatFormats text according to the Java Message Format specification.
Resource Message FormatFormats text from a resource bundle according to the Java Message Format specification.
LookupMaps a string from one column to another string. The possible mappings are given as (key, text) pairs. If the string from the column is null or matches none of the defined keys, a fallback value is returned.
Indirect LookupReturns a value from a mapped field. The field's value is used as a key to the field-mapping. The expression maps the value to a new column name and returns the value read from this column.
Resource Bundle LookupPerforms a resource-bundle lookup using the value from the defined field as a key in the resource bundle. This expression behaves like a resource field.
Open FormulaEnables you to create your own custom OpenFormula function using the built-in Formula Editor. This function will run before any other action in the report.

Chart Data functions

The Chart Data category contains functions that create datasets for JFreeChart elements.

Function NamePurpose
CategorySet Data CollectorSee CategorySet
Pie DataSet CollectorSee PieSet
Pivoting CategorySet Data CollectorSee PivotCategorySet
TimeSeries CollectorSee TimeSeries
XY-Series CollectorSee XYSeries
XYZ-Series CollectorSee XYZSeries

Image functions

The Image category contains functions.

Function NamePurpose
Area ChartSee Area
Bar ChartSee Bar
Bar Line ChartSee Bar Line Combination
BarCodeA simple barcode chart available through the Report Designer palette
Bubble ChartSee Bubble
Extended XY Line ChartSee XY Extended Line (XY Step, XY StepArea, XY Difference)
Line ChartSee Line
Multi Pie ChartSee Multi-Pie
Pie ChartSee Pie
Radar ChartSee Radar
Ring ChartSee Ring
Scatter Plot ChartSee Scatter Plot
SparklineCreates a Sparkline chart element. See Create a Sparkline Chart for more details.
Survey ScaleA sliding scale chart element.
Waterfall ChartSee Waterfall
XY Area ChartSee XY Area
XY Bar ChartSee XY Bar
XY Line ChartSee XY Line

Script functions

The Script category contains functions that enable you to directly type in code from a supported scripting language.

  • Bean-Scripting Framework (BSF)
  • Bean-Scripting Host (BSH)
  • JavaScript
  • Single Value Query

The only unique object Pentaho offers in Report Designer for a scripting language is getValue for the Bean-Scripting Framework, which retrieves the current record or row, as shown below:

Object getValue()
  {
  	Object value = dataRow.get("RegionVariance");
      if (value instanceof Number == false)
      {
      	return Boolean.FALSE;
      }
      Number number = (Number) value;
      if (number.doubleValue() < 0)
      {
      return Boolean.TRUE;
      }
      return Boolean.FALSE;
  }

Deprecated functions

The Deprecated category contains functions that had to be included in this version of Report Designer to provide backwards compatibility for files created with older Report Designer versions. You should never use any of these functions in new reports; there are no Deprecated functions that are not more sensibly implemented in other functions in other categories.