Function reference
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 Name | Purpose |
Open Formula | Enables 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. |
Page | Counts the number of pages rendered thus far in a report. |
Total Page Count | Lists the total number of pages in the rendered report. |
Page of Pages | Prints 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 Name | Purpose |
Is Export Type | Tests whether the given export type has been selected for this report. |
Row Banding | Alternates the background color of each item band in a group. |
Hide Repeating | Hides equal values in a group. Only the first changed value is printed. |
Hide Page Header & Footer | Hides the page header and footer bands when the output type is not pageable. |
Show Page Footer | Only 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 Name | Purpose |
Sum | Calculates the sum of the selected numeric column. This produces a global total. |
Count | Counts the total number of items contained in a group. If no group is specified, all items in the entire report are counted. |
Count by Page | Counts 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 Count | Counts the total number of items in the selected groups. If no group is specified, all items in all groups are counted. |
Minimum | Identifies the lowest or smallest value in a group. |
Maximum | Identifies the highest or largest value in a group. |
Sum Quotient | Performs simple division on the sum totals from two columns and returns a numeric value. |
Sum Quotient Percent | Performs simple division on the sum totals from two columns and returns a percentage value. |
Calculation | Stores 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 Page | Counts items on a page according to the specified criteria. This value is reset to zero when a new page is reached. |
Sum For Page | Adds 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 Name | Purpose |
Sum | Calculates a running total sum of the specified column. |
Count | Counts the items in a group or report. |
Group Count | Counts the number of groups in a report. |
Count Distinct | Counts the distinct occurrences of a certain value in a column. |
Average | Calculates the average value in a given column. |
Minimum | Identifies the lowest or smallest value in a column. |
Maximum | Identifies the highest or largest value in a column. |
Percent of Total | Calculates 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 Name | Purpose |
Message Format | Formats text according to the Java Message Format specification. |
Resource Message Format | Formats text from a resource bundle according to the Java Message Format specification. |
Lookup | Maps 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 Lookup | Returns 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 Lookup | Performs 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 Formula | Enables 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 Name | Purpose |
CategorySet Data Collector | See CategorySet |
Pie DataSet Collector | See PieSet |
Pivoting CategorySet Data Collector | See PivotCategorySet |
TimeSeries Collector | See TimeSeries |
XY-Series Collector | See XYSeries |
XYZ-Series Collector | See XYZSeries |
Image functions
The Image category contains functions.
Function Name | Purpose |
Area Chart | See Area |
Bar Chart | See Bar |
Bar Line Chart | See Bar Line Combination |
BarCode | A simple barcode chart available through the Report Designer palette |
Bubble Chart | See Bubble |
Extended XY Line Chart | See XY Extended Line (XY Step, XY StepArea, XY Difference) |
Line Chart | See Line |
Multi Pie Chart | See Multi-Pie |
Pie Chart | See Pie |
Radar Chart | See Radar |
Ring Chart | See Ring |
Scatter Plot Chart | See Scatter Plot |
Sparkline | Creates a Sparkline chart element. See Create a Sparkline Chart for more details. |
Survey Scale | A sliding scale chart element. |
Waterfall Chart | See Waterfall |
XY Area Chart | See XY Area |
XY Bar Chart | See XY Bar |
XY Line Chart | See 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.