Learn how to add Atlassian Analytics to a site and understand what you need to query data and create charts.
Learn how to connect and manage your connected Atlassian data and external data.
Create or build off of dashboard templates to start analyzing your Atlassian data.
Learn how to create insightful charts and dashboards with your organization’s data.
Learn how to use Visual SQL to create queries and transform data for making charts.
Need feedback on data? Learn how to share charts and dashboards with teammates so they can leave comments.
Learn how to grant resource access to the people who need it.
Learn how to manage your workspace and how to monitor your workspace activity.
Atlassian Analytics uses SQLite to power the non-query Visual SQL steps. There are many core functions that you can use for strings and numeric values, but here are some of the common ones you can use for strings:
Returns the number of characters in the string
Converts the string to all lowercase letters
Converts the string to all uppercase letters
REPLACE(X, Y, Z)
REPLACE("column name", 'replace_this', 'with_this')
Replaces a character or substring in your string with another character or string
X represents the string that contains the character or string you want to replace.
Y represents the character or substring you want to replace.
Z represents the character or string that is used to replace Y.
Finds the position of a character in a string
X represents the string that contains the character whose position you want to obtain.
Y represents the character whose position you want to obtain.
If the character appears multiple times in the string, the function returns the position of its first occurrence.
SUBSTR(X, Y, Z)
Obtains a substring of the string you’re working with
X represents the string you want to obtain a substring from.
Y represents the starting position to obtain the substring (the first character position in the string is always 1).
Z (optional; can be omitted) represents the number of characters in the resulting string.
Use SUBSTR() to format a phone number
If you have a phone number column in the format 5556667777, but you want the format (555) 666-7777, you can format it using the SUBSTR() function.
Use the following substr() function in a custom formula:
Replace Phone with the name of the column containing the phone number.
Use SUBSTR() to get the domain name from an email
Here’s a more complicated example. Let’s say we want to pull the domain name from each user’s company email. We know emails follow this general format: email@example.com
To make the final SUBSTR() function a bit cleaner, let’s get two pieces of information using the INSTR() function:
The position of the @ in each email (We’ll name this column Position of @):
The position of the .com in each email (We’ll name this column Position of .com):
Now let’s figure out how to get the domain from the email. Remember that the SUBSTR() function has three parameters: X, Y, and Z.
Our X value is the column containing the email addresses, which is “Email”—that one’s easy.
Our Y value needs to be the position after the @ position:
"Position of @"+1
Our Z value needs to be the length of the domain, which we can get by subtracting our Y value from the position before the .com (in other words, (Z-1)-Y):
("Position of .com"-1)-"Position of @"
This is our final SUBSTR() formula:
Took a bit of effort, but we’re able to successfully get the domain names from each user’s email!
Was this helpful?