This lesson is part of the Reporting in Ignition course. You can browse the rest of the lessons below.


Autoplay Off

Supplemental Videos


The SQL Query data source runs as a prepared statement, and supports the user of parameters. Additionally this data source features the power Query Builder that allows you to create queries through a simple to use drag-and-drop interface.

Video recorded using: Ignition 7.9


(open in window)

[00:00] The SQL query data source allows you to easily craft parameterized queries. We'll start in the Data panel. I already have one created here. The way it works is we can just start typing in here. And then put a question mark anywhere within our query. Once we do that, you'll see this little parameters area opens up. From here we can write an expression, we can add one of our parameters that we've already added to our report or we can just do straight tag binding. Make as many parameters as you'd like. Just to help keep things straight, if you click one one and you're editing, you'll see that whatever question mark relates to that parameter is highlighted in blue. It's important to note that these run as prepared statements so they're more resistant to SQL injection attacks. This type of data source also features the query builder. I'm going to go ahead and clear this out. We look to the right-hand side, you'll see there's Show Query Builder or the Show Builder Button. First what you need to do is come down near the dropdown and select whatever type of database you're using. If you don't see yours here, you're not too certain, you can always just use the universal. I'll click on my SQL since I know I'm using my SQL and I'll click on Show Builder. The interface has changed quite a bit. You'll see some database tables over here. These are all the database tables listed in the selected database connection my data source is using. I can just double-click on one of them and you'll see this new table appears up here. Each checkbox here represents a column on the table, save for the top one, which is Select All. You can check whichever columns you'd like returned so I'm just going to go ahead and select all of them. Let's see down below, I have a query that's set. Let me click on Apply and that's really it. But there a lot more configurations we can make. We'll click the Show Builder button again. You'll see my query's back up here. For every column I select up top here, you'll see there's a new row entered down below. On the left-hand side of this table, you'll see this sideways arrow icon. If we click on it, we expand some options available to these rows. From here we can remove certain rows, we can reorder them, we can add a new row in-between and change this up however we like. I'm going to go ahead and click this just to minimize this section. We'll add a couple more rows here. Looking at this first column here, you'll see we got Expression. We can click here and just change whatever column we're selecting. We'll go over, we add an aggregate, so average, count, max, standard deviation, so on. We can give our columns an alias. My Alias, see it appears down below. We specify a sort type, whether they go on ascending or descending or in what order. You can apply grouping to all the rows or just some of them and then there's some criteria for grouping that's listed over here. Additionally, if you come up top and you right-click on some of this white space here, you can add an object. An object can be any of your tables, which we see over here on the side but you can also add any views or stored procedures you have. You close this and we'll clean up our space a little bit, too. We can very easily do joins, as well, so I'll drag my equipment details table and my equipment downtime report table. There we go. I want to get the name and the location from this table. I'm doing the cause and duration and I want to join from equipment ID to ID on my equipment details. I'll click, drag from equipment ID to ID on the equipment details table, I'll go. See there's this little line between the two. If you want to get rid of this join, you can just right-click on it and select Remove. But if we take the look down below, we see it has query already for us. We'll click apply. We could drop the table down in our report, but since this is just a query we're working with here, we'll just copy this, go into Tools in the designer, go to the Database Query Browser, paste this in, execute it, and there's all my data. As you can see, the query builder is a powerful tool and it's only available with the SQL Query Data Source.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.