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.

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 (;):

So the IF function could be explained as follows:

And the second IF function can be explained as follows:

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:

Now we will create the second parameter:

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:

Have a go hero

Next, to sharpen the skills we have learned, we will modify the report created in this chapter 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)