Pentaho - Functions

Each page of a report contains a Page-header area and a Page-footer area. The remaining page is available for the actual report content.

The page footer tab is used to present some attributes and functions. For example, we can print the max value of the age field of an employee in the Page-footer. The reporting engine allows these features by using functions.

Let us use the same employee table which we have used in the previous chapters. After adding all the fields into the report workspace, let us now add a function into the Page-footer tab to find out the maximum age of an employee.

Follow the steps given below to add a predefined function to your report.

Step 1 - Click the Function Button (f_x)

Take a look at the following screenshot. Here the Data tab in the structure pane includes a symbol f_x (marked as “1”). It is the add function button. Click this button to add different functions into the report.

Step 2 - Select a Particular Function

Then, you will find a dialog box with different functions segregated into different groups. To print the maximum age of an employee at the page footer, we should choose the Maximum function in the Summary group which is marked as “1” in the following screenshot. Select it and click OK.

Once you click the Ok button, the function will be added to the Functions label in the Structure Pane which is placed at the right side of the screen.

Step 3 - Define a Field Name

Once you select the added function (i.e. Maximum), you will find another pane below the structure pane containing the properties of that function.

Take a look at the following screen. The maximized box contains two pointers (Pointer 1 and Pointer 2).

Pointer 1 − Select the function in the data tab of the structure pane.
Pointer 2 − Edit the Field name in the properties section by selecting the age field from the dropdown list. It is because we have to print the maximum age of an employee.

Step 4 - Add a Function to Report Workspace

Now, the function is ready with the customized properties. Now you can use that function in your report as a page footer attribute.

Take a look at the following screenshot. Again, it contains two pointers (Pointer 1 and Pointer 2).

Pointer 1 − Select and drag the Maximum function from the Structure pane to the page footer band in the workspace, as shown in the following screenshot. Now the design of your report is ready.
Pointer 2 − Select the Preview button on the left side of the screen.

Step 5 - Check Preview

Take a look at the following screen. It shows the preview of the report. The maximum age of an employee is marked and shown in a maximized box.

Pentaho - Additional Functions

The following table provides an additional list of functions that you can use in your reports.

Common Functions
S.No.	Function Name	Purpose
1	Open Formula	Enables you to create your own custom Open Formula function using the built-in Formula Editor.
2	Page	Count the number of page used in report before rendering.
3	Total Page Count	List the total number of pages in the rendered report.
4	Page of Pages	Prints the current page number along with the total number of pages in the render report.
Report Functions
S.No.	Function Name	Purpose
1	Is Export Type	Tests whether the given export type has been selected for this report.
2	Row Banding	Alternates the background color of each item band in a group.
3	Hide Repeating	Hides equal values in a group. Only the first changed value is printed.
4	Hide Page Header and Footer	Hides the page header and footer bands when the output type is not pageable.
5	Show Page Footer	Only shows the page footer on the last page rendered in the report.
Summary Functions
S.No.	Function Name	Purpose
1	Sum	Calculates the sum of the selected numeric column.
2	Count	Counts the total number of items contained in a group. If no group is specified, all items in the entire report are counted.
3	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.
4	Group Count	Counts the total number of items in the selected groups. If no group is specified, all items in all groups are counted.
5	Minimum	Identifies the lowest or smallest value in a group.
6	Maximum	Identifies the highest or largest value in a group.
7	Sum Quotient	Performs simple division on the sum totals from two columns and returns a numeric value.
8	Sum Quotient Percent	Performs simple division on the sum totals from two columns and returns a percentage value.
9	Calculation	Stores the result of a calculation. This function is used in groups.
10	Count of pages	Counts items on a page according to the specified criteria. This value is reset to zero when a new page is reached.
11	Sum of Pages	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
S.No.	Function Name	Purpose
1	Sum	Calculates a running total sum of the specified column.
2	Count	Count the items in a group or in a report.
3	Group Count	Counts the number of groups in a report.
4	Count Distinct	Counts the distinct occurrences of a certain value in a column.
5	Average	Calculates the average value in a given column.
6	Minimum	Identifies the lowest or smallest value in a column.
7	Maximum	Identifies the highest or largest value in a column.
8	Percent of Total	Calculates the percentage value of a numeric column. The total sum is divided by the number of items counted.
Advanced Functions
S.No.	Function Name	Purpose
1	Message Format	Formats text according to the Java Message Format specification.
2	Resource Message Format	Formats text from a resource bundle according to the Java Message Format specification.
3	Lookup	Maps a string from one column to another string.
4	Indirect Lookup	Returns a value from a mapped field.
5	Resource Bundle Lookup	Performs a resource-bundle lookup using the value from the defined field as a key in the resource bundle.
6	Open Formula	Enables you to create your own custom Open Formula function using the built-in Formula Editor.