Azure DevOps categorization rules

This article describes how to create categorization rules for ADO allocation reporting. 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:

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:

-- 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

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

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:

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