Introduction to Pentaho Report function and formulas

Reading Time: 3 minutes

prerequisite : basic knowledge of Pentaho

Pentaho Logo - LogoDix

Pentaho Reporting offers many capabilities and expressions that can be used all through record advent. A characteristic in Pentaho Reporting is used to calculate a computed value, whilst an expression in Pentaho Reporting is a feature whose scope is constrained to the contemporary dataset row. A feature can also keep nation, gaining access to many rows of information.

capabilities and expressions in document report creation

beginning from the report clothier user interface, capabilities and expressions live in two unique places because their scope is distinctive. features can be determined in report explorer, particularly, in the facts tab below the capabilities institution. the subsequent screenshot indicates how the file explorer seems with functions described:

Simple but mostly used common functions or Document Function

The commonplace class incorporates features that take care of web page numbering, and a popular OpenFormula characteristic that you could use to create your own custom feature.

Function NameUse of Function
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.
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 NameUse of Function
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.

Summary Functions

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

Function NameUse of Function
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.

Running Functions

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

Function NameUse of Function
SumCalculates a running total sum of the specified column.
CountCounts the items in a group or report.
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.

Chart Data Functions

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

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

Script Functions

The Script contains functions that you can directly use in type of code from a supported scripting language like

  • 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.

Formulas in Pentaho

formulation are used in lots of locations within a record for dynamic evaluation. Pentaho Reporting permits the definition of custom components functions so that builders may also amplify the functionality of the components subsystem.
OpenFormula defines the types, syntax, and semantics for calculated formulation, inclusive of many predefined features and operations, in order that formulas can be exchanged between applications and produce substantively identical outputs while recalculated with identical inputs. both closed and open source software program can put in force OpenFormula.
To define a method feature, you must implement the function interface located in the org.pentaho.reporting.libraries.formula.function package, as well as a Function Description described within the same package deal. word that this is a special feature interface as described earlier

for more details visit below links :-

Pentaho (v 8.2) formulas and function

Pentaho report design Interface