Reader small image

You're reading from  Pentaho 5.0 Reporting by Example: Beginner's Guide

Product typeBook
Published inAug 2013
PublisherPackt
ISBN-139781782162247
Edition1st Edition
Tools
Right arrow
Author (1)
Right arrow

Chapter 8. Using Formulas in Our Reports

In this chapter, we will talk about formulas. We will explain in detail how to create them and use them. As in previous chapters, 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 chapter 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...

Starting practice


In this chapter, we will create a copy of the report created in the previous chapter, 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 chapter, we will use the report we created earlier. 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 chapter. 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 in the previous chapter. 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 in the previous chapter, we have also modified its colors to grayscale. Later in this chapter, we will make the color...

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

Formulas are used for greatly varied purposes, and their use depends on the result...

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

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

Summary


We opened a report created in the previous chapter, 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,...

lock icon
The rest of the chapter is locked
You have been reading a chapter from
Pentaho 5.0 Reporting by Example: Beginner's Guide
Published in: Aug 2013Publisher: PacktISBN-13: 9781782162247
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
undefined
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $15.99/month. Cancel anytime