16.5.0 SQL Wizard Reference
The SQL Wizard is a user interface that allows you to build SQL select statements without needing to know how to code SQL. The SQL Wizard makes it easy to navigate and select a subset of data in a SQL data source. For example, instead of creating a table of all the employees in a company, you can use the Wizard to create a table of sales reps only.
This article will introduce you to the Wizard. For a detailed example that uses the Wizard, see How Do I Create a Select Statement With the SQL Wizard?
Changes in Version 16.5.0
Added search bar to enable search functionality:
- Search for specific columns across the connected SQL database or a specific table
The SQL Wizard Interface
The SQL wizard is comprised of four panes: the Data Pane; the Composition Pane; the Results Pane; and the Select Statement Pane.
The Data Pane is a graphical representation of your SQL data source. When selecting columns to use in your select statement, you can drag them from the Data Pane onto the Composition Pane.
The search bar can be used to find columns in your database faster. The search can be global (across the entire database), or it can be focused on a specific table within the database. To search within a specific table, locate said table and click on it (make sure its selected), then conduct the search.
- A general search will return multiple columns. For example, if "name" is searched, the "LastName", "FirstName" and any other column that has "Name" in it's name are returned.
- If a case sensitive search is preferred, check the "Match Case" checkbox.
To cycle through the results of the search, use the down arrow button located next to the search box (or ENTER key) to go to the next column, and use the up arrow button (or SHIFT+ENTER) to cycle back through the results.
Note: When conducting a new search, make sure to select the database or table that will be searched.
The Composition Pane is where you select columns you would like returned, create conditions that will be applied to the data, sort your data and create joins across two or more tables.
- The Columns section is where you drag from the Data Pane all of the columns you will use in your report (this is so you don't have to sift through all of your data, just the information you need).
- The Sort area is where you define a sort order for the returned data, i.e. ascending or descending alphanumeric sorts.
- The Filters area is where you create conditions. A condition is made up of a column, a comparison, and a value. Only the data which meets these conditions will be returned.
- The node (or column) is the data upon which you want to create a condition. In this example's data source, the column is the customers' company names.
- The comparison is a contextual drop-down list whose options may include, but are not limited to, equal to, not equal to, greater than, greater than or equal to, less than, less than or equal to, contains, and starts with. In this example, we chose the comparison starts with.
- The value is what the data in the column will be compared to. In this example, we chose the value "A."
- The Joins area is where the Wizard connects two or more different tables which contain the same column. All the columns from joined tables can be used in your select statement. For example, the join below is connecting the Customers table and the Orders table through the Customer ID column, which is contained in both tables.
The Results Pane is what you see when you have created a condition. It shows what data meets the condition(s) and will be returned to a Tag when you generate output from your Report Template. It is similar to the Results Pane in the Tag Editor.
Select Statement Pane
The Select Statement Pane shows, appropriately enough, the select statement created by the conditions. It is updated in real time as you add, modify or delete conditions. When you click OK to close the Wizard, this is the text you will see in the Query Pane of the Tag Editor.