I. Overview
1) Application scenarios
If you want to "conditionally display" the values of certain data columns when you drag columns from a dataset directly into a cell, you can use the dataset function to directly fetch part of the data from the dataset into a report template.
2) Precautions
When using a dataset function, the dataset name cannot appear with a special character, such as -.
Dataset functions are not supported in the parameter panel.
II. Tablename
1. Tablename.select
1) Overview
| Grammar | tablename.select(colname,filter1&&filter2&&......) | Filter out the qualified data in a column of the dataset. The result is an array. The same data will not be merged. |
|---|---|---|
| parameter 1 | tablename | Represents the dataset name, report dataset or server dataset name, not the table name in the database. |
| parameter 2 | colname | Represents the column name, case insensitive. |
2) Precautions
The judgment in the screening condition can use either single equal sign or double equal sign.
A string can also be quoted in either single or double quotes without affecting the result.
Tablename.select () differs from sql () in that tablename.select() fetuses data from a dataset, while sql () fetuses data from a database. See SQL functions for details
In the formula, a string that begins with 0 is used to determine a match.For example, ds1.select(colname,ID="003") 0, 03, 003...... Results. If you only want to return 003, you can use EXACT to determine the match. For example: the ds1. Select (colname, exact (ID, "003"))
3) Example
For example, dataset ds1 takes out the table "Products" in the built-in FRDemoEN database and enters the following formula in the cells respectively:
| Formula | Result |
|---|---|
Enter in the A2 cell =ds1.select(Product_name) | Returns all product names in the product name column of datset ds1.
|
| Enter in the B2 cell=ds1.select(Product_name,Inventory_quantity>20&&Order_quantity > 30) | Returns the products whose inventory of dataset ds1 is greater than 20 and whose order is greater than 30.
|
| Enter in the C2 cell=ds1.select(Product_name,SupplierID="1"||Inventory_quantity>30) | Returns products with dataset ds1 vendor 1 or inventory greater than 30.
|
2. Tablename.group
1) Overview
| Grammar | tablename.group(colname,filter 1 && filter 2,ascending and descending order) | Select the data that meet the conditions in a certain column of the dataset. If the adjacent data are the same, the data can be merged, and the ascending and descending order can also be arranged according to the column. |
|---|---|---|
| parameter 1 | tablename | Represents the dataset name, report dataset or server dataset name, not the table name in the database. |
| parameter 2 | colname | Represents the column name, case insensitive. |
| parameter 3 | Ascending and descending order | Is a Boolean value, true indicates ascending order, false indicates descending order. Note: If a descending order parameter is used, then the filter parameter must be written. If there is no filter parameter, either true or space can be used instead: for example=ds1.group(Salesperson,true,false)or=ds1.group(Salesperson, ,false) |
2) Precautions
For example, dataset ds1 fetched the "Sales_Volume" table in the built-in FRDemo database:
| Formula | Statement |
|---|---|
| Enter in the cell=ds1.group(Salesperson) | Returns the values in the ds1 salesmen column of the dataset and is merged if adjacent data are the same. |
| Enter in the cell=ds1.group(Salesperson,Region = "East China"&&Sales_Volume > 200) | Return dataset ds1 of salesmen with total sales of more than 200 in East China, and merge adjacent data if they are the same.
|
| Enter in the cell=ds1.group(Salesperson,true,false) or=ds1.group(Salesperson,,false) | Returns the values in the ds1 salesperson column of the dataset, which will be merged as long as the data is the same. The result is sorted in descending order, and the parameters in the middle are filtered. If there is no condition, empty or true can be used.
|
| Enter in the cell=ds1.group(Salesperson,Region=="East China") | Return dataset ds1 East China sales, and adjacent data will be merged if the same. |
| Enter in the cell=ds1.group(Salesperson,Region=="East China",true) | Return dataset ds1 East China sales, and will merge all the same items, the result is in ascending order. |
3. Tablename.select (# number)
1) Overview
| Grammar | tablename.select(#number) | Returns the row number or corresponding column data in the dataset |
|---|---|---|
| parameter 1 | tablename | Represents the dataset name, report dataset or server dataset name, not the table name in the database. |
| parameter 2 | number | Indicates the column number. If tablename.select(#0) print an array of row numbers Tablename.select (#1) prints the array data for the first column in the database table |
2) Precautions
If there is any abnormality in the linkage calculation of ds1.select(#0) in the filling scenario, the parent-child lattice relationship needs to be checked. For example, when the calculation results shown below appear:
The remoteEvaluate(String) method does not support tablename.select.
3) Example
For example, dataset ds1 retrieves the "Sales_Volume" table in the built-in FRDemoEN database:
| Formula | Result |
|---|---|
| Enter in the cell=ds1.select(#0) | Returns the row number of the dataset
|
| Enter in the cell=ds1.select(#1) | Returns the corresponding column data in the dataset
|
4. tablename.value(row,col/colname)
1) Overview
| Grammar | tablename.value(row,col/colname) | Gets the value of a row and a column in dataset ds1. |
|---|---|---|
| parameter 1 | tablename | Represents the dataset name, report dataset or server dataset name, not the table name in the database. |
| parameter 2 | row | Said the line Numbers |
| parameter 3 | col/colname | Represents the column number or column name |
2) Precautions
The chart title of the report does not support this function.
The chart block in the decision report does not support this function.
This function is not supported in JavaScript.
3) Example
For example, dataset ds1 retrieves the "Sales_Volume" table in the built-in FRDemoEN database:
| Formula | Statement |
|---|---|
| Enter in the cell=ds1.value(3,2) | Returns the value of row 3, column 2 in dataset ds1 |
| Enter in the cell=ds1.value(3,"Salesperson") | Returns the value of the salesman column in row 3 of dataset ds1 |
III. Application
1) Select a Field based on different conditions
Enter the following formula in the cell:
=if(condition,ds1.group(customerid),ds2.group(customerid))
And set its extended property to top to bottom.
Formula description:
| Formula | Statement |
|---|---|
| =if(condition,ds1.group(customerid),ds2.group(customerid)) | If true, the cell uses the CustomerID column in dataset ds1, otherwise it uses the CustomerID column in dataset ds2. |
2) Re-calculate the data returned by the dataset function
Enter the formula =sum(ds1.select(Sales_Volume)) in the cell to return the sum of the sales column of dataset ds1.
The sum formula can also be used in other ways, such as count, max, etc.
IV. Extend the data returned by the dataset function
Data columns are automatically expanded from top to bottom when dragged directly into a cell. However, using the dataset function to obtain data as an array, is displayed in a cell, need to set the cell extension properties, data will be expanded.
For example, enter the formula =ds1.group(Salesperson,Region=="East China",true)，Set extension room orientation to "Vertical"，as shown in the picture below:
The preview report is as follows:
