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


Autoplay Off

Supplemental Videos


The Basic SQL data source query is similar to a Query Binding on a Vision component. Learn how add parameters and make the query dynamic.

Video recorded using: Ignition 7.9


(open in window)

[00:00] Let's create a basic SQL query data source. This type of query is very similar to a query binding you would see on a vision component. We'll start in the data panel here. We'll click on the plus icon and click on basic SQL query. When we create a new basic SQL query, there's a placeholder query all ready for us. We can also set the data key, this is the name of the data source, so we'll go ahead and just change this to fruit report. Next, we can change the query type. You'll see that the query data sources are available, so we can't change this to a static CSV or a scripted data source. Note that changing the query type will wipe out your query, so always make sure to create a copy before you change the query type here. We can specify a certain database connection. Next is this preview limit property. We'll come back to this in a moment, though. And there's an area where you can create a nested query. So let's start by looking at what data I want to bring in here. We'll go to the database query browser, and I have 10 rows here. I'm going to go ahead and bring the data in as is to my table. So we'll go ahead and close this, and I already have the query. Now my query here is just selecting everything from the table. And if we take a look at the preview, I already have the table set up, and there are all of our rows here. Now let's take a look at this preview limit. This limits the number of rows returned by your data sources when viewed from the preview panel. This way, if you're trying to troubleshoot a particular problem, you can go ahead and limit how much data's coming in. Note that this is only applied when viewed from the preview panel. So just to give you an example, I'll go ahead and limit this to five. If we check the preview panel, you'll see that there are only five rows, which makes looking through the raw data much easier. Set it back to data and set this back to a hundred. Now what I'd like to do is go ahead and filter this off of a particular parameter. So let's create a parameter. Create a new one, call it customer name. Go to string, and we'll give it a default value of Mary, which again, can be overwritten, which we'll do. Next, we'll come to the data source. And we need to go ahead and add a where clause. So we'll start a new line. Customer column equals, now I can type out with the curly braces, customer name. But there's also this little parameters icon right here. I'll go ahead and click on that, click on customer name. And you can see it automatically does it for us. Now since we are passing a string in, let's go ahead and add some quotes around here. Take a look at our preview. You'll see that only Mary's values are coming back. Next, let's take a look at this in the report viewer component. Click on project browser, click our windows, basic SQL query, and you'll see I have this window already set up here for us. Now, I also created this dropdown list component. This component has a list of all of our customers in it. If we take a look at the customer name parameter on our report viewer, you see that I've gotten that already to the dropdown list. So now we can go ahead and just select each one of our customers, and get their rows from our table.

You are editing this transcript.

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