Visual SQL steps

Visual SQL steps allow you to perform a variety of post-query manipulations in Atlassian’s analytics offering. Add other Visual SQL steps after the initial query of your chart to edit that query’s results.

Open

Visual SQL steps are located in two areas:

1. Steps that affect the entire result table are generally found directly above the result table

2. Steps that affect a single column are found when you hover over the column

Add query

Before you can show your data in a chart, you need to get some data to show. This is why every chart must start with an initial query. To help you remember this requirement, new charts have a “Query” step added by default.

Once you’ve executed your initial query, you can add a new query by selecting Add query above the result table. This opens a dropdown where you can choose to either

• create an empty new query

• copy an existing query

The query is a major part of Visual SQL and requires a bit more explanation. Learn more about pulling data using:

• visual mode queries or

• SQL mode queries

Open

Rename a query

Giving descriptive names to your queries can make it easier to identify the purpose of each query, especially when you need to edit one. To rename a query:

1. Select its current name in the "Query" step block

2. Type in the desired query name

3. Press Enter or click outside of the text field to save the new query name

Collapse a query

To collapse the query, select the Collapse icon of the query.

Open

Join

Merge the result sets of two queries by joining them together. You can’t manually add a “Join” step to Visual SQL; it only appears after you’ve merged a second query (See Merge queries). You can choose a different join type and–depending on the chosen join type–choose how many columns to join by and whether to include or exclude certain types of data from the merged result set.

• You can select the number of columns to join by for outer, inner, and left joins. Learn more about join types

• For outer joins, you can choose whether to include or exclude null match rows.

• For unions, you can choose whether to include or exclude duplicate rows by choosing either all or distinct values. If you choose to include or exclude query names, a column titled “Layer” is added to your result set; it labels each row with the query that it came from.

Apply formula

Apply a calculation to an existing column by hovering over the column and selecting the Apply formula icon. You can use available guided formulas or a custom formula to change the values in the column itself, rather than using a “Formula column” step then hiding the original column.

The available guided formulas depend on the column’s data type, but all columns allow you to use a custom formula.

Filter

Filter rows based on certain conditions. Choose to include or exclude rows matching any or all specified filter conditions.

• Matching all conditions - Uses AND logic. Row will be included or excluded only if it matches every condition.

• Matching any conditions - Uses OR logic. Row will be included or excluded if it matches one or more conditions.

Unlike the filters in a "Query" step, you can only filter by columns in your result set.

Consecutive “Filter” steps in Visual SQL are connected by an implicit AND operator. With this, you can create a chain of AND-OR filter conditions.

Regular expression matching

Any text column supports filtering by Java-style regular expressions in the “Filter” step. Matching is case-sensitive and unicode-aware by default. For case-insensitive matching, add (?i) to the beginning of your regular expression.

Formula column

Add a formula column to your query results by doing either of the following:

• Select Formula column above the result table

• Select the rightmost column labeled +Formula column in the result table

Use one of our guided formulas or a custom formula to manipulate or add data.

Group & aggregate

Change the grouping or aggregation of columns. This is exactly like choosing to group or aggregate a column from its aggregation menu in a "Query" step, but now you can change the group or aggregation type at any point in Visual SQL.

The aggregation types also include an extra option: Concatenate. This strings together grouped values into a comma-separated list.

Hide column

Hide a column in your result set by hovering over the column and selecting the Hide column icon. Once you hide a column, it won’t show in your result table.

If…then

Add a column to your result set where the values depend on whether or not any of the specified conditional statements are met. Hover over the column whose values you’d like to evaluate and select the If…then icon.

Limit rows

Limit the number of rows included in your result set. This can be useful when applied after a sort. For example, sort sales in descending order and limit rows to 10 to show the top 10 sales. Offset allows you to select a specific range of rows. For example, to include only rows 10-30, set the limit to 20 and the offset to 10.

If you’re trying to limit the rows of your initial query, you could modify the row limits in the "Query" step itself rather than adding a separate “Limit rows” step.

Pivot

If your chart has one numeric column grouped by two categorical columns, such as count of activity grouped by month and by activity type, you’ll want to pivot the data so you can use it in a chart.

Pivot tables require three columns. Your data is pivoted on the second column—in other words, the values in the second column become column headers.

• Select Pivot above the result table.

• Select Unsorted, Ascending, or Descending for the column sort direction to customize the column order in your pivot table.

• Choose the aggregation for duplicate group values; the available aggregations are Auto, Sum, Avg, Median, Min, Max, and Concatenate. Auto will choose either Sum or Concatenate, depending on the data type.

• Select Save to apply the pivot.

Rename column

Change the name of a column in your result set.

To rename a column:

1. Select its column name in the result table

2. Type in the new column name

3. Press Enter or click outside of the text field to save it

This is useful for display purposes or shortening column names for ease of use in formulas. Once you rename a column, you’ll use the new name in all future steps.

Reorder column

Quickly drag and drop columns in the result table to put them in the desired order.

Sort rows

Sort the values of multiple columns in a single step or sort only a single column’s values.

To sort the values of multiple columns in a single step:

1. Select Sort rows above the result table

2. Select a column and the sort direction to apply to it

3. Select Add sort to add another column

4. Repeat Step 3 as needed

5. Select Save to apply the sort

To sort a single column’s values:

1. Hover over the column whose values you want to sort

2. Select the Sort rows icon

3. Select the sort direction

Transpose

Switch your rows and columns.

There are no limits on the input table format, but the resulting table cannot contain more than 1,000 columns.

Unpivot

Unpivots a single-row table, turning the column headers into a single column named “Category”—basically the opposite of what the “Pivot” step does.

Select Three column result to ignore the first column and only unpivot the second to the last columns of your result set.

Unpivoting your data is useful when you have multiple single-value columns that you want to use in a visualization, such as a pie, line, or bar chart.

Zero fill

Fills in missing values in your data with zeros. It’s important when charting a time series with missing dates, or when performing calculations on columns that contain null values.

Zero fill example

The result set before “Zero fill”:

Open

The result set after “Zero fill”:

Open