Sprint Pack help

Use Sprint Pack data in Power BI, Excel and other BI tools

Sprint Pack can publish each board’s sprint totals into a board property inside Jira. BI (Business Intelligence) tools and spreadsheets read it through Jira’s REST API with credentials you already control. Nothing leaves Atlassian: the app has no server of its own and never sends your data anywhere.

What is published

This statement appears wherever you publish, word for word:

Publishing writes a board’s sprint totals to a property on that board. Anyone who can see the board can read it through Jira’s REST API. Totals count only issues the app can see. Issues with a security level that excludes the app are left out, so published totals can be lower than Jira’s Sprint Report. No issue keys or names are published. Unpublish before uninstalling.

In the rollup property (sprint-pack-rollup), per sprint: counts and estimates for committed, added, removed, completed, completed from the commitment, not completed, carried over, completed outside the sprint and re-estimated; the estimate change totals; anomaly flags; the sprint’s name, dates and goal. Per board: its project, labels, unit and notices. Definitions: how the numbers are counted.

In the burndown property (sprint-pack-burndown), for the active sprint and the last 12 closed sprints: one row per calendar day with remaining, completed, added, removed, scope and ideal in the board’s unit, the same as issue counts, and partial on today’s row of an active sprint. An active sprint’s rows run to its planned end: the days after today carry the ideal line only, with the other values empty. Plus the working-day settings the ideal line used. About 1 KB per two-week sprint. See burndown.

Refreshed by the app: after sprint changes, at most every 15 minutes for the active sprint. The board panel says when the BI copy was written and whether it’s up to date, has a refresh queued, or will refresh when sprints finish computing.

Who publishes:

Unpublish before uninstalling. Board properties survive an uninstall. Use Unpublish all on the admin page first. On a site with many boards the deletion carries on in the background, and the page shows how many boards are left. Uninstall once it says It’s safe to uninstall. The app also tries to delete every published property as it’s uninstalled, but that step has about a minute, which may not be enough on a site with many boards. Copies already loaded into BI tools stay there.

Who can read it

Anyone who can see the board can read its published property through Jira’s REST API. The property can’t be filtered per reader.

Whose view the published totals count

Published totals count the issues the app can see; the views inside Jira count what each viewer can see.

Surface Counts Label
Sprint scope views Issues the viewer can see “Counted as you see them”
Exports Issues the person exporting can see “As visible to you on "
Published board property Issues the app can see "countedAs": "app"
Issue lists through search Issues the reader can see countedAs: "reader"

So a viewer who can’t see every issue sees lower numbers in Jira than in the published data. The view then shows the published totals beside theirs (“Published to BI: 18 committed”). See whose view is counted.

Issue security

On a site with issue security levels, the admin page says whether Sprint Pack can see secured issues:

Boards that span several projects

A company-managed board’s filter can cover several projects. Published totals then include issues from all of them, as the app sees them, and anyone who can see the board can read those totals. The publish panel warns you and names the projects before you publish.

Only the board’s own location (the project it lives in) is published as projectKey and projectName. The other projects its filter covers aren’t.

Sprints created on another board

A board can show a sprint that was created on another board, for example when two boards share a filter. Each sprint is published once, under the board it was created on (its boardId is that origin board), with all its figures there. It isn’t repeated under the other boards that show it. To get its figures, publish the board it was created on.

When the license lapses

Published data is frozen, never deleted. While Sprint Pack’s license isn’t active, it stops refreshing the published properties. They keep the data as of the last refresh, and each board’s index gets a notice saying so, with the date. Readers show it in Boards[notices] (the notices column of the boards table). Publishing resumes on renewal, and the notice goes with the next refresh. See the license.

Set up a service account

A service account is a reader you can name and revoke. It doesn’t take a Jira seat, and each organisation gets 5 free. You need org admin access in admin.atlassian.com. Allow about 15 minutes, and do steps 3 and 4 in one sitting: Atlassian shows each secret once.

  1. Create the service account. Directory → Service accounts → Create. Note its email address (it ends in @serviceaccount.atlassian.com). It’s not a secret.
  2. Let it see the boards. Give it Jira access, and add it to a group that can browse the projects of the published boards. On a company-managed board, the board’s filter must be shared with that group or the project. The service account sees exactly the boards that group sees.
  3. Choose the scopes. The credential screen lists hundreds of scopes with similar names. Search for and select exactly these three:

    read:board-scope:jira-software
    read:board-scope.admin:jira-software
    read:project:jira
    

    read:board-scope.admin:jira-software is the easy one to miss: without it the boards are listed but every read of the published data fails with “scope does not match”. It’s read-only, despite “admin” in its name. No write scope is needed.

    Only if this reader will also read issue lists (see issue lists), add a fourth: read:jira-work. It lets the account read every issue it can browse, so leave it out for a reader that needs only the totals.

  4. Create the credential, and save everything before leaving the screen.
    • Scoped API token (for Power Query in Excel or Power BI, the script and Google Sheets): save the token. It’s used with the service account’s email from step 1, not yours.
    • OAuth 2.0 client credential (for the script): save both the client ID and the secret. The client ID is shown only once too.
    • Put them straight into your password manager or where the reader will use them (Power BI’s credential dialog, the OS keyring for the script). Not a text file or a chat.
  5. Test it before you hand it over, with the script. Its boards.csv lists every scrum board the account can see, published or not; a missing board is one it can’t see (step 2).
  6. Record the expiry. A scoped API token expires (at most a year). Enter the date on the Sprint Pack admin page: from 30 days before, a notice with the date (nothing else) appears there and in the published data’s notices. OAuth client credentials don’t expire until revoked.

If it doesn’t work:

What the reader sees Cause Fix
401 “Unauthorized” Wrong email for the token (often the admin’s own), or a revoked or mistyped token Use the service account’s @serviceaccount.atlassian.com email with its token
401 “Unauthorized; scope does not match” A scope is missing, most often read:board-scope.admin:jira-software Create a new credential with the three scopes in step 3
401 on the site address (your-site.atlassian.net) Scoped tokens work only through api.atlassian.com/ex/jira/{cloudId} Use the “Scoped API token or service account” snippet
The script says “token request refused (403: client is disabled)” The OAuth credential was revoked Use another credential, or create a new one (steps 3–4)
A board is missing The account can’t see it: no browse permission, or a filter not shared with its group Step 2
A board shows published = false The board isn’t published A Jira admin, or the board’s admin if allowed, publishes it

Excel and Power BI (Power Query)

Open the board’s “Use in Power BI / Excel” panel in the Sprint scope view (or “Show setup for BI tools” on the admin page), choose your kind of token, copy the Power Query (M) snippet into a blank query, and sign in with Basic authentication.

Kind of token Address Sign in with
API token without scopes Your site, https://your-site.atlassian.net Your Atlassian email and the token
Scoped API token or service account https://api.atlassian.com/ex/jira/<cloud ID> The account’s email and the scoped token

One query, six tables. The snippet gives the same tables and columns as the script and Google Sheets: Boards, BoardGroups, Sprints, SayDo, EstimateDrift and Burndown. Issue lists aren’t included; see issue lists.

  1. Paste the snippet into a blank query (Power BI: Get data → Blank query → Advanced editor; Excel: Data → Get Data → From Other Sources → Blank Query → Advanced Editor) and name the query SprintPack.
  2. Don’t load SprintPack itself. In Power BI, clear its Enable load. In Excel, use Close & Load To → Only Create Connection.
  3. For each table you want, right-click Table next to its name → Add as New Query. Or make a blank query that says = SprintPack[Sprints].

Measures in Power BI

Compute say/do and the other ratios as a ratio of sums, over closed sprints, leaving flagged sprints out and never mixing units. That’s how the views and the dbt package count. An average of the per-sprint sayDo column gives a two-point sprint the same weight as a forty-point one; use the column only in tables of single sprints.

Copy these measures into the Sprints table (Modeling → New measure):

Say/do =
VAR Counted = FILTER(Sprints, Sprints[state] = "closed" && ISBLANK(Sprints[anomalies]))
RETURN
    IF(
        DISTINCTCOUNT(Sprints[unit]) > 1,
        BLANK(),
        DIVIDE(SUMX(Counted, Sprints[points_completedFromCommitment]), SUMX(Counted, Sprints[points_committed]))
    )

Say/do (issues) =
VAR Counted = FILTER(Sprints, Sprints[state] = "closed" && ISBLANK(Sprints[anomalies]))
RETURN DIVIDE(SUMX(Counted, Sprints[issues_completedFromCommitment]), SUMX(Counted, Sprints[issues_committed]))

The others follow the same pattern as Say/do, with the same unit check:

Measure Numerator (SUMX(Counted, …)) Denominator (SUMX(Counted, …))
Velocity ratio Sprints[points_completed] Sprints[points_committed]
Scope change Sprints[points_added] + Sprints[points_removed] Sprints[points_committed]
Committed, completed (totals) Sprints[points_committed], Sprints[points_completed] none

Get the files

The script (sprint_pack_bi.py), the dbt package and the Fivetran connector are downloads, not part of the Jira app.

The setup panel and “Send to your BI team” generate everything you paste (the Power Query snippet, the Google Sheets script and the command lines), so those routes need no download.

The script

sprint_pack_bi.py (Python 3, standard library only) writes the published tables as CSV and JSON, for Fabric notebooks, dbt, warehouses and scheduled jobs. The secret comes from an environment variable or the OS keyring, never the command line:

JIRA_API_TOKEN=… python3 sprint_pack_bi.py --site https://your-site.atlassian.net --email you@example.com --out ./sprint-pack
JIRA_API_TOKEN=… python3 sprint_pack_bi.py --cloud-id <cloud id> --email <name>@serviceaccount.atlassian.com --out ./sprint-pack
SPRINT_PACK_CLIENT_SECRET=… python3 sprint_pack_bi.py --cloud-id <cloud id> --client-id <client id> --out ./sprint-pack

To keep the secret off the screen and out of shell history, store it in the OS keyring (pip install keyring) while it’s still shown in admin.atlassian.com. This asks for the secret first, then the client ID or email it belongs to:

python3 -c "import getpass, keyring; s=getpass.getpass('Secret or token: '); u=input('Client ID or service account email: ').strip(); keyring.set_password('sprint-pack', u, s); print('stored for', u)"

Google Sheets

The setup panel generates an Apps Script for your kind of token. It fills one tab per table and refreshes on a timer.

  1. In your sheet: Extensions → Apps Script. Paste the script and save.
  2. Run setToken() once. It asks for the email and the token and stores them in your user properties, not in the script. Anyone who can edit the script can change what your trigger runs under your account, and so reach the token: give edit access only to people you’d trust with it, or use a service account’s scoped token.
  3. Run refreshAll() once and authorise it.
  4. Run installDailyTrigger() (or the hourly one).

Looker Studio

Looker Studio reads Google Sheets, so it works on top of the Sheets route. Add the sheet the Google Sheets script fills as a data source, one per tab, and build your report from those tables. Compute ratios such as say/do per board first, then compare boards with the median, as the unit rule explains.

Tableau and Qlik

Tableau (including the free Tableau Public) connects to Google Sheets and to CSV files, so use the Sheets route for scheduled refresh, the script’s CSVs for a one-off, or the warehouse if IT already runs one. The column definitions are the same as everywhere: see columns.

Qlik: a page for its REST connector will follow if customers ask for it.

A warehouse: the script on a schedule, dbt and Fivetran

Full refresh, every time. Past sprints can change after they close (a rules update, a board’s estimation field changing, a reopened sprint), so an incremental load would miss changes. The data is small; each load replaces the tables. Every row carries ruleVersion, generatedAt, generation and loadedAt, so a load can be traced, and schema.json gives typed columns.

Where the script runs:

The dbt package puts the metric definitions in one place, the same as the views:

Measure Definition Models
say_do completed from the commitment ÷ committed sprint_metrics, board_metrics (ratio of sums over the last N closed sprints), label_metrics (median and mean across boards)
velocity_ratio completed ÷ committed same
scope_change (added + removed) ÷ committed same
reestimate_rate issues re-estimated during the sprint ÷ (committed + added) same
carry_over_issues carried over ÷ (committed + added) same
burndown_daily the published burndown, typed one row per sprint and day

Point columns are never added across boards with different units: label_metrics gives point totals only when a label’s boards share a unit, and board_metrics gives unit mixed and no point totals when a board’s sprints use different units. Every mart reads only the latest load (the highest loadedAt), so rows an upserting loader keeps for sprints no longer published don’t count. IDs are text in the staging models. A test checks that sprint_metrics.say_do equals the script’s saydo table.

Join keys with Jira copies in your warehouse. sprintId is Jira’s sprint ID and boardId is the sprint’s origin board. In Fivetran’s Jira connector, sprintId ↔ sprint.id and boardId ↔ sprint.board_id. In Airbyte’s Jira source, the sprints stream carries boardId and boards carries id. Neither copies board properties, so Sprint Pack data gets there only through the script or the Fivetran connector. Check the column names against each vendor’s current schema before relying on them.

Join-key types. boardId and sprintId are text in every Sprint Pack table, as Jira’s REST API returns IDs. A Jira copy may store them as numbers. Cast one side so the types match, for example on BigQuery with Fivetran’s Jira connector: on CAST(sp.sprint_id AS INT64) = sprint.id, or on sp.sprint_id = CAST(sprint.id AS STRING). Whether they copy Forge app fields on issues is unverified; the issues table doesn’t depend on it.

Requests for BI data

Everyone sees “Use in Power BI / Excel” on the Sprint scope view. On a published board it opens the setup. On an unpublished board it says what publishing gives and who can turn it on, and offers Request BI data for this board.

Send to your BI team

“Send to your BI team” (admin page for all boards, or the board panel for one) builds a Markdown handoff, shown on screen and downloaded. It contains:

It never contains a credential. Before it’s produced, the app checks the generated snippets and scripts for anything that looks like a token, and the whole document for an Atlassian API token. If a board’s name or one of its labels looks like one, the handoff isn’t produced and the message names the board: rename it or remove the label.

Labels and location in BI data

Every published board carries:

Labels are current, not historical. Relabelling a board changes the labels on every past sprint of that board in BI data. groupsChangedAt says when the labels last changed. Labels are kept whether or not a board is published.

Reader Location Labels
Script, Sheets and the Power Query snippet (boards, boardgroups, and every sprints/saydo row) projectKey, projectName groups joined with ; , and boardgroups with one row per board and label
The property itself projectKey, projectName groups (a list), groupsChangedAt

For one row per label, use boardgroups (BoardGroups in Power Query).

Columns

One column contract (columns.json) defines every table and column that the script, the Sheets script, the dbt package and the Fivetran connector produce. Each column’s meaning is defined once, in how the numbers are counted, and every column in the contract links to its definition there.

Table One row per Key columns
boards board published, status, complete, pendingSprints, countedAs (always app), unit, notices, groups
boardgroups board and label label
sprints sprint issues_<bucket> and points_<bucket> for every bucket, approximate, anomalies, countedAs, dataAsOf
saydo closed or active sprint sayDo, velocityRatio, scopeChange (estimates), sayDoIssues (issue counts)
estimatedrift sprint reestimated, refinedBeforeStart, unestimatedAtStart, estimateRemoved, driftNet, driftAbsolute, timeLoggedVsOriginal
burndown sprint and day remaining, completed, added, removed, scope, ideal, the same as issue counts, partial
issues issue and bucket bucket, issueKey, countedAs (always reader)

Issue lists

The published property holds totals only. Issue lists come from Jira’s own search, under the reader’s permissions, so a reader who can’t see every issue gets shorter lists than the totals. Both routes need read:jira-work.

Field IDs differ per site. Look them up through GET /rest/api/3/field, matching schema.custom ending in /static/<key> (added-after-start, removed-after-start, times-carried-over, reestimated-in-sprint, sprint-start, sprint-end, last-added-after-start, last-removed-after-start), or run sprint_pack_bi.py --fields.

Power Query example: the issues added after the start of each sprint in the Sprints table.

let
    Site = "https://your-site.atlassian.net",
    Sprints = <the Sprints table from the snippet>,
    Search = (jql as text) =>
        let
            Body = Json.FromValue([jql = jql, fields = {"key"}, maxResults = 1000]),
            Response = Web.Contents(Site, [RelativePath = "rest/api/3/search/jql", Content = Body, Headers = [#"Content-Type" = "application/json", Accept = "application/json"]]),
            Issues = Json.Document(Response)[issues]
        in
            List.Transform(Issues, each _[key]),
    Added = Table.AddColumn(Sprints, "addedIssues", each Search("issue in addedAfterSprintStart(" & [sprintId] & ")")),
    Rows = Table.ExpandListColumn(Added, "addedIssues")
in
    Rows

More than 1,000 issues continue with nextPageToken; the script handles that, this example doesn’t.