Function Calculation Composition and Calculation Syntax

Version

Function Description

When designing templates, you need to use the formula functions in many cases such as summing, counting, and making judgements. This document describes the calculation composition and calculation syntax of functions.

Concept

Function Syntax

In FineReport, functions are the main part of calculations.

1. In FineReport calculations, functions are displayed in blue, each with a specific syntax.

For example, you can enter = in a cell of the template you create in FineReport, and then double-click the cell, after which the formula editing box will appear, as shown in the following figure.

Note: 

1. Simulation calculations are not supported in the dataset function.

2. Remote simulation calculations are not supported in the report function. 

Position	Description
A	Position to enter the formula Note:  As the calculation result with four decimals are rounded up automatically, slight differences may exist between the calculation result and the actual data. If you need to display the data accurately, you can use the ROUND function to adjust the data precision. For details, see ROUND function.
B	Box to search the function Function and Parameter, two types of search results. Prefix f(x) for function items Prefix $ for parameter items
C	Automatic recognition and display of entered formulas Parameters (including global variables, system variables, already defined global parameters, template parameters, and dataset parameters) and functions can be displayed.  Prefix f(x) for function items  Prefix $ for parameter items Note:  1. Custom formulas are displayed in the same way as the formulas that come with the report. 2. Functions are prioritized over parameters in display position, so parameters are displayed after all related functions have been displayed. 3. Only one among the parameters with the same name can be displayed.
D	Selecting the function type
E	Selecting the needed function
F	Selecting whether to reference a parameter
G	Checking the use of formulas
H	Checking the validity of formulas. For details, see section "Formula Checking."
I	Checking before closing. For details, see section "Formula Checking."
J	Simulation calculation.

For details, see section "Result Viewing Based on the Simulation Calculation."

In V 11.0.4, you are allowed to adjust the outermost layer size of the formula editing box, as shown in the following figure.

2. Multiple functions can be used in the calculation. For example: IF(SIGN(A2)=1,B2,0)

Two functions, IF and SIGN, are involved in the calculation. 

The above example is a case where one function is included (nested) in another. In this case, the SIGN function for cell A2 will be calculated before the IF function because the SIGN function is inside the parentheses.

Data Column Syntax

You can insert the data column into the calculation. The function syntax often shows where to insert the data column. For example: COUNT(value1,value2,...)

For example, if B2 is the salesperson data column, you can enter the formula =count(B2) in a cell to calculate the number of the salespersons, as shown in the following figure.

Operator Syntax

To create a calculation, you need to know the supported operators. Operators are in light black in FineReport calculations.

Text Expression Syntax

This section describes the correct syntax for text expressions in FineReport calculations.

Text expressions represent constant values essentially. When using functions, you may need to use text expressions to represent values like numbers, strings, and dates.

The function calculation consists of four elements:

Report Design

For example, create a dataset ds1 based on the built-in dataset Sales volume and make a judgment on the sales volume of each region.

Drag the Region field and the Sales volume field into cell A2 and cell B2, respectively, select Summary in Data Setting for the Sales volume field, and insert the formula into cell B2 to sum the sales volume of regions, as shown in the following figure.

Formula Input

Choose Cell Element > Insert Element > Insert Formula, and enter the formula if(B2>8000, "high volume","low volume") in the pop-up Formula Definition box to define the sales volume (If the sales volume is greater than 8000, it is defined as high volume, otherwise it is defined as low volume.), as shown in the following figure.

The following describes the entered calculation content :

Formula Checking

Two checking methods are available:

Method one: You can click Validity Check to check the formula, as shown in the following figure.

Method two:

You can select Check Before Closing, and click OK to check the formula. If the formula is correct, the formula editing box will be closed directly while if the formula is wrong, an error message will appear, as shown in the following figure.

Result Viewing Based on the Simulation Calculation

After the formula editing, you can click Simulation Calculation, and enter the data to be validated of the parameter B2. For example, enter 400, and you can view the calculation result low volume, as shown in the following figure.

Note: 

1. The following functions are not supported in simulation calculations: dataset function, hierarchical coordinates function, GETUSERDEPARTMENTS() and GETUSERJOBTITLES() (used to retrieve departmental roles based on department and position), WEBIMAGE function, TOIMAGE function, file function, and treelayer function.

2. Remote simulation calculations are not supported in the report function.

3. Parameters that require expansion before calculation are not supported in simulation calculations.