Jira categorization rules

This article describes how to create categorization rules for Jira allocation reporting. A categorization rule is an ordered set SQL queries that define categories for grouping your Jira issues. DX executes categorization rules nightly. For testing purposes, you can execute categorization rules immediately while editing.

When defining a category, you can choose between two types:

• Fixed category – Your query must return a jira_issue_id column, which represents the specific set of issues which fall under your category.
• Dynamic category – Your query must return a jira_issue_id and value column. The value column will determine the category each issue is assigned to.

Below are additional best practices for creating and editing categories rules:

• Keep categories unpublished while you’re working on them to avoid confusion.
• Test changes by clicking Process issues and then Preview to see the output.
• Order rules from most specific to broadest.
• Prefer sub-queries over hardcoded IDs.

Core database tables

Below are the main database tables you will use when defining SQL categories:

Table	Description
jira_issues	Table containing all Jira issues
jira_custom_fields	List of all custom fields
jira_issue_custom_field_values	Values of custom fields per issue
jira_issue_types	Story, Bug, Epic, etc.
jira_issue_relationships	Flattened representation of each issue’s hierarchy

Tips for working with jira_issue_relationships

Jira issues are often structured hierarchically, e.g. Initiatives > Epics > Stories. The jira_issue_relationships table contains all ancestor-descendant relationships, not just direct parent-child links.

For example, given this hierarchy:

Initiative A
└── Epic B
    └── Story C

The jira_issue_relationships table will include the following rules:

Ancestor	Descendant
Initiative A	Epic B
Epic B	Story C
Initiative A	Story C

You can use the jira_issue_relationships table to join data from any ancestor level in your categorization logic, avoid complex recursive CTEs or multiple joins, and cleanly apply fallback logic (e.g. check Story metadata, then Epic, then Initiative).

Examples

Categorize by issue type

To categorize issues based on issue type, you can define categories that group issues using the jira_issue_types table.

You can define a single Dynamic category like the one below:

SELECT DISTINCT
    jira_issues.id AS jira_issue_id,
    jira_issue_types.name AS value
FROM jira_issues
JOIN jira_issue_types
    ON jira_issue_types.id = jira_issues.issue_type_id

Or, you can define multiple Fixed categories, for example:

-- This SQL is for our "Tech debt" category

SELECT DISTINCT jira_issues.id AS jira_issue_id
FROM jira_issues
JOIN jira_issue_types
    ON jira_issue_types.id = jira_issues.issue_type_id
WHERE jira_issue_types.name IN ('Bug', 'Fix')

-- This SQL is for our "Innovation" category

SELECT DISTINCT jira_issues.id AS jira_issue_id
FROM jira_issues
JOIN jira_issue_types
    ON jira_issue_types.id = jira_issues.issue_type_id
WHERE jira_issue_types.name IN ('Feature', 'Enhancement')

Categorize by custom field value

To categorize issues based on custom field values, define categories that group issues using the jira_custom_field_values tables. You may also want to use the jira_issue_relationships the table to fallback to Epic/Initiative custom field values if a field is not set for an issue.

You can define a single Dynamic category like the one below:

WITH custom_field_ids AS (
    SELECT id
    FROM jira_custom_fields
    WHERE name = 'Example'
),
epic_issue_type_ids AS (
    SELECT id
    FROM jira_issue_types
    WHERE name = 'Epic'
),
initiative_issue_type_ids AS (
    SELECT id
    FROM jira_issue_types
    WHERE name = 'Initiative'
)
SELECT DISTINCT
    jira_issues.id AS jira_issue_id,
    COALESCE(
        issue_values.value,
        epic_values.value,
        initiative_values.value
    ) AS value
FROM jira_issues
LEFT JOIN jira_issue_relationships relationships
    ON jira_issues.id = relationships.descendant_id
LEFT JOIN jira_issues epics
    ON relationships.ancestor_id = epics.id
    AND epics.issue_type_id IN (
        SELECT id
        FROM epic_issue_type_ids
    )
LEFT JOIN jira_issues initiatives
    ON relationships.ancestor_id = initiatives.id
    AND initiatives.issue_type_id IN (
        SELECT id
        FROM initiative_issue_type_ids
    )
LEFT JOIN jira_issue_custom_field_values issue_values
    ON jira_issues.id = issue_values.issue_id
    AND issue_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN jira_issue_custom_field_values epic_values
    ON epics.id = epic_values.issue_id
    AND epic_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN jira_issue_custom_field_values initiative_values
    ON initiatives.id = initiative_values.issue_id
    AND initiative_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
WHERE COALESCE(
    issue_values.value,
    epic_values.value,
    initiative_values.value) IS NOT NULL

Or, you can define multiple Fixed categories. Here’s one example:

-- This SQL is for our "Tech debt" category

WITH custom_field_ids AS (
    SELECT id
    FROM jira_custom_fields
    WHERE name = 'Example'
),
epic_issue_type_ids AS (
    SELECT id
    FROM jira_issue_types
    WHERE name = 'Epic'
),
initiative_issue_type_ids AS (
    SELECT id
    FROM jira_issue_types
    WHERE name = 'Initiative'
)
SELECT DISTINCT jira_issues.id AS jira_issue_id
FROM jira_issues
LEFT JOIN jira_issue_relationships relationships
    ON jira_issues.id = relationships.descendant_id
LEFT JOIN jira_issues epics
    ON relationships.ancestor_id = epics.id
    AND epics.issue_type_id IN (
        SELECT id
        FROM epic_issue_type_ids
    )
LEFT JOIN jira_issues initiatives
    ON relationships.ancestor_id = initiatives.id
    AND initiatives.issue_type_id IN (
        SELECT id
        FROM initiative_issue_type_ids
    )
LEFT JOIN jira_issue_custom_field_values issue_values
    ON jira_issues.id = issue_values.issue_id
    AND issue_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN jira_issue_custom_field_values epic_values
    ON epics.id = epic_values.issue_id
    AND epic_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN jira_issue_custom_field_values initiative_values
    ON initiatives.id = initiative_values.issue_id
    AND initiative_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
WHERE COALESCE(
    issue_values.value,
    epic_values.value,
    initiative_values.value) IN ('Bug', 'Fix', 'Maintenance')