Pentaho – Using Formulas in Our Reports

Exclusive offer: get 50% off this eBook here
Pentaho 5.0 Reporting by Example: Beginner’s Guide

Pentaho 5.0 Reporting by Example: Beginner’s Guide — Save 50%

Create high-quality, professional, standard reports using today's most popular open source reporting tool with this book and ebook

£18.99    £9.50
by Dario R. Bernabeu Mariano García Mattío | August 2013 | Beginner's Guides Open Source

In this article, by Mariano Garcia Mattio and Dario R. Bernabeu, authors of the book Penatho 5.0 Learning by Example, we will talk about formulas. We will explain in detail how to create them and use them. We will use a practical example to guide us as we explain how to work with formulas, creating general use formulas that we can use as an object and specific-use formulas that we can apply to our objects' styles and attributes. We will see the full potential that formulas offer in our reports, and we will create formulas that can be manipulated by the parameters that end users select.

By the end of the practical example, we will see how the combination of formulas and parameters opens up new horizons for the creation and personalization of reports and allows us great flexibility in design.

In this article we will do the following:

  • Create a copy of the previous report, adapt to its layout, and give our parameters default values
  • Create a formula that makes a row-by-row calculation and later add it to the Details section
  • Configure the background color of one of our report's objects using a formula
  • Create two new parameters so that the end user can choose the evaluation criteria of this formula.

(For more resources related to this topic, see here.)

At the end of the article, we propose that you make some modifications to the report created in this article.

Starting practice

In this article, we will create a copy of the report, then we will do the necessary changes in its layout; the final result is as follows:

As we can observe in the previous screenshot, the rectangle that is to the left of each title changes color. We'll see how to do this, and much more, shortly.

Time for action – making a copy of the previous report

In this article, we will use an already created report. To do so, we will open it and save it with the name 09_Using_Formulas.prpt. Then we will modify its layout to fit this article. Finally, we will establish default values for our parameters. The steps for making a copy of the previous report are as follows:

  1. We open the report 07_Adding_Parameters.prpt that we created. Next, we create a copy by going to File | Save As... and saving it with the name 09_Using_Formulas.prpt.
  2. We will modify our report so that it looks like the following screenshot:

  3. As you can see, we have just added a rectangle in the Details section, a label (Total) in the Details Header section, and we have modified the name of the label found in the Report Header section. To easily differentiate this report from the one used previously, we have also modified its colors to grayscale. Later in this article, we will make the color of the rectangle vary according to the formula, so itis important that the rest of the report does not have too many colors so the result are easy for the end user to see.
  4. We will establish default values in our parameters so we can preview the report without delays caused by having to choose the values for ratings, year, and month.
  5. We go to the Data tab, select the SelectRating parameter, right-click on it, and choose the Edit Parameter... option:

  6. In Default Value, we type the value [G]:

  7. Next, we click on OK to continue. We should do something similar for SelectYear and SelectMonth:
    • For SelectYear, the Default Value will be 2005.
    • For SelectMonth, the Default Value will be 5. Remember that the selector shows the names of the months, but internally the months' numbers are used; so, 5 represents May.

What just happened?

We created a copy of the report 07_Adding_Parameters.prpt and saved it with the name 09_Using_Formulas.prpt. We changed the layout of the report, adding new objects and changing the colors. Then we established default values for the parameters SelectRating, SelectYear, and SelectMonth.

Formulas

To manage formulas, PRD implements the open standard OpenFormula. According to OpenFormula's specifications:

"OpenFormula is an open format for exchanging recalculated formulas between office application implementations, particularly for spreadsheets. OpenFormula defines the types, syntax, and semantics for calculated formulas, including many predefined functions and operations, so that formulas can be exchanged between applications and produce substantively equal outputs when recalculated with equal inputs. Both closed and open source software can implement OpenFormula."

For more information on OpenFormula, refer to the following links:

Formulas are used for greatly varied purposes, and their use depends on the result one wants to obtain. Formulas let us carry out simple and complex calculations based on fixed and variable values and include predefined functions that let us work with text, databases, date and time, let us make calculations, and also include general information functions and user-defined functions. They also use logical operators (AND, OR, and so on) and comparative operators (>, <, and so on).

Creating formulas

There are two ways to create formulas:

  • By creating a new function and by going to Common | Open Formula
  • By pressing the button in a section's / an object's Style or Attributes tab, or to configure some feature

In the report we are creating in this article, we will create formulas using both methods.

Using the first method, general-use formulas can be created. That is, the result will be an object that can either be included directly in our report or used as a value in another function, style, or attribute. We can create objects that make calculations at a general level to be included in sections that include Report Header, Group Footer, and so on, or we can make calculations to be included in the Details section. In this last case, the formula will make its calculation row by row. With this last example, we can make an important differentiation with respect to aggregate functions as they usually can only calculate totals and subtotals.

Using the second method, we create specific-use functions that affect the value of the style or attribute of an individual object. The way to use these functions is simple. Just choose the value you want to modify in the Style and Attributes tabs and click on the button that appears on their right. In this way, you can create formulas that dynamically assign values to an object's color, position, width, length, format, visibility, and so on. Using this technique, stoplights can be created by assigning different values to an object according to a calculation, progress bars can be created by changing an object's length, and dynamic images can be placed in the report using the result of a formula to calculate the image's path.

As we have seen in the examples, using formulas in our reports gives us great flexibility in applying styles and attributes to objects and to the report itself, as well as the possibility of creating our own objects based on complex calculations. By using formulas correctly, you will be able to give life to your reports and adapt them to changing contexts. For example, depending on which user executes the report, a certain image can appear in the Report Header section, or graphics and subreports can be hidden if the user does not have sufficient permissions.

The formula editor

The formula editor has a very intuitive and easy-to-use UI that in addition to guiding us in creating formulas, tells us, whenever possible, the value that the formula will return. In the following screenshot, you can see the formula editor:

We will explain its layout with an example. Let's suppose that we added a new label and we want to create a formula that returns the value of Attributes.Value. For this purpose, we do the following:

  • Select the option to the right of Attributes.Value. This will open the formula editor. In the upper-left corner, there is a selector where we can specify the category of functions that we want to see. Below this, we find a list of the functions that we can use to create our own formulas. In the lower-left section, we can see more information about the selected function; that is, the type of value that it will return and a general description:

  • We choose the CONCATENATE function by double-clicking on it, and in the lower-right section, we can see the formula (Formula:) that we will use. We type in =CONCATENATE(Any), and an assistant will open in the upper-right section that will guide us in entering the values we want to concatenate.

    We could complete the CONCATENATE function by adding some fixed values and some variables; take the following example:

If there is an error in the text of the formula, text will appear to warn us. Otherwise, the formula editor will try to show us the result that our formula will return. When it is not possible to visualize the result that a formula will return, this is usually because the values used are calculated during the execution of the report.

Formulas should always begin with the = sign.

Initially, one tends to use the help that the formula editor provides, but later, with more practice, it will become evident that it is much faster to type the formula directly. Also, if you need to enter complex formulas or add various functions with logical operators, the formula editor will not be of use.

Pentaho 5.0 Reporting by Example: Beginner’s Guide Create high-quality, professional, standard reports using today's most popular open source reporting tool with this book and ebook
Published: August 2013
eBook Price: £18.99
Book Price: £30.99
See more
Select your format and quantity:

Time for action – creating a new formula

  1. We will use the formula editor to create a new formula that multiplies the Amount and Quantity. Then we will place it in the Details section so it makes its calculation row by row. Finally, we will change the type of object for the Amount and change the format in which the numbers are presented. We will create a formula to make a calculation based on the Amount and Quantity of each of the rows and place this value under the label Total. We choose the Data tab, right-click on Functions, and choose the Add Functions... option:

  2. In the new window, we go to Common | Open Formula and click on OK.

  3. We will configure the formula we just created. First, we change its name:
    • Function Name = FormulaTotal
  4. Next, we specify the calculation that this formula will make. We press the button to the right of the formula. This will open the formula editor.
  5. In the lower-right portion of the formula editor, we see only the = sign where it says Formula:
  6. The calculation that we want to make is a multiplication of the sum_amount and count_rental fields. To choose the sum_amount field, we press the button and choose [sum_amount].

    Let's see how our formula is coming along:

  7. Next, we add the multiplication sign by either typing it from the keyboard or pressing the button.
  8. Finally, we press the button again, but this time we choose count_rental.

    Our formula should look like this:

    Done! We have created our first formula.

  9. When you add a field to the formula, use the following nomenclature: [field_name]

    Now we press OK to continue. We add the recently created formula ( FormulaTotal ) to the Details section under the Total label:

    If we choose FormulaTotal and look at Attributes.type, we see that it is a Text-field type object; this means we cannot apply the same format to it as we did in the previous article with sum_amount.

    Remember that what we did with sum_amount was configure its Attributes.format attribute with the value $ #,##0.00;($ #,##0.00). We were able to do this because sum_amount is a Number-field type object.

  10. Next, we will convert the FormulaTotal object to Number-field. To do so, we select FormulaTotal, and in the upper menu, we choose number-field by going to Format | Morph |.
  11. Next, we configure the following:
    • Attributes.format = $ #,##0.00;($ #,##0.00)

    Another way to do this is by adding a Number-field object to our report and assigning the value [FormulaTotal] to its Attributes.field attribute, and then configuring the desired format in Attributes.format.

What just happened?

We created a new formula. We did it by creating a function and then by going to Common | Open Formula. Next, we used the formula editor UI to create an expression that multiplies the fields Amount and Quantity. Finally, we added our formula to the Details section, so it will make its calculation row by row.

Time for action – styles with formulas

We will configure the rectangle's Style.bg-color property, which we have previously placed in the Details section, so that its color will be defined based on a row-by-row evaluation of the sum_amount field. We will use the formula editor and the IF function to do so. Next, we will create two parameters so that the final user can establish the conditions based on which the rectangle will be colored. What we will do now is create a formula so that the background color of the rectangle we added earlier changes according to certain values.

  1. We select Rectangle, and in the Style tab, press the button found on the right of bg-color:

  2. This will open the formula editor. In Formula:, we type the following:

    =IF([sum_amount]<4;"#990000";IF([sum_
    amount]<6;"#cccc00";"#66cc00"))

  3. Next, we click on OK to continue.

We will explain each part of this calculation, but first we will talk a bit about the function IF. The IF function receives the following three parameters that are separated by semicolons (;):

  • The first is a logical expression that will return a true or false value. A comparison is often placed here, for example [sum_amount]<4, where the result of the comparison will return true if [sum_amount] is less than (<) 4, else it will return false.
  • The second value is what the function will return when the first value is true.
  • The third value is what the function will return when the first value is false.

So the IF function could be explained as follows:

  • If sum_amount is less than 4 then the color will be red (#990000); else evaluate what to do according to the second IF function)

And the second IF function can be explained as follows:

  • IF([sum_amount] is less than 6?; color yellow (#cccc00); color green ( #66cc00)

We can see how our report is coming along by previewing the following screenshot:

The limit values that we have chosen in making the previous formula are totally arbitrary. What we will do now is create two parameters so that the end user can define the value of these limits.

The first parameter will collect the value that will be used to divide the bottom range from the middle, and the second parameter will divide the middle from the top.

Next, we will create the first parameter:

  • Name = SelectFirstThreshold
  • Label = First Threshold:
  • Value Type = Number
  • Default Value = 4
  • Display Type = Text Box

Now we will create the second parameter:

  • Name = SelectSecondThreshold
  • Label = Second Threshold:
  • Value Type = Number
  • Default Value = 6
  • Display Type = Text Box

In the formula that we have just applied to Style.bg-color of the rectangle, we will modify the calculation as follows:

=IF([sum_amount]<[SelectFirstThreshold];"#990000";IF([sum_amount]
<[SelectSecondThreshold];"#cccc00";"#66cc00"))

If we preview the report and modify the values of the parameters SelectFirstThreshold and SelectSecondThreshold, we will see how the background color of the rectangle varies.

Lastly, we will change the order the parameters appear in so that the two new parameters are in the top part of the list of selectors when we execute our report.

We go to the Data tab, select the SelectFirstThreshold parameter, right-click on it, and choose the Send Forward option. This will make the chosen parameter move up one level in the hierarchy. We should repeat this procedure until SelectFirstThreshold is first in the list.

Then we will use the same procedure to position SelectSecondThreshold second in the list.

If we preview our report, we can see the changes we have made:

What just happened?

We created a new formula to calculate our rectangle's Style.bg-color property according to the value of the sum_amount field in each row. We used the IF function to do so, which was explained in detail through the example. Later we created the parameters SelectFirstThreshold and SelectSecondThreshold so that the end user is able to choose the values of the limits used by the formula to calculate the color of our rectangle. Lastly, we placed these two new parameters at the top of the selector list.

Have a go hero

Next, to sharpen the skills we have learned, we will modify the report created in this art and adapt it to meet new requirements.

The report that we propose, which you have to create, will have an image with a stoplight in the Group Footer section, and the following screenshot will be shown at the end of each group, in this case at the end of the analysis of the films of each rating. Let's see the last page of the rating G:

The steps you should follow, broadly speaking, are as follows:

  1. Create a copy of the 09_Using_Formulas.prpt report and save it with the name 10_Using_Formulas_Plus.prpt.
  2. Create a function of the type Running | Average (Running) and configure it as follows:
    • Function Name = AvgAmount
    • Field Name = sum_amount
    • Reset On Group Name = RatingGroup

If you are using Windows, you should use the backslash instead of the forward slash to separate folders; that is, you should use \ instead of /.

This formula will insert the path corresponding to an image based on a comparison made to the average of sum_amount (function AvgAmount). This formula can be explained as follows:

  • If [AvgAmount] is less than [SelectFirstThreshold], then insert a red stoplight (traffic_red.png); else if [sum_amount] is less than [SelectSecondThreshold] , then insert a yellow stoplight ( traffic_yellow.png ), and insert a green stoplight ( traffic_green.png)

Summary

We opened a report created in the previous article, we saved it with another name, and we modified its layout to fit the necessities of the new exercise. We defined the default values for the parameters SelectRating, SelectYear, and SelectMonth.

We explained what formulas are, what they are used for, and the ways to use them. We also talked about the formula editor UI and used it in a practical example.

We created a formula named FormulaTotal and used the formula editor to enter the calculation that it will make. Then we included FormulaTotal in our report in the Details section.

We configured Attributes.bg-color for a rectangle that we added to our report so that its background color is determined by a formula.

Next, we created two parameters (SelectFirstThreshold and SelectSecondThreshold) so that the final user can input values that will intervene in the calculation of the formula that defines the background color of our rectangle.

Finally, so you could sharpen the skills learned, we proposed that you modify the report created in this article to show a stoplight.

Resources for Article:


Further resources on this subject:


Pentaho 5.0 Reporting by Example: Beginner’s Guide Create high-quality, professional, standard reports using today's most popular open source reporting tool with this book and ebook
Published: August 2013
eBook Price: £18.99
Book Price: £30.99
See more
Select your format and quantity:

About the Author :


Dario R. Bernabeu

Dario R. Bernabeu is a systems engineer at the Instituto Universitario Aeronáutico (University Aeronautic Institute) IUA. He is the co-founder of eGluBI (www.eglubi.com.ar). He specializes in development and implementation of OSBI solutions (Open Source Business Intelligence), project management, analysis of requirements/needs, deployment and configuration of BI solutions, design of data integration processes, data warehouse modelling, design of multidimensional cubes and business models, development of ad hoc reports, advanced reports, interactive analysis, dashboards, and so on. A teacher, researcher, geek, and open source software enthusiast, his most notable publication is "Data Warehousing: Research and Concept Systematization – HEFESTO: Methodology for the Construction of a DW". Being the coordinator of the social network Open BI Network (www.redopenbi.com), he makes many contributions to various forums, wikis, blogs, and so on. You can find his blog site at http://tgx-hefesto.blogspot.com/.

Mariano García Mattío

Mariano García Mattío is a systems engineer for the IUA and specialist in distributed systems and services for the Facultad de Matemática Astronomía y Física (Faculty of Mathematics Astronomy and Physics) FaMAF UNC. He is an associate professor of: databases 1, databases 2, and advance database systems at the IUA, school of engineering; database engines at the IUA, school of administration; object-oriented programming paradigm, and distributed systems at the IUA's master in embedded systems. He is the teacher in charge of assignments for applied databases at the UCC. Also, Mariano is the co-director of the research project on new information and communication technologies at the UCC and co-director of the research project on networks monitoring and communication systems at the IUA. He is also a member of the Virtual Laboratories research project at the IUA and co-founder of eGluBI. He is the coordinator of the social network Open BI Network. He specializes in Java SE and Java EE technologies, node.js, administration and design of databases, and OSBI.

His blog site is http://jmagm.blogspot.com/.

Books From Packt


Pentaho Data Integration 4 Cookbook
Pentaho Data Integration 4 Cookbook

Instant Pentaho Data Integration Kitchen [Instant]
Instant Pentaho Data Integration Kitchen [Instant]

Pentaho Reporting 3.5 for Java Developers
Pentaho Reporting 3.5 for Java Developers

Pentaho 3.2 Data Integration: Beginner's Guide
Pentaho 3.2 Data Integration: Beginner's Guide

Pentaho Data Integration Beginner's Guide, Second Edition
Pentaho Data Integration Beginner's Guide, Second Edition

IBM Cognos Business Intelligence 10.1 Dashboarding Cookbook
IBM Cognos Business Intelligence 10.1 Dashboarding Cookbook

OSWorkflow: A guide for Java developers and architects to integrating open-source Business Process Management
OSWorkflow: A guide for Java developers and architects to integrating open-source Business Process Management

Java EE Development with Eclipse
Java EE Development with Eclipse


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software