AI Assistant Prompt Examples

This page provides practical examples of AI Assistant prompts you can use with the Advanced Reports app. We tried to select examples which:

• Provide immediate business value
• Are clear illustaion of problem and solution - Standard Jira cannot do this analysis, while Advanced Reports can
• Are relatable - Solve problems that are common in teams using Jira
• Are actionable - Results directly suggest what to improve, where to provide training or support
• Can make visual impact - Results can be easily visualized in a table, heatmap, area, line, pie or scatter plot charts
• Are dashboard friendly - Results can be easily visualized in a dashboard

Prompt 1: Heatmap of delays

Show me team members whose work items are taking significantly longer than average to move through our workflow, and identify which statuses are causing the delays

Heatmap showing workflow delays by team member and status

🚫 Tricky with standard Jira

• Standard Jira can show basic cycle times, but can't easily compare individual performance against team averages
• No built-in way to identify statistical outliers or bottlenecks
• Can't easily correlate time-in-status with specific team members
• Requires complex JQL queries and manual calculations

✅ Simple with Advanced Reports

• Uses SQL to calculate averages, percentiles, and statistical comparisons
• Leverages the histories table to track time spent in each status
• Can identify outliers using statistical methods (e.g., items taking >2x average time)
• Creates clear visualizations showing bottlenecks

Example Results

• team_member: The display name of the team member (assignee).
• status_name: The name of the status where delays are observed.
• num_work_items_delayed_in_this_status: The count of individual work items for this assignee that were delayed in this specific status according to the defined threshold.
• total_days_spent_in_this_status_by_assignee: The sum of time (in days) this assignee spent across all their delayed work items in this status.
• avg_days_spent_per_item_by_assignee: The average time (in days) this assignee's delayed work items spent in this status.
• global_avg_days_for_this_status: The overall average time (in days) any work item spends in this status across all data.
• average_delay_in_days_per_item: The average difference (in days) between the assignee's time per item in this status and the global average for that status.
• ratio_to_global_average: How many times longer, on average, the assignee's items are staying in this status compared to the global average.

Results table showing workflow delays by team member and status

Scatter plot showing workflow delays by team member and status

Example SQL query generated by AI Assistant

WITH GlobalStatusAverages AS (
    SELECT
        value AS status_id,
        AVG(seconds_in_state) / 86400.0 AS avg_days_in_status
    FROM
        histories
    WHERE
        field_id = 'status'
    GROUP BY
        value
),
WorkItemStatusDurations AS (
    SELECT
        h.work_item_id,
        wi.assignee AS assignee_id,
        h.value AS status_id,
        SUM(h.seconds_in_state) / 86400.0 AS item_days_in_status
    FROM
        histories AS h
    JOIN
        work_items AS wi
        ON h.work_item_id = wi.id
    WHERE
        h.field_id = 'status'
        AND wi.assignee IS NOT NULL
    GROUP BY
        h.work_item_id,
        wi.assignee,
        h.value
)
SELECT
    u.displayName AS team_member,
    s.name AS status_name,
    COUNT(DISTINCT wsd.work_item_id) AS num_work_items_delayed_in_this_status,
    SUM(wsd.item_days_in_status) AS total_days_spent_in_this_status_by_assignee,
    AVG(wsd.item_days_in_status) AS avg_days_spent_per_item_by_assignee,
    gsa.avg_days_in_status AS global_avg_days_for_this_status,
    (AVG(wsd.item_days_in_status) - gsa.avg_days_in_status) AS average_delay_in_days_per_item,
    (AVG(wsd.item_days_in_status) * 1.0 / gsa.avg_days_in_status) AS ratio_to_global_average
FROM
    WorkItemStatusDurations AS wsd
JOIN
    GlobalStatusAverages AS gsa
    ON wsd.status_id = gsa.status_id
JOIN
    users AS u
    ON wsd.assignee_id = u.id
JOIN
    statuses AS s
    ON wsd.status_id = s.id
WHERE
    wsd.item_days_in_status > gsa.avg_days_in_status * 1.5
GROUP BY
    u.displayName,
    s.name,
    gsa.avg_days_in_status
HAVING
    COUNT(DISTINCT wsd.work_item_id) >= 1
ORDER BY
    average_delay_in_days_per_item DESC,
    u.displayName,
    s.name;

Prompt 2: SLA Breach Histogram with Moving Average

Show a histogram of SLA breaches per week, with a moving average line calculated from SQL aggregations. SLA breach occurs if work item has been open (i.e. not in done status) for more than a week

Histogram showing SLA breaches per week with moving average line

🚫 Tricky with standard Jira

• Standard Jira cannot track cumulative time spent in non-done statuses across status transitions
• No built-in way to calculate weekly aggregations of SLA breaches
• Cannot generate moving averages or trend analysis over time
• Requires complex workarounds with custom fields and manual calculations
• No native histogram visualization for time-based breach analysis

✅ Simple with Advanced Reports

• Uses the histories table to track cumulative time spent in open statuses
• Leverages SQL window functions to calculate moving averages
• Can group data by week using DATE_TRUNC functions
• Creates clear visualizations showing breach trends over time
• Provides both raw counts and smoothed trend lines for better insights

Example Results

• week_start_date: The start date of each week (Monday) when breaches are counted.
• breaches_count: The number of work items that breached SLA (stayed in open status for more than 7 days) during that week.
• moving_average_8_week: The 8-week moving average of breach counts, providing a smoothed trend line to identify patterns and seasonality in SLA performance.

Results table showing SLA breaches per week with moving average line

Example SQL query generated by AI Assistant

WITH OpenStatusTimes AS (
    SELECT
        h.work_item_id,
        SUM(h.seconds_in_state) AS total_open_seconds
    FROM
        histories AS h
    JOIN
        statuses AS s
        ON h.value = s.id
    JOIN
        status_categories AS sc
        ON s.statusCategoryId = sc.id
    WHERE
        h.field_id = 'status'
        AND sc.key != 'done'
    GROUP BY
        h.work_item_id
),
BreachedWorkItems AS (
    SELECT
        wi.id AS work_item_id,
        wi.created AS created_date
    FROM
        OpenStatusTimes AS ost
    JOIN
        work_items AS wi
        ON ost.work_item_id = wi.id
    WHERE
        ost.total_open_seconds > (7 * 24 * 3600) -- More than 7 days (604800 seconds)
),
WeeklyBreaches AS (
    SELECT
        CAST(DATE_TRUNC('week', created_date) AS DATE) AS week_start_date,
        COUNT(work_item_id) AS breaches_count
    FROM
        BreachedWorkItems
    GROUP BY
        week_start_date
)
SELECT
    week_start_date,
    breaches_count,
    AVG(breaches_count) OVER (ORDER BY week_start_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS moving_average_8_week
FROM
    WeeklyBreaches
ORDER BY
    week_start_date;