Dynamically Change SQL Queries Using Alteryx Analytic Apps

As analysts, we frequently find ourselves being asked to do basic data pulls from databases filtered down to a specific user’s needs. This may be sales data for specific products, within a date range, for certain customers, or really anything under the sun. This is typically a simple task for analysts to tackle, but it’s rare the end-user has the knowledge to build SQL queries. Because of this it often falls on us, the analyst. Pending work volume, a relatively simple but often urgent task like this can prevent analysts from getting more complicated projects completed on time. The good news is that if your company is using Alteryx, you can set up an analytic app for the end-users to query the databases themselves. In this blog, I’ll show you how to set up an app that dynamically changes an SQL query to match the end user’s inputs.

The first thing we need to do is to write the base query that the end-user will be updating. For the purposes of this blog, I uploaded a subset of Tableau’s Superstore Data to Snowflake. Drop an input tool on the canvas, and input the query that will be needed. The important parts are to only put what you want in the output in the SELECT clause, and comment out every dimension in the WHERE clause that the user will be given the choice to edit. I start my WHERE clause with ‘1=1’ just so I don’t have to strip out the first “AND” to keep the calculations later on much simpler.

The remainder of what we will do will use the Interface group of tools. Let’s start with dropping a check box onto the canvas. I named this “Filter by Product Category?” and opted to collapse the group when deselected. This function will ask the user if they want to filter the data they are pulling by the Product Category field.

Next, we will connect this check box to our query in the input tool. Do this by dragging the magnifying glass at the bottom of the check box, to the lightning bolt on top of the input tool. You’ll notice that an action tool shows up as an intermediary, and this is where the magic really happens!

We are going to be updating the input data tool with a formula. To do this, select “Update Value with Formula” in the action type dropdown, and click on the File – Value as the value you want to update.

If you click on the ellipsis in the bottom right, you can open the formula box and see all your options. We want this formula to uncomment the line of --AND "Product Category" in ('PRODUCTCATEGORYPLACEHOLDER'). Below is the formula that will accomplish this:

Let’s talk through what this is doing. #1 stands for the value of the check box. If it’s checked, it will return TRUE and if not, FALSE. If it is checked, we are replacing a string in the Destination (input tool). The specific string it’s looking for is the line for filtering by Product Category in the WHERE clause, and replacing it with the same line, without the double dash marks, thus allowing this line to be used in our query. If it’s false, I just have the same line being replaced with itself with no changes, leaving the line commented out. I do this because I haven’t found a way to just tell the formula to do nothing if FALSE.

If you open the analytic app in debug mode and select the check box, our query now looks like this:

You can see that the Product Category line is now uncommented from the query. The last step we need to do is to replace ‘PRODUCTCATEGORYPLACEHOLDER’ with the values that the user selects. Let’s jump into that now.

I have found that the best way to start is the List Box tool. This tool will allow the user to select as many values as they need in the output. My favorite part of this is that we can dynamically query every possible option in the database, and use those to populate the list box, leaving no potential for someone to enter an invalid value and break the query!

The first thing to do is to format the tool. Enter the title for the tool, select “Generate Custom List”, then change the Start and End Text to be a single quote. The default on the tool is a double quote, but in order for the syntax in the “in” statement to be correct, we need the values separated by a single quote.

After that, we’ll choose “External Source” in the List Values dropdown. You’ll notice that it says it must contain name and value fields. We can take care of this right in the SQL query. In properties, choose the connection to the DB that stores the data and write a SELECT DISTINCT statement for the value you want to populate the list box. For this example, I’m using Product Category. To take care of the Name and Value requirement, I simply repeat the select product category with a different alias on each line as shown below.

Now, all we have left to do is connect the list box to the input using an action tool. The update we need to make here is very simple. We will just check the Replace a specific string box, and type in 'PRODUCTCATEGORYPLACEHOLDER'. This tells the action tool to take our formatted list of user-selected values, and stick it inside the “in” statement.

The last thing we will do is do a little formatting on the interface designer. I want the product categories to only show if a user selects the check box signifying they want to filter by that dimension. To accomplish this, click on the product categories box in the interface designer, then click the up arrow on the right. This will nest it within the Product Category check box, and since we checked “Collapse Group When Deselected” earlier in the process, it will hide the list of product categories.

And that’s it! We now have a workflow that can dynamically update a SQL query based on a user’s input. You can replicate this strategy across any dimension that a user would want to filter an extract. There are endless uses for the data after the query has been altered by the user using all of Alteryx’s groups of tools. You can create a workflow that will make formatted sales reports, custom emails, statistical models, and just simple excel extracts depending on what is needed by the end-user. And since you’re setting all of this up for anybody to run, you also unlock an efficient way for users to get insightful analytics at the push of a button.