Syntax summary of FR module

There are many places where FR uses formulas, cells (the ones beginning with = are parsed as formulas), condition display, data dictionary, report attribute value definition, chart title, axis definition, header and footer, and even cell You can write formulas in the mouse hover prompts in other properties, although the edit box is very different.

1) The sentence grammar feels weird at first contact, if (conditional expression, value 1, value 2), if can be nested, if (conditional expression 1, value 1, if (conditional expression 2, value 2, value 3) )).

2) The switch statement. For details, please see: Switch function

3) Both single and double equal signs can be used for judgment.

4) Use single quotes and double quotes for strings.

• Bool has only true or false. And logic or && or logic or ||.

• If it is judged to be empty, use isnull() or len($$$)=0.

• Format formula. The format formula is more flexible. format($$$,"0.00") can convert the number of the current cell into two decimal places, or use format(12,"budget value: 0.00 million") to change 12 into budget value: 12 million Yuan, is this different from the custom display data dictionary magic horse? One thing to note is that the full-digit year, month, and day are styled as "yyyy-MM-dd" instead of "yyyy-mm-dd". The lowercase mm means minute (minute).

5) When taking a cell in the formula, use A1 or b2 directly, and the syntax for cross-sheet value is sheet1!A1.

6) SQL function, a method to directly call the database. For details, please see: SQL function

7) Data set formula.

The dataset formula is a method in which the formula directly calls the data set. For details, please see: Dataset Function

For example, there is a template dataset ds1 whose fields are region, salesperson, and sales volume. You can use ds1.group (region) to get the region field of ds1. The group is a group, so it is easy to know that select is a list, and ds1.select (salesperson) Is to obtain the salesperson field in the form of a list, and you can add restrictions. For example, ds1.select (salesperson, sales=111) is to obtain a collection of salespersons with a sales volume of 111. Several functions related to the data set are colcount, colname, row, rowcount, etc.

8) Level coordinates. For details, please see: Hierarchical coordinate function

Level coordinates are more difficult in the formula. The "playability" is very strong and there are many things. Only the most commonly used ones can be remembered, which can solve various BT needs. The more commonly used one is the ranking formula =count(B3[!0]{B3> $B3})+1. See it for yourself.

9) Filter formula. For details, please see: Filtering

There are two types of conditions for filtering, one of which is formula conditions. The difference between this formula is that you can directly write any field of the data set where the field of the current cell is located, which is much more flexible than ordinary formulas. Take the built-in sales table as an example, filter the cell where the area field is located, and the formula conditions can be directly written: sales==111.

10) Conditional attributes. For details, please see: Condition Attribute

Simply put: the condition attribute panel, the upper part is the attribute, and the lower part is the condition. When the following conditions are met, that is, the return value is true, the above property settings are executed.

The pane defined by the dataset is also one of the most commonly used modules in reports.

1. Implementation process

In fact, a string is generated here, and FR passes this string to the report for execution through the set data connection. In this process, the report should first replace the report rules (for example, the current rule is to put parameters in ${}) and replace it with a string that everyone recognizes. After the corresponding database is executed, the value will be returned, and it may be returned. If an error message is reported, it is also possible to return a data set. This error message should be given by the corresponding database. So for the same SQL statement, different databases using different SQL syntax may have different results. For example, select'sdf' is displayed normally in Access and SQLServer, but an error is thrown in Oracle. Use select version() or show status or show tables to execute normally in the data set definition of MySQL, but not others. The and and or in SQL must be distinguished from formulas. Someone may use && as and in formulas. And the or || in the formula is the concatenation of the string in Oracle.

2. Call formulas in SQL

The report realizes the interaction with SQL statements through formulas and parameters.

The formula is placed in the frame ${}. So ${"select * from t1"} is actually equivalent to select * from t1.

The difficulty of using formulas in data sets is that they are cumbersome to stitch together.

The first example should be the return of all sql statements when the parameter is empty on the help document:

SELECT * FROM ORDERS where 1=1 ${if(len(area) == 0,"","and shipper area ='" + area + "'")} ${if(len(province) == 0, "","and shipper province ='" + province + "'")}

Take an example of calling format in FR:

select ${"'"+format(p1,"yyyy-MM-dd")+"'"} from aa

If you omit the single quotes before and after the format function, you will get wrong results. In fact, sometimes it can be simplified by some methods, such as declaring a variable p2, the definition of referencing this variable is format(p1,"yyyy-MM-dd"), then the above SQL becomes select '$ {p2}' from aa

3. Call SQL functions in SQL

As mentioned earlier, SQL statements are actually strings. In fact, it is possible to call a string from the database as a SQL statement for operation. This will use SQL functions, let's say an example:

=sql("FRDemo",sql("FRDemo","select testtext from test where id=2",1,1),1,1)

The testtext field of the second data in the test table is the string select top 1 area from sales

This string can be used in SQL formulas as SQL statements to execute

The dataset definition side can be written like this ${sql("FRDemo","select testtext from test where id = 2",1,1)}

1. JS of FR

As a B/S product, JavaScript on the browser side is essential. The js in FR has already called finereport.js.

When previewing the report, the report Servlet will convert the cpt template to HTML, and FR JS will be introduced into the head of this HTML. This finereport.js contains many built-in functions and some public attributes, whether in the template or In other web pages, as long as finereport.js is introduced, public properties and methods can be called in the form of FR.xxx.

2. Difference

There are roughly two types of JS, the event related to the page state and the event related to the button. There are three types of buttons, one is the toolbar, the other is the parameter panel or decision report mode, and the other is the fill-in page. Some statements of different buttons are slightly different.

The dashboard is form, and the filling is write, so as to obtain the widget and assign the widget value as an example:

this.options.write.getWidgetByName("p1").setValue("aaa");
this.options.form.getWidgetByName("p1").setValue("aaa");
contentPane.getWidgetByName("p1").setValue("aaa");

3. JS edit box call formula

The formula is placed in the frame "${}".

For example, var v1="${len('sdf')}"; then v1 is 3.

This can be compared with the data set definition and formula interaction. The string below "" in JS is a string, SQL is actually a string, SQL is defined in ${} where the formula is, and "${}" in the JS edit box is the formula.

The first is the op parameter. For details, please refer to: description of op parameter

The preview mode of the report by default is pagination preview, when the op parameter is write, it is the report preview, op=view is data analysis, op=form is decision report mode

Types of other op parameters:

op=fs, data decision system

op=excel_submit, import and fill in Excel in background

op=auth_login, the middle page of login, requires fine_username and fine_password parameters

Other parameters:

__bypagesize__: Control whether to page

__pi__: Whether to display the parameter interface

__filename__: Control the export file name, if not, use the template name by default

Note: The underline is two horizontal lines