Map Function

Function

During report creation, you may need to retrieve data from a dataset based on the value of a specific cell and return the corresponding value.

In this case, you can use the map() function to find the corresponding dataset by the dataset name, and then locate the returned value corresponding to the key within the dataset index column. The map() function first search the template dataset, then the server dataset for required data.

Example:

The formula MAP(1001, "employee", 1, 2) returns the value in the second column of the row in the dataset employee where the value in the first column is 1001.

The formula MAP(1001, "employee", "name", "address") returns the value in the column address of the record in the dataset employee where the value in the column name is 1001. 

Note:

Only the first value found is returned.

Usage Description

MAP(Object, String, Int, Int): finds the dataset corresponding to the given dataset name, and returns the value in the specified column of the record where the index column matches the given key.

Object: the index value, or the content to be looked up

String: the name of the dataset, or the name defined for the data query

Int: the column index of the index value

Int: the column index of the return value

Note:

The last two parameters can also be column names. You can find the returned value corresponding to the key value in the index column based on the dataset name. The datasets are searched sequentially from the report dataset to the server dataset. The initial value of the index column number and returned value column number is 1.

Report Design

Data Preparation

Create a dataset named ds1 with the SQL statement SELECT * FROM Supplier, as shown in the following figure.

Template Design

Create a general report, and assign values to cells A1 to A5, respectively. You can right-click cell A2, choose Cell Element > Insert Formula, and insert the formula. You can assign values to cells A3 to A5 in the same way. The formulas are as follows:

MAP(A1, "ds1", "Company_name", "SupplierID"): The function returns the value in the column SupplierID of the record in dataset ds1 where the value in the column Company_name equals the value in cell A1.

MAP(A1, "ds1", 2, 1): The function returns the value in the first column of the record in dataset ds1 where the value in the second column equals the value in cell A1.

MAP("Intel", "ds1", "Company_name", "SupplierID"): The function returns the value in the column SupplierID of the record in dataset ds1 where the value in the column Company_name is Intel.

MAP("Intel", "ds1", 2, 1): The function returns the value in the first column of the record in dataset ds1 where the value in the second column is Intel.

Effect Display

1. PC

Save the template and click Pagination Preview, as shown in the following figure.

2. Mobile Terminal