[00:00] In this lesson, I'll show you how to set up a SQL query data sourcing report, which allows you to easily create parameterized queries. First thing I'll do is I'll create the SQL query data source and start typing out my query. If I want to use a parameter, I can type in a question mark You'll notice the parameter area that just popped up below the query. This allows you to write an expression and reference one of the report parameters or tag value. You can add as many parameters as you like and if you click inside one of these areas, you'll see it highlights the question mark that it corresponds to. One thing to note is that these front is prepared statements. So they're more resistant to SQL injection attacks than the basic SQL queries. Another advantage of the SQL query data source is the ability to use the SQL query Builder, which you can get to by clicking Show Builder. Before you click the Show Builder button select your database from the translator drop-down.

[01:07] If you can't find your database you can always select Universal. We can see all the database tables that exist within the selected database connection over here. If I double click on one, you can see that this window appears the table's columns, and there's now a query in the window below. If I want to query specific columns, I can start checking some of the boxes. And you'll see that additional rows are added to the table here. Each of the columns in this table provides another option for modifying the query. The Output column specifies whether the row should appear in the resulting data. The Expression column allows you to select a column from the table that this row represents. The Aggregate column is where you can calculate the average count max/min or sum of the selected column. The Alias column lets you specify an alternative name for the column.

[02:01] The next two columns Sort Type and Sort Order let you specify a column to sort on and sort in either an ascending or descending order. The Grouping column allows you to group the results. Criteria lets you specify a where clause. Finally, the Orc columns allow you to add additional conditions to wear clause. You can also right click on the background here and add a new object. I'll add one of my other tables. If I wanted to join these two tables, the Query Builder allows that. All I need to do is drag and drop in the two columns that they share. In my case, the ID and equip ID columns. If I wanted to remove this join, I could right click on the connection here. But I'll leave it. Now that I've selected the data I want, I'll test it out. If I copy the query down below and paste it into the Database Query Browser, you can see that it built a query that returns all the data I want.

[03:04] As you can see the Query Builder is a powerful tool that can be leveraged via the SQL Query Data source.