Overview
Version
Report Server Version | Functional Change |
11.0 | / |
11.0.18 | Added a new writing method to prevent SQL injection and enhance security. The original method is still available. |
Function Role
Although dataset functions can directly retrieve data from datasets with conditions, sometimes you still hope that a value can be directly retrieved from a database in a specific cell, instead of defining a dataset and then retrieving data.
In this case, you can use the SQL function.
Function Explanation
Syntax | SQL(connectionName,sql,columnIndex,rowIndex) | The return data is the element corresponding to the columnIndex column and the rowIndex row in the SQL statements table obtained from the connectionName database. |
Parameter 1 | connectionName | Name of a data connection (in string format and with quotes such as "FRDemo") |
Parameter 2 | sql | SQL statements (in string format) |
Parameter 3 | columnIndex | Column number (integer) |
Parameter 4 | rowIndex | Row number (integer) |
Note:
Notes
Only Select query statements are supported. Adding, deleting, and modifying statements like Insert, Delete, and Update are not supported.
Retrieving Specified Contents Without Parameters from a Database
Sample data: STSCORE data table in the built-in database FRDemo
Retrieve the value of the third row and third column from the STSCORE table in the built-in database FRDemo.
The value of the third row and third column is Alex.
To directly display the data value Alex in a cell of the report, instead of defining a dataset first and then retrieving the data, you can use the formula sql(). Enter the formula in the cell: =sql("FRDemo","SELECT * FROM STSCORE",3,3) to view the value Alex in the preview.
Retrieving Specified Contents with Parameters from a Database
Sample data: STSCORE data table in the built-in database FRDemo
SQL Parameter As a Regular Parameter
Retrieve values of the third column whose CLASSNO is Class 1.
Enter the formula in the cell: =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3). The display effect is shown in the following figure.
The following table explains each part of the formula above.
Formula | Explanation |
"FRDemo" | Data connection name |
SELECT * FROM STSCORE where CLASSNO = 'Class1' | SQL statement used to query data with CLASSNO as Class1 |
3 | Column number (data in the third column) |
If you need to display a specific value, such as Jonny (the value in the fourth row and the third column in Class1), the syntax is as follows:
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3,4)
SQL Parameter As a Variable
Note:
If the parameter value is a variable (such as a report parameter or a cell), the syntax is as follows: =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '${class}' ",3,4) or =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+A1+"' ",3,4).
For example, you hope to display the names of all students in each class through a filter widget.
First, set a template parameter class. Then enter the formula in the cell: =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '${class}' ",3).
The effect is as follows.
If the parameter passed is to get the value of the current cell ($$$ as the parameter), strings need to be enclosed in single quotes.
=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '${$$$}' ",3,4)
1. If there are multiple parameters or cell values, the SQL function should be written as follows: =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO in ('${class}') and COURSE in ('${COURSE}') ",3,4) 2. The separator for the return value of the parameter class should be a comma (,).Note:
SQL Parameter As a Variable and Needing to Be Concatenated
In SQL statements, you can also use IF function to judge and concatenate template parameters. For example, if you hope to select all students' names when the parameter class is empty, you can enter the formula: =sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '${class}'"),3).
sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '${class}'"),3)
The following table explains each part of the formula above.
Formula | Explanation |
"SELECT * FROM STSCORE where 1=1 " | Add quotation marks to both sides of the SQL statement to make it be a string. |
+if(len(class)== 0,"","and CLASSNO = '${class}'" | The addition (+) here refers to the string concatenation symbol. Concatenate the previous SQL statements through the addition (+). When the parameter class is empty, the query statement is equal to SELECT * FROM STSCORE. When the parameter class is not empty, the query statement is equal to SELECT * FROM STSCORE WHERE 1=1 and CLASSNO ='${class}'.
|
sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '${class}'"),3) | When the parameter class is empty, the query statement is equal to sql("FRDemo","SELECT * FROM STSCORE",3). When the parameter class is not empty, the query statement is equal to SELECT * FROM STSCORE WHERE 1=1 and CLASSNO ='${class}'.
|
If the parameter is a fuzzy query in SQL, you can use the following formula: =sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3,4).
