Overview
This document explains how to use the calculation function in FineReport. This document also explains when calculations are available and how to use the formula editor. If you are a new user of FineReport and not familiar with the functions, you can start by reading this document.
Reason for Calculation Use
You can create data based on the existing data in the data source and perform calculations on the data by calculation.
You can perform complex analyses and create visual templates with the calculation results.
Scenario for Calculation Use
You can create data based on the existing data in the data source during the calculation.
You can use functions to perform calculation in many scenarios. Such as: Splitting data.
Converting field data types (for example, converting a string to a date)
Filtering results
Calculating proportions
The following sections show scenarios where calculations are needed.
Lack of Needed Data for Analysis in the Data Source
The built-in data table OrderDetails_new in FRDemo contains Price, Quantity, Purchase Price, and Discount fields.If you need to calculate the Profit value of each order ID and the Average Profit value of all values of order IDs, you can use formulas similar to those shown in the following part to perform the calculation, as shown in the following figure.
Profit: D2*(C2 - E2)*(1 - F2)
Average profit: average(G2)
The following figure shows the result.
Value Conversion in Components
For example, you can enter the formula TODATE("2021/02/07 Wednesday", "yyyy/MM/ddEEE", "zh") to convert the string "2021/02/07 Wednesday" to a time type value, as shown in the following figure.
Configuration of Table Style and Color
For example, you can set alternating row colors in a table.
For details, see Interlace Color Change after Hidden Row.
Positions for Function Calculation Use
Function Use in the Formula Editing Box
The following shows the positions in FineReport where you can use Formula Editor:
| Position | Illustration |
|---|---|
Condition Attribute | Example: Changing the Background Color Across Rows/Columns.
|
| Data Dictionary | Example: section "Formula" in Data Dictionary.
|
Sifting and Filtering |
|
| Data Entry Submission Condition | |
| Chart Title | |
| Chart Axis | |
| Cells as Chart Data Sources | |
| Report Header and Footer | |
| Cell Content Prompt | |
Function Use in the SQL Editing Box
Using common functions in SQL statements
You can put formulas in ${} when using formulas in SQL statements. For example, the following SQL statement returns all content of the table if the parameter is null.
SELECT * FROM Order WHERE 1=1 ${if(len(area) == 0,"","and supplier area = '" + area + "'")} ${if(len(province) == 0,"","and supplier province = '" + province + "'")}
Note:| Parameter/Formula | Description |
|---|---|
| area and province | Custom parameters |
| if(len(area) == 0,"","and supplier area = '" + area + "'") | If the value of the area parameter is not null (namely, len(area)!=0), the IF clause will be executed. For example, if the value of area is East China, then the execution result of the IF clause is and the supplier area = 'East China',. The SQL statement concatenated with the IF clause is as follows. SELECT * FROM Order where 1=1 and supplier area='East China' and supplier province='Jiangsu' |
Using SQL functions in SQL Statements
SQL statements are actually strings. Therefore, you can use an SQL statement from the database as a string to get the numbers. In this case, you can use SQL functions such as the following formula:
=sql("FRDemo","SELECT * FROM Sales_volume ",1,1) returns East China.
Meanwhile, you can also use this string in SQL formulas to be executed as a string. So you can use SELECT * FROM where area='${sql(“FRDemo”, “SELECT * FROM Sales_volume ‘,1,1)}’ to create a dataset.
Alternatively, you can use SELECT * FROM where area='East China'.
Previous：Function Calculation Composition and Calculation Syntax
