SQL Query
The SQL Query Data Source allows you to craft parameterized queries that run as a prepared statement. In addition, it also features the new Query Builder, which is a powerful Drag-and-Drop query building GUI that allows you to make complex queries from your connected databases. As Prepared Statements, these queries are more resistant to SQL injection offering additional security over basic queries. Craft your query, and for each variable you wish to evaluate, substitute a '?' and a new editor will be added below in which you can add static values, expressions, or references.
Using the SQL Query Datasource
SQL Query Data Sources are created by selecting it from the 
button menu on the Data tab of our Report Workspace. As with all Query data sources, at minimum you will want to verify the Database and give the Query a meaningful name in the right column's Data Key field.
Examples
Using Parameters in Queries
Parameterized Queries are an easy way to create Queries which can be safely configured to utilized dynamic values when a Report is executed in a schedule or Vision Report Viewer. When we write a Query of this type, we can substitute '?' anywhere we want to utilize a parameter value. As we add ? placeholders, the configuration panel will add editors for each placeholder. These editors allow you a ton of options. They can reference report Parameters, get values from Tag paths or utilize Ignition Expressions.
In this example, we'll use a simple parameter to alter the results of a Query at runtime in a Vision Report Viewer. Specifically, we are going to use Parameterized Query to alter the results displayed in a Table in the Vision component.
We'll be using a query that returns two columns of data:
-
The name of Customer
-
The city in which they are located.
The results from the base query look just like the image below.
The first thing we will need is a Parameter for the report, so we start by creating a parameter through our Data panel and name it CustomerLocation. Next, we'll create the actual SQL Query datasource and add our query.
SELECT CompanyName AS customer_company_name, City AS customer_location FROM CustomersWHERE City = ?
Now in the Parameter 1 editor below the query box, we want to add a reference to our Parameter, which we can do using curly braces like {CustomerLocation}. Here is what the finished data source looks like.
When the Report executes, it is going to substitute the value of the ? for whatever value is used for CustomerLocation. Remember that parameter values can be entered in the Parameter configuration of the Report Data panel, as a property in the Vision Report Viewer component, or in the Report Schedule panel, or any mixture of the three. If a value is not selected as a default in the Report Data panel, one will need to be specified in the Vision component to generate a report view in the Viewer, or in the Report Schedule for successful generation via schedule Action. If a default is provided, any values entered in those respective areas with override the default. In our case, we intend to use the Report only in a Vision component, so we will forgo the default and let the operator enter a value to see the result.
Now we'll create a simple table in the design panel by dragging the table component onto the page and dropping our CustomerLocations data source key into the DataKey config field. We'll set Header and a Details rows to visible and unstructured and add and then add some simple text. The header will let us know which city the parameter is set to, and we will simple print a line for each customer name we find that match the city in the details row. If we click over to the Preview panel now, we'll find the Report blank. This is expected since we have no default parameter and have yet to specify one.
Now we shift to the Vision workspace to create a new Window and add a Report Viewer component. If we select the Report we created in the Property Inspector of the Report Viewer component, we should see a similar Data Collection Error to the one in the Reporting Workspace Preview panel.
To correct the error, we simple need to add a value to the parameter field for CustomerLocation. We know there are some Customers in Madrid, so we try that and we get the following result.
These parameter properties and bindable, so you can use text fields, tag values, or any other Ignition binding to set the value. By using parameters in this way, we allow a lot of flexibility while at the same time significantly reducing the risk of SQL injection. This is a simple example, but using this same process you can create complex reports in a similarly simple process!
Crafting Queries with the Query Builder
The SQL Query data source includes our first release of the powerful SQL Query Builder tool. The Query Builder is a graphic tool for building queries. While a basic understanding of SQL helps make the most of Query Builder powerful tool, most people will have no problem creating effective queries after a brief tutorial.
To activate the Query Builder, start by selecting the SQL Syntax version from the drop down menu beneath the 
button. If your Database type isn't available (or you aren't sure), you can likely get most of the general functionality selecting the Universal option. Then push the button to show the Query Builder. The Query builder has a lot of functionality and can consume a lot of space in the Designer. As a result, we recommend using it on larger monitors if possible.