---
title: "Jira categorization rules"
canonical_url: "https://docs.getdx.com/reports/jira-categorization-rules/"
md_url: "https://docs.getdx.com/reports/jira-categorization-rules.md"
last_updated: "2026-05-08"
---

# Jira categorization rules
This article describes how to create categorization rules for [Jira allocation reporting](https://docs.getdx.com/reports/jira-allocation/). 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:

```sql
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:

```sql
-- 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')
```

```sql
-- 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:

```sql
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:

```sql
-- 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')
```
---

## Sitemap

[Overview of all docs pages](/llms.txt)