Let Function

In a report, complex FR script expressions can be used, for example: =if (a long formula > 0, execution statement 1 (a long formula), execution statement 2 (a long formula)). In this case, the first step is to place the long formula to a cell (for example, A1), and then input =if(A1>0,Execution statement 1,Execution statement 2) (for example, =if(A1>0,A1,A1) in another cell. Generally, in such long formulas, some simple calculations are included. If you reference cells in these calculations, multiple cells will be referenced to derive the final result. This assignment method not only increases memory consumption for redundant calculations, but also results in poor performance. In view of this, FineReport has introduced the LET function, which allows you to assign a value to a long expression directly. The Let function can be used for direct calculation of final results (of complex script expression) and performance improvement.

Description of the Let Function

LET(Variable_name, Variable_value, Variable_name, Variable_value, ..., Expression): A local variable assignment function. The total number of parameters, N, must be an odd number. The final parameter is an expression, while the preceding N-1 parameters (an even number) form variable assignment pairs.

• variable_name must be a valid variable name starting with a letter, which can include letters, numbers, and underscores.

• The expression refers to the result expression to be calculated after values are assigned based on the preceding N-1 parameters. These variable assignments are only valid within this expression.

The following shows an example.

LET(a, 5,b, 6, a+b) returns 11.

Example

The following examples are introduced based on the previously mentioned scenarios. You can adapt these examples to your actual needs when applying the formula.

1. If a long formula ((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1) is assigned to a, the result expression IF(a>1000,(a+200)/a,(a-200)*a) need to be calculated.

2. Common approach: You can enter ((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1) into a cell (for example, A1), and enter =IF(A1>1000,(A1+200)/A1,(A1-200)*A1) into another cell (for example, B1).

3. The value in cell A1 is substituted into the result expression accordingly during calculation. For example, the following content needs to be calculated: =IF((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))>1000,((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))+200)/(((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)),((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))-200)*(((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)))

This expression not only occupies additional cells but also causes repeated calculations, often resulting in poor performance.

4. Expression of the LET function: You can directly assign variables and enter the result expression within the cell. You can enter =LET(a, ((10 + 20 + 30) * MAX(10, 20, 30) + DATEDIF("2001/2/28", "2004/3/20", "D")) * COS(0.5) / ROUND(2.15, 1), IF(a> 1000, (a+ 200) / a, (a - 200) * a)) in a cell to execute the result expression and obtain the outcome.

Summary

In comparison, using the LET function is clearly simpler than the common approach. Moreover, if the formula involves additional simple calculations, such as calculating the maximum and average value, you can avoid using extra cells and creating redundant references. Instead, you can calculate the result directly in one step or write some commonly used calculation formulas in the LET function, significantly enhancing the overall performance.