Have you ever created a new Jira work type scheme, only to discover that a suitable one already existed?
When a user requests a new work type, a Jira administrator must check whether an existing scheme already contains all the required work types. Doing this manually can take time, especially when Jira has many schemes.
This Insights chart lets you select the required work types and quickly find every scheme that contains them.
It also shows:
- Exact matches
- Additional work types
- Total work types in each scheme
A simple chart that helps Jira administrators reuse existing schemes, keep Jira configuration clean, and avoid unnecessary work.
Follow these steps to create the Jira Workflows by Status chart
Open Atlassian Insights: https://YOURSITE.atlassian.net/insights.
If your site still uses Atlassian Analytics open https://YOURSITE.atlassian.net/analytics/ instead.
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 WORK_TYPE_NAME (1)
- Set the display name to “Select the work types that the scheme must contain” (2)
- Enable “Multiple select” (3) to allow users to select more than one status
- In Column (4):
- Choose the “Work item” table (5) and the “Work type” column (6) to give Insights access to all work types names in your organization:
After this step, the dropdown control displays all available work types:
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_work_types AS (
SELECT DISTINCT
wt.workspace_id,
wt.issue_type_id,
wt.name,
LOWER(TRIM(wt.name)) AS normalized_name
FROM jira_issue_type AS wt
WHERE {WORK_TYPE_NAME.IN('wt.name')}
),
selected_work_type_count AS (
SELECT
workspace_id,
COUNT(DISTINCT normalized_name) AS selected_count
FROM selected_work_types
GROUP BY workspace_id
),
scheme_work_types AS (
SELECT DISTINCT
wts.workspace_id,
wts.issue_type_scheme_id,
wts.name AS scheme_name,
wt.issue_type_id,
wt.name AS work_type_name,
LOWER(TRIM(wt.name)) AS normalized_name
FROM jira_issue_type_scheme AS wts
INNER JOIN jira_issue_type_scheme_mapping AS wtsm
ON wts.issue_type_scheme_id = wtsm.issue_type_scheme_id
AND wts.workspace_id = wtsm.workspace_id
INNER JOIN jira_issue_type AS wt
ON wtsm.issue_type_id = wt.issue_type_id
AND wtsm.workspace_id = wt.workspace_id
),
scheme_analysis AS (
SELECT
swt.workspace_id,
swt.issue_type_scheme_id,
swt.scheme_name,
COUNT(DISTINCT swt.normalized_name) AS total_work_type_count,
COUNT(
DISTINCT CASE
WHEN selected.normalized_name IS NOT NULL
THEN swt.normalized_name
END
) AS matching_work_type_count,
CONCAT_WS(
', ',
SORT_ARRAY(
COLLECT_SET(
CASE
WHEN selected.normalized_name IS NOT NULL
THEN swt.work_type_name
END
)
)
) AS matching_work_types,
CONCAT_WS(
', ',
SORT_ARRAY(
COLLECT_SET(
CASE
WHEN selected.normalized_name IS NULL
THEN swt.work_type_name
END
)
)
) AS additional_work_types
FROM scheme_work_types AS swt
LEFT JOIN selected_work_types AS selected
ON swt.workspace_id = selected.workspace_id
AND swt.issue_type_id = selected.issue_type_id
GROUP BY
swt.workspace_id,
swt.issue_type_scheme_id,
swt.scheme_name
)
SELECT
sa.scheme_name AS `Work Type Scheme Name`,
CASE
WHEN sa.total_work_type_count = swtc.selected_count
THEN 'Exact match'
ELSE 'Contains additional work types'
END AS `Match Type`,
sa.matching_work_types AS `Matching Work Types`,
COALESCE(
NULLIF(sa.additional_work_types, ''),
'None'
) AS `Additional Work Types`,
sa.total_work_type_count AS `Total Work Types`
FROM scheme_analysis AS sa
INNER JOIN selected_work_type_count AS swtc
ON sa.workspace_id = swtc.workspace_id
WHERE sa.matching_work_type_count = swtc.selected_count
ORDER BY
CASE
WHEN sa.total_work_type_count = swtc.selected_count THEN 0
ELSE 1
END,
sa.total_work_type_count ASC,
sa.scheme_name ASC;
The chart has been created successfully:
Now, select the work types you want to search:
You will find all existing work type schemes that contain the selected work types:
Less searching, faster reuse, and fewer duplicate work type schemes 😄
Have you built any Jira Admin life-saving charts in Insights?