Every Jira Administrator knows this feeling:
"I need to find an existing workflow that includes all the statuses required for the new workflow, ideally with exactly the same statuses."
Thirty minutes and twenty workflow tabs later...
"Why do we have so many workflows? Maybe one already has the same statuses as the new one, and we can just reuse it. However, it’s just really hard to find! " 😄
That's why I built Workflows by Status in Atlassian Analytics.
Isn’t it great? Now you can easily find the workflow with the requested statuses rather than creating a new one from scratch!
Select one or more statuses, and the chart displays the workflows containing all of them.
This helps Jira Administrators:
- Easily find and reuse existing workflows instead of creating new ones.
- Find where statuses are used.
- Understand the impact of a status change.
- Support workflow clean-up.
- Avoid opening workflows one by one.
- Save time and reduce unnecessary clicks.
Follow these steps to create the Jira Workflows by Status chart
Enter Atlassian Analytics: https://YOURSITE.atlassian.net/analytics/
Click on the Create Dashboard blue button on the top-right corner:
On the dashboard, create a dropdown control that allows users to select the statuses they want to find:
In the dropdown control:
- Create a variable named STATUS_NAME (1)
- Set the display name to “Status” (2)
- Enable “Multiple select” (3) to allow users to select more than one status
- In Column (4):
- Choose the “Work item status” table (5) and the “Name” column (6) to give Atlassian Analytics access to all status names in your organization:
After this step, the dropdown control displays all available statuses:
Now, create a new chart on the dashboard:
Select the custom chart option:
Select the SQL tab option:
Copy and paste the SQL code below, then click “Run Query”:
SQL query:
SET TIME ZONE 'UTC';
WITH selected_statuses AS (
SELECT DISTINCT
s.workspace_id,
s.issue_status_id,
s.name
FROM jira_issue_status AS s
WHERE {STATUS_NAME.IN('s.name')}
),
selected_status_count AS (
SELECT
workspace_id,
COUNT(DISTINCT issue_status_id) AS selected_count
FROM selected_statuses
GROUP BY workspace_id
)
SELECT
w.name AS `Workflow Name`,
CONCAT_WS(
', ',
SORT_ARRAY(COLLECT_SET(ss.name))
) AS `Matching Statuses`
FROM jira_workflow AS w
INNER JOIN jira_workflow_status_mapping AS wsm
ON w.workflow_id = wsm.workflow_id
AND w.workspace_id = wsm.workspace_id
INNER JOIN selected_statuses AS ss
ON wsm.issue_status_id = ss.issue_status_id
AND wsm.workspace_id = ss.workspace_id
INNER JOIN selected_status_count AS ssc
ON w.workspace_id = ssc.workspace_id
GROUP BY
w.workspace_id,
w.workflow_id,
w.name,
ssc.selected_count
HAVING COUNT(DISTINCT ss.issue_status_id) = ssc.selected_count
ORDER BY
w.name ASC;
The chart has been created successfully:
Now, select the statuses you want to include:
You will find all workflows that match the selected statuses exactly:
Less clicking, faster impact analysis, and fewer existential questions about workflow design 😄
Have you built any Jira Admin life-saving charts in Atlassian Analytics?
