Visual SQL steps

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

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

Keep in mind that steps only appear after you select Run query for your initial query.

Add query

Queries are the cornerstone for creating charts. This is where you’ll pull the data you want to show. All charts must start with an initial query. To help you remember this, all new charts always add and expand the first "Query" step.

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 there are two query modes you can choose from:

• Learn more about creating visual mode queries.

• Learn more about creating SQL mode queries.

Rename a query

Give descriptive names to your queries so it’s 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.

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. Learn more about merging queries.

You can select a different join type and—depending on that join type—select 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 available join types.

Apply formula

Apply a calculation to an existing column in column in the result set:

1. Hover over the column in the result table.

2. Select the Apply formula icon.

3. Select the type of formula you want to use: guided or custom. The available guided formulas depend on the column’s data type, but all columns allow you to use a custom formula. Learn more about guided formulas and custom formulas.

4. Select Save.

Bucket data

The “Bucket data” step takes a numeric data column and groups it into separate buckets according to the conditions that you specify. This data is then used to aggregate the data within each bucket.

The result of this step is a two-column output, with the first column representing the data buckets and the second column containing the aggregated values. To perform a “Bucket data” step:

1. Select Bucket data above the result table

2. Select the name of the column that you would like to apply the bucket step.

3. Select how you’d like to aggregate each data bucket. The available aggregations are:

   1. Count: counts how many data points are in each category

   2. Average: calculates the mean or average value of the data within each bucket

   3. Maximum: identifies the highest value in each bucket

   4. Minimum: identifies the lowest value in each bucket

   5. Sum: adds up all the data points within a bucket

4. Select the data bucket type that you want to use. The available data bucket types are:

   1. Standard deviation: size of each bucket is the standard deviation of the values in the selected column

   2. Bucket size: specify the size of each bucket

   3. Number of buckets: specify the total number of buckets you want to create

In the example shown below, the “Bucket data” step is used on the "Cycle time (days)" column with “Count” as the aggregation type. It counts the number of occurrences within each bucket, where each bucket represents a range of ten days.

Filter

Filter rows based on certain conditions.

Select where to include or exclude rows matching any or all specified filter conditions:

• Matching all conditions: Uses AND logic. Includes or excludes a row only if it matches every filter condition.

• Matching any conditions: Uses OR logic. Includes or excludes a row 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.

Forecast

Provide predictive data for a column in your query results. Select whether to add data to the existing column or create a new one, the number of rows returned, and the type of extrapolation used (cubic, quadratic, or linear).

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

Similar to the “Apply formula” step, use either a guided formula or custom formula to manipulate or add data.

Group & aggregate

Change the grouping or aggregation of columns.

This is exactly like selecting to group or aggregate a column from its aggregation menu in a visual mode query, but now you can change the type of group or aggregation 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.

Hover over the column and select the Hide column icon. Once you hide a column, it won’t show in your result table.

If you need to use hidden columns in a formula, hide the columns as the last step. Columns must be present in the result table to use in other steps, aggregations, or custom formulas.

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.

If you want to add multiple conditions for a particular case or reference multiple columns, you’ll need to use a “Formula column” step, select Custom as the formula type, then type your custom CASE statement.

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.

The starting with row number field is the offset; it helps you select a specific range of rows. For example, to include only rows 10-30, set the limit to 20 and the offset to 10.

Pivot

Turn the values in the second column of a three-column result set into column headers.

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 that data so you can use it in a chart.

Select Unsorted, Ascending, or Descending for the sort direction to change the order that the columns appear after the pivot.

Select how you’d like to aggregate duplicate group values. The available aggregations are:

• Auto: Uses either sum or concatenation, depending on the data type

• Sum

• Avg (average)

• Median

• Min (minimum)

• Max (maximum)

• Concatenate

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 select 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 columns

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

Unpivot 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 in 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

Fill in missing values in your data with zeros.

It’s important when display a time series with missing dates in a chart, or when performing calculations on columns that contain null values.

The “Zero fill” step fills in all date and numeric columns in your query results.

Zero fill example

The result set before “Zero fill” step:

The result set after “Zero fill” step: