Jira pivot tables for non-analysts

[#1]

10 questions you can answer with Rows / Columns / Values (without exporting to Excel and crying)

Pivot tables are basically adult LEGO: you snap a few blocks together and suddenly your “messy Jira reality” turns into something you can explain to a human.

In the Time in Status app by SaaSJet, Pivot Table View is built exactly for that: generate customized reports, slice data in different ways, and go deeper than a single chart ever could.

And yes—this is a “non-analyst” guide. If you can drag fields around and resist the urge to group everything by everything, you’re already qualified.

What Pivot Table View actually is (in one breath)

Pivot tables turn raw data into summaries using three areas:

• Rows (list things vertically)
• Columns (compare categories side-by-side)
• Cells (the numeric result)

In Pivot Table View, the main controls you’ll use are Fields, Options, Format, Charts, and Export.

Two rules that save you from “pivot table soup”

Rule 1: Put “many unique values” in Rows

The docs recommend using Rows for fields with lots of unique values (Assignee, Issue Key, Summary, etc.) so the pivot stays readable.

Rule 2: Keep Columns simple

Use Columns for fields with fewer unique values (Months, Statuses, Issue Types) and avoid deep grouping in Columns—nesting too much horizontally becomes unreadable fast.

Think of Columns like a restaurant menu: more than 12 items and your brain leaves the chat.

How to build a pivot (the practical 60-second flow)

1. Switch to Pivot Table View
2. In Fields, configure Report Filters, Columns, Rows, Values using Jira work item fields.
3. Add a field to Values (numeric fields behave best). Values show as SUM by default.
4. Change the Values calculation if needed (click the SUM sign; choose another function).
5. Click Apply when your setup is ready.

Tiny but important: field order changes the answer

The docs show that Sprint → Assignee produces a different report than Assignee → Sprint. Same fields, different order, different story.

10 questions (with exact Rows / Columns / Values recipes)

1) Where is time actually going? (the #1 bottleneck)

Use when: “We feel slow” but nobody knows where.

• Rows: Status 
• Columns: (none)
• Values: Sum of your time spent in status

Tip: Use Classic layout for clarity (more on layouts below).

2) What’s our worst-case delay by status? (the “where do we get stuck for days” question)

Use when: a few tickets blow up your timeline.

• Rows: Status
• Columns: (none)
• Values: Max of your time spent in status

Make it obvious: add conditional formatting to highlight “big” values.

3) Which project is generating the most waiting time?

Use when: multiple teams share the same Jira and blame is in the air.

• Rows: Project
• Columns: Waiting statuses
• Values: Sum time spent in status

Deep thought: If Waiting time dominates, “speed up dev” won’t help. You’re looking at a queue/policy problem, not a keyboard problem.

4) Which sprints were “heavy” (and where did the time go)?

Use when: retros need more than feelings.

• Rows: Sprint
• Columns: Status
• Values: Sum time spent in status

Tip: If it becomes wide, move deeper grouping into Rows (per the “avoid deep Columns” guidance).

5) Are we improving month over month?

Use when: you need a trend without creating 12 separate reports.

• Rows: Status 
• Columns: TIS Period (Month/Week/etc.)
• Values: Sum time spent in status

The docs explicitly recommend TIS Period for reporting by different time periods.

6) Which issue types produce the biggest “time bombs”?

Use when: some issue types consistently explode.

• Rows: Issue Type
• Columns: (none)
• Values: Max time spent in status

Retro prompt: “If bugs are max-heavy, what does that tell us about discovery/testing earlier in the flow?”

7) Are estimates changing mid-flight? (historical field changes)

Use when: “Why did this story suddenly become bigger?”
The docs note pivot tables can show historical data, like how Story Points changed over time.

• Rows: Issue Type
• Columns: TIS Period (Week/Month)
• Values: Min Story Points + Max Story Points (two values)

Interpretation: Min≠Max means the field changed during the period. (Not “bad,” but very worth discussing.)

8) Who is becoming the “workflow magnet” (carefully, without turning it into a scoreboard)

Use when: work quietly piles onto one person.

• Rows: Assignee
• Columns: Status 
• Values: Sum time spent in status

Responsible use: this is for spotting process risk (interrupt handling, review bottlenecks), not rating humans.

9) Which component/label correlates with long waiting?

Use when: dependencies live inside certain components/services.

• Rows: Component (or Label)
• Columns: Status 
• Values: Sum time spent in status

Then filter down: Pivot supports multiple filtering styles (value-based, member-name, report filter).

10) “Show me the exact issues behind that scary number.”

Use when: someone points at a cell and says “THIS is the problem.”
Switch to Flat View for a raw record list (no aggregation or hierarchical grouping), which is great for drilling into details.

• Layout: Flat
• Rows: Issue Key, Summary, Assignee (whatever you need)
• Report Filters: add Sprint/Project/Status to narrow scope

Flat view is your “show me the receipts” mode.

Make it readable (Layouts + Totals)

You can:

• show/hide Subtotals and Grand totals, and
• choose compact / classic / flat layout.

My default recommendation:

• Classic when you want clarity and easy analysis
• Compact when you’re short on dashboard space
• Flat when you need raw, row-level details

Make insights pop (Format + Conditional formatting)

Pivot “Format” lets you adjust cell formats like value display, text alignment, decimal places, etc.
And you can add conditional formatting right there:

• click plus to add a condition
• pick the value + condition (>, <, =) + threshold
• then choose the formatting (font, text color, highlight)

This is how you turn a pivot from “data” into “oh… that’s the problem.”

Mini cheat: time values are in decimal hours

The Pivot Table FAQ notes time values are displayed in decimal hours, and gives quick conversion:

• Days = total hours ÷ 24
• Business days = total hours ÷ your working day length (often 8, but depends on your schedule)

Conclusion: pivots don’t make you “data-driven.” They make you argument-proof

A good pivot doesn’t win debates by being loud. It wins by being specific.

Once your team can say:

• “Time stacks here,”
• “These items are the outliers,”
• “This field changed mid-sprint,”
• “This category dominates our waiting time,”

…fixes get smaller, kinder, and more effective.

Time in Status app | Demo call