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

# Azure DevOps categorization rules
This article describes how to create categorization rules for [ADO allocation reporting](https://docs.getdx.com/reports/ado-allocation/). A categorization rule is an ordered set SQL queries that define categories for grouping your ADO work items. 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 an ado_work_item_id column, which represents the specific set of work items which fall under your category.
- **Dynamic category –** Your query must return an ado_work_item_id and value column. The value column will determine the category each work item 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 work items** 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                                       |
| ----------------------------------- | ------------------------------------------------- |
| `ado_work_items`                    | Table containing all ADO work_items               |
| `ado_work_item_types`               | User Story, Bug, Issue, etc.                      |
| `ado_custom_fields`                 | List of all custom fields                         |
| `ado_work_item_custom_field_values` | Values of custom fields per work item             |
| `ado_work_item_tags`                | List of tags associated with work items           |
| `ado_work_item_relationships`       | Flattened representation of each item's hierarchy |

#### Tips for working with `ado_work_item_relationships`

ADO work items are often structured hierarchically, e.g. Epics > Features > User Stories. The `ado_work_item_relationships` table contains all ancestor-descendant relationships, not just direct parent-child links.

For example, given this hierarchy:

```
Epic A
└── Feature B
    └── User Story C
```

The `ado_work_item_relationships` table will include the following rules:

| Ancestor  | Descendant   |
| --------- | ------------ |
| Epic A    | Feature B    |
| Feature B | User Story C |
| Epic A    | User Story C |

You can use the `ado_work_item_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).

## Examples

#### Categorize by type, title, and tags

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

```sql
SELECT DISTINCT
    ado_work_items.id AS ado_work_item_id,
    ado_work_item_types.name AS value
FROM ado_work_items
JOIN ado_work_item_types ON ado_work_item_types.id = ado_work_items.work_item_type_id
```

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

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

SELECT DISTINCT ado_work_items.id AS ado_work_item_id
FROM ado_work_items
JOIN ado_work_item_types ON ado_work_item_types.id = ado_work_items.work_item_type_id
LEFT JOIN ado_work_item_tags ON ado_work_item_tags.work_item_id = ado_work_items.id
    AND ado_work_item_tags.name = 'Maintenance'
WHERE
    ado_work_item_types.name = 'Bug' OR
    ado_work_items.title ILIKE '%refactor%' OR
    ado_work_item_tags.id IS NOT NULL
```

```sql
-- This SQL is for our "Innovation" category

SELECT DISTINCT ado_work_items.id AS ado_work_item_id
FROM ado_work_items
JOIN ado_work_item_types ON ado_work_item_types.id = ado_work_items.work_item_type_id
LEFT JOIN ado_work_item_tags ON ado_work_item_tags.work_item_id = ado_work_items.id
    AND ado_work_item_tags.name = 'Roadmap'
WHERE
    ado_work_item_types.name = 'User Story' OR
    ado_work_items.title ILIKE '%feature%' OR
    ado_work_item_tags.id IS NOT NULL
```

#### Categorize by custom field value

To categorize work items based on custom field values, define categories that group work items using the `ado_custom_field_values` tables. You may also want to use the `ado_work_item_relationships` the table to fallback to Feature/Epic custom field values if a field is not set for a work item.

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

```sql
WITH custom_field_ids AS (
    SELECT id
    FROM ado_custom_fields
    WHERE name = 'Investment Category'
),
feature_work_item_type_ids AS (
    SELECT id
    FROM ado_work_item_types
    WHERE name = 'Feature'
),
epic_work_item_type_ids AS (
    SELECT id
    FROM ado_work_item_types
    WHERE name = 'Epic'
)
SELECT DISTINCT
    ado_work_items.id AS ado_work_item_id,
    COALESCE(
        item_values.value,
        feature_values.value,
        epic_values.value
    ) AS value
FROM ado_work_items
LEFT JOIN ado_work_item_relationships relationships
    ON ado_work_items.id = relationships.descendant_id
LEFT JOIN ado_work_items features
    ON relationships.ancestor_id = features.id
    AND features.work_item_type_id IN (
        SELECT id
        FROM feature_work_item_type_ids
    )
LEFT JOIN ado_work_items epics
    ON relationships.ancestor_id = epics.id
    AND epics.work_item_type_id IN (
        SELECT id
        FROM epic_work_item_type_ids
    )
LEFT JOIN ado_work_item_custom_field_values item_values
    ON ado_work_items.id = item_values.work_item_id
    AND item_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN ado_work_item_custom_field_values feature_values
    ON features.id = feature_values.work_item_id
    AND feature_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN ado_work_item_custom_field_values epic_values
    ON epics.id = epic_values.work_item_id
    AND epic_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
WHERE COALESCE(
    item_values.value,
    epic_values.value,
    epic_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 ado_custom_fields
    WHERE name = 'Investment Category'
),
feature_work_item_type_ids AS (
    SELECT id
    FROM ado_work_item_types
    WHERE name = 'Feature'
),
epic_work_item_type_ids AS (
    SELECT id
    FROM ado_work_item_types
    WHERE name = 'Epic'
)
SELECT DISTINCT ado_work_items.id AS ado_work_item_id
FROM ado_work_items
LEFT JOIN ado_work_item_relationships relationships
    ON ado_work_items.id = relationships.descendant_id
LEFT JOIN ado_work_items features
    ON relationships.ancestor_id = features.id
    AND features.work_item_type_id IN (
        SELECT id
        FROM feature_work_item_type_ids
    )
LEFT JOIN ado_work_items epics
    ON relationships.ancestor_id = epics.id
    AND epics.work_item_type_id IN (
        SELECT id
        FROM epic_work_item_type_ids
    )
LEFT JOIN ado_work_item_custom_field_values item_values
    ON ado_work_items.id = item_values.work_item_id
    AND item_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN ado_work_item_custom_field_values feature_values
    ON features.id = feature_values.work_item_id
    AND feature_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
LEFT JOIN ado_work_item_custom_field_values epic_values
    ON epics.id = epic_values.work_item_id
    AND epic_values.custom_field_id IN (
        SELECT id
        FROM custom_field_ids
    )
WHERE COALESCE(
    item_values.value,
    feature_values.value,
    epic_values.value) IN ('Bug', 'Fix', 'Maintenance')
```
---

## Sitemap

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