Pentaho Report Designer and Bean Shell Scripting

I’ve been working for few months with Pentaho Report Designer, PRD from now on, I will summary in three articles some basic concepts and practices that will help you to handle data and obtain some output from the application.

The main intentions of these three articles are to explain:

  • How to use BSHScripting
  • Obtain and configure the logging output
  • Modify PDR charts by using Chart Post-Processing Script

This is not an advanced article but I will suppose for the explanations that you have already some basic knowledge of PRD. If you need help for understand any part leave a comment and I will be really glad to answer. Anyway, I will attach the final report to each article so you will be able to download it and have a closer look.

Functions and BSHScripting

The first thing we are going to do is create a table with some data that we can handle. For this purpose I have created the table shown below:

AddDataAndDataset

AddQueryForTable

You can create a table just selecting from the Data tab the Data Sets option and choosing Table as dataset. I have created for this report a table composed by Lion King characters and number of friends. We will use it as dataset for our examples.

Now that we have our data let’s imagine that we are strongly interested in calculating the average number of friends. There are several ways to do it I will present two: predefined functions and BSHScripting functions.

First method: Predefined functions

Following we will calculate the sum of number of friends, and the average number of friends for each character. This will help us to explain how functions works n PDR functions can be used for access to our data, other functions, expressions or the data source. Those are stateful they maintain the state during the report generation.

AddDataAndFunction

AddFunction

Showing data in the report is quite easy we just need to move the fields to the report layout and fill the required parameters in case they the fields have it. In the same way for show function results we just need to move the function field to the report layout. To achieve this you have to go to Data tab, left click on functions and choose Add functions…

As you might know Pentaho Report Designer provide a bunch of Functions with a predefined behaviors such as summarize data, groups or add scripts. If you didn’t know about it I really encourage you to play around with these functions.

The first function that we are going to use is the Sum. This function can be found in the Summary folder and will allow us to sum the number of friends that Lion King characters have.

We still need to configure the function main parameters. In this case we are going to modify the Required parameters:

FuncAndParameters

What have we done? First we have named the function so we can easily identify what is the main purpose. I have chosen SumFriends but any other name will be fine moreover we still need to add the field name somewhere of the field that we are interested in, in this case Friends.

Now it is time to calculate the average. How could we proceed? Of course there are several ways, we could add a new function, type count in order to count how many Lion King characters there are, and after that use a Open Formula function which allow us to divide our SumFriends our count function. If you are interested in Functions and Expressions you can find more information here.

Another option is much appropriate, is to use the predefined function Average:

TotalSum

As you can see, works and is very easy.

Second method: Beanshell function.

Beanshell function is not the best solution to accomplish our goal but this example will help us to illustrate how to use this kind of function. To use beanshell functions, just add a function BeanShell (BSH) from the Script folder.

As you can imagine the way it works is a bit different because we need to do some scripting to obtain results. After adding a name to the function we can specify the script expression, just open the Expression in Required as before and there we can begin to code.

BeanShell is a Java-like scripting language, invented by Patrick Niemeyer. It runs in the Java Runtime Environment (JRE) and uses a variation of the Java syntax, in addition to scripting commands and syntax.

As a general rule, any BSH script has to be called from the main method getValue(). You can find information about this method here. The first thing we are going to do is to define our code:

getValue(){
 int sumFriends = 278;
 return sumFriends;
}

This code will produce the result as follows:

ResultFirstSumBSH

But, wait! We didn’t use any Summarized Sum function, we just defined the total. That’s right, and of course is not the proper way to do it, if we want to handle much more data and realize more complex operations.

What we need is a way to obtain the values of the column Friends, a way to access our dataset and sum the valuesThis can be achieved by changing a bit our function:

getValue(){
 resultSet = runtime.getData();
 totalRows = resultSet.getRowCount();
 sumFriends = 0;
 for(rowIterator=0; rowIterator<totalRows;rowIterator++){
  // column 2 is our column Friends
  sumFriends += runtime.getData().getValueAt(rowIterator,2); 
 }
 return sumFriends;
}

The interesting part is to understand how we got access to the field. As summary I just looked at our resultset by accessing runtime.getData(), and using the method getRowCount(). The getData() method allow us to get data from the TableModel, and getRowCount() returns the number of rows in the model. Finally, to access the data we call the method getValueAt(rowIndex, columnIndex) which returns the cell value at columnIndex and rowIndex.

The expression runtime encapsulates all properties of the current report that one might need to call. The runtime allows the access to the DataRow, the TableModel of the current report and the ProcessingContext

The last step in order to obtain the average is quite easy we just need to divide to the total number of friends sumFriends by totalRows. Here the code:

getValue(){
 resultSet = runtime.getData();
 totalRows = resultSet.getRowCount();
 sumFriends = 0;
 for(rowIterator=0; rowIterator<totalRows;rowIterator++){
  // column 2 is our column Friends
  sumFriends += runtime.getData().getValueAt(rowIterator,2); 
 }
 return sumFriends/totalRows;
}

Here is the result:

Avg

Till here the basics about BSHScripting, next time I will explain how to obtain output from PRD that help us to debug our scripts. Please I will be really glad to read your suggestions or comments any errors or ways to improve this post and the solutions presented.

Resources

http://community.pentaho.com/javadoc/
http://javadoc.pentaho.com/reporting/
https://github.com/pentaho/pentaho-reporting
Pentaho – BeanShell Data.prpt

Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *