SQL Query Data Source
Using the SQL Query Datasource​
The SQL Query Data Source allows you to craft parameterized queries that run as a prepared statement. As Prepared Statements, these queries are more resistant to SQL injection offering additional security over basic queries.
The SQL Query type looks very similar to the Basic SQL Query type. It has a large text area where you can enter in a SQL select query. On the right, you have the option to rename the Data Key, choose what database to run this query against, and add in a Nested Query.
Parameters in SQL Query​
Because the SQL Query Data Source runs as a prepared statement, passing parameters into this query type works a little differently.
Instead of placing a parameter within { } characters like the Basic SQL Query, we place a ? where we would like to pass in a parameter. Doing this will actually create a new text area below the query area for your parameter. This smaller text area is where you can pass a parameter into the query. You can pass in Tag values or Report Parameters, and since the parameter area allows expressions, you can use expressions to create any value you like from a combination of Tags and Report Parameters.
This is the preferred way to pass dates into your queries. You can use the {StartDate} and {EndDate} default Parameters directly in the Parameter fields.
You can add as many of these to a query as needed, with new parameter areas popping up underneath as they get added. To help keep track of what parameter corresponds to which "?" when entering in a value into the parameter area, the associated "?" will be highlighted.
For parameters in a SQL query, you can only use parameterization in the clause of a prepared SQL statement.
Crafting Queries with the Query Builder​
The SQL Query data source includes the powerful SQL Query Builder tool. The Query Builder is a powerful Drag-and-Drop query building GUI that allows you to make complex queries from your connected databases. While a basic understanding of SQL helps make the most of the Query Builder tool, most people will have no problem creating effective queries after a brief tutorial. The Query Builder is a third party tool that we brought into the Reporting Module. We go into detail on how to use it on the Query Builder page, but you can also check out the Active Query Builder's documentation for additional information.
Using the Builder​
To activate the Query Builder in your SQL Query datasource type, start by selecting the SQL Syntax version from the drop down menu beneath the SQL Query Builder button. If your Database type isn't available (or you aren't sure), you can get most of the general functionality by selecting the Universal option. Then push the SQL Query Builder button to show the Query Builder.
