SQL - Part 3: Filtering with Static Values


To filter data based on a single, static criteria through the creation of a simple document which displays a list of discontinued products.

Background Information

Suppose you are required to create a document which lists all of your companies discontinued products so that you can clear out the inventory. AutoTag and the SQL Wizard allow you to do just that by creating a filter to find all discontinued products. Here, we won’t show you the actual template creation as you can learn this in several other tutorials such as the ForEach Tag tutorial.


Inserting a Tag, ForEach Tag, SQL - Part 1: Basic Selection

Starting Template

Start with SQL Wizard open. To open it, simply select the ForEach tag you are selecting data for (or create one and make sure the document has a SQL datasource, then make sure it’s selected) and click the Wizard button in the AutoTag ribbon, or check out the SQL - Part 1: Basic Selection tutorial.


1. Drag ProductName and Discontinued to Columns

First, we need data to be returned, so we’ll start off by adding the ProductName and Discontinued columns from the Products table. Do this by expanding Tables, then Products, and then drag ProductName and Discontinued to the top box in the middle of the window labeled Columns. This is exactly what you did in Part 1.

Show Me How!

2. Add a filter group, then add a filter

After that, we want to add the filter. In the SQL Wizard, to add filters, you have to click the statement click here to add a group first, then click here to add a filter.

Show Me How!

3. Set filter to use Products.Discontinued

Once you have a filter, you simply need to set the properties so the filter knows what it is filtering. Click onclick here to select a node and select the Discontinued column from the Products table.

Show Me How!

4. Set filter to check for True

Next, click on click here to set the value to set a value to compare Discontinued to. Since we want to see Discontinued products, type True here (with a capital T; SQL Wizard is case sensitive in this case). And that’s it. Creating a filter is that easy!

Show Me How!

5. View results in right panel

Of course you probably want to see what kind of output is returned when this filter is applied. You may have already noticed, but you can easily see this in the right side of the window.

Show Me How!

Quiz Yourself!

True/False: Filtering is simply a way of getting rid of the columns I don’t need, so I only get a small number of columns to choose from.
False. Filtering removes the good stuff from my database so all I’m left with is junk.
True. That’s the point of the SQL Wizard.
False. Selecting columns from the SQL Wizard already removes the unneeded columns from your selection. Filtering filters out rows of data.
True. Filtering can filter out entire columns or just rows.
Can you use drag and drop to create filters?
Yes. But I’m not sure what to drag so I just click.
Yes. The wizard can do anything! Even collect data from Mars!
No. Filters are created by clicking and selecting. Most of the operations in a filter are a simple selection anyway.
No, you have to click first, and then you can drag and drop
Which is not a good use for filters?
Creating a book list with books only written by a certain author
Like online shopping, I want something that costs less than X, and is made by brand Y
Finding data based on criteria that is not stored in the database
Selecting a subset of financial data based on a certain date range


You have completed this tutorial. We recommend taking a look at the SQL - Part 4: Filtering with Parameter Values tutorial next!