Value Function 

Function Role

Sometimes, you want to extract the data meeting required conditions in certain columns and rows directly from the dataset instead of extracting the data from the cell where fields from the dataset have been dragged and filtering conditions have been added. In this case, you can use the value function.

Function Explanation

The value function can be written in multiple ways. Different parameter combinations correspond to different data extraction rules.

The simplest syntax is Value(Dataset name in the designer,Index of a required column in the dataset).

For example:

=value("ds1",3) returns the data in the third column from the dataset ds1 as an array.

Syntax:

Notes

The parameters in the value function can be set by a variety of methods. You can enter the parameters directly in the function formula or reference cell and template parameters.

For example:

value("ds1",1,2) returns the data in the first column of the second row from the dataset ds1.

value("ds1",A1,A2) returns the data in the column indexed to the value in cell A1 of the row indexed to the value in cell A2.

value('ds1',1,2,"beef") returns the values corresponding to the value beef in the second column from the first column of ds1. beef can be changed to the cell data.

value($p1,1,$p2) returns the data in the first column of the row indexed to p2 from the dataset specified by p1. p1 (assigned as the dataset name) and p2 are template parameters.

The syntax value("dataset",1,-1) is not supported, as -1 does not take effect. If you need the data in the last row of a column, you can use the similar formula VALUE('ds1',COUNT(value("ds1",3,4,"America"))) to extract the data.

Use the built-in data CUSTOMER as sample data.

Template Creation

Create a template, and enter the SQL statement SELECT * FROM CUSTOMER to create ds1, as shown in the following figure.

Data Extraction with Formulas

Enter the following formulas in the cells respectively:

Formula	Returned Value	Effect	Formula	Syntax Description
=value("ds1",3,2)	This function returns the value Wshaington in the second row of the third column from the table CUSTOMER.		Value(tabledata,col,row)	This function returns the value in the column indexed to col of the row indexed to row from tabledata.
=value("ds1",3) Note:  Since the returned data is an array, you need to set Expansion Direction to Vertical.	This function returns the data in the third column from ds1.		Value(tableData,col)	This function returns the value in the column indexed to col from tableData.
=value("ds1",3,4,"America") Note:  Since the returned data is an array, you need to set Expansion Direction to Vertical.	This function returns the element corresponding to the value America in the fourth column from the third column of ds1.		Value(tableData,targetCol,orgCol,element)	This function returns the element corresponding to the value element in the coloumn indexed to orgCol from the column indexed to targetCol of tableData.
=value("ds1",3,4,"America",1)	This function returns the first element corresponding to America in the fourth column from the third column of ds1.		Value(tableData,targetCol,orgCol,element,idx)	This function returns the No.idx element corresponding to element in the column indexed to orgCol from the column indexed to targetCol of tableData.