How to create Points Committed vs Accepted reports Atl. Analytics using data from Jira Align (EI)

[dimitrios.tsiolakis]

Hi,

I need some guidance on how to create the following reports in Atlassian Analytics using data from Jira Align:

• Iteration Metrics (M1)
• Planned vs Actual Velocity

I’ve attached screenshots of the reports for reference:

• Story Point Progress by Team
• Iteration Metrics (M1) view

Could you please share the steps or any resources that explain how to build these reports? Any tips on best practices for configuring the data would also be greatly appreciated.

Thank you for your support!

Best regards,
Dimitris

[Mohsin Shaikh]

Hi Dimitris,

Here are answers to your questions.

Please note if you require specific data or suggestions on Jira Align, then its better to open a ticket with the Jira Align team.

1. “Committed” Field in EI

You are correct—Jira Align EI does not have a direct “committed” field or flag. The typical approach is:

• Committed Stories for an Iteration/Sprint:
  Use the FK Sprint ID in the Story table to identify stories assigned to a sprint at the time of extraction.

  • Limitation: This only reflects the current assignment, not whether the story was planned at the start of the sprint.

  • Best Practice:

    • If you want to approximate “committed at sprint start,” you’ll need to extract a snapshot of the Story table at the sprint start (not always feasible with EI’s current structure).

    • Otherwise, use FK Sprint ID as a proxy for “committed,” with the caveat that it may include stories added mid-sprint.

---

2. Sizing: Story Points vs. Level of Effort

• Story Points:
  Jira Align EI does not have a “story points” field.

• Recommended Proxy:
  Use Level of Effort for sizing, as it is the closest equivalent in the EI schema.

  • Level of Value can be used for value-based reporting, but for velocity and commitment, Level of Effort is standard.

---

3. Accepted Stories: Accepted Status Flag vs. State

• Accepted Status Flag:
  This is the recommended field for identifying accepted stories in EI.

  • Accepted Status Flag = TRUE means the story is accepted.

• State:
  The “State” field shows the current workflow state (e.g., In Progress, Done, Accepted), but may not always align with the “Accepted” definition used in reporting.

• Best Practice:
  Use Accepted Status Flag for consistency and to match Jira Align’s built-in reporting logic.

---

4. Fields for “Committed Sprint”

• FK Sprint ID:
  Use this field to identify the sprint to which a story is currently assigned.

• FK Team Sprint Assignment ID:
  This can be used for more granular team-level assignments, but for most reporting, FK Sprint ID is sufficient.

---

5. Sample Query Logic

Here’s a sample SQL logic for the two reports, adapted for EI’s available fields:

Iteration Metrics (Points Committed vs Accepted):

SELECT t.TeamName, s.SprintName, COUNT(st.StoryID) AS Stories_Committed, SUM(CASE WHEN st.AcceptedStatusFlag = 1 THEN 1 ELSE 0 END) AS Stories_Accepted, SUM(st.LevelOfEffort) AS Points_Committed, SUM(CASE WHEN st.AcceptedStatusFlag = 1 THEN st.LevelOfEffort ELSE 0 END) AS Points_Accepted FROM Story st JOIN Team t ON st.FKTeamID = t.TeamID JOIN Sprint s ON st.FKSprintID = s.SprintID WHERE s.SprintStartDate >= [Start Date] AND s.SprintEndDate <= [End Date] GROUP BY t.TeamName, s.SprintName

• Stories Committed: All stories assigned to the sprint (using FK Sprint ID).

• Stories Accepted: Stories where Accepted Status Flag = 1.

• Points Committed: Sum of Level of Effort for all stories in the sprint.

• Points Accepted: Sum of Level of Effort for accepted stories.

Planned vs Actual Velocity:

SELECT t.TeamName, pi.PIName, SUM(st.LevelOfEffort) AS Points_Planned, SUM(CASE WHEN st.AcceptedStatusFlag = 1 THEN st.LevelOfEffort ELSE 0 END) AS Points_Accepted FROM Story st JOIN Team t ON st.FKTeamID = t.TeamID JOIN ProgramIncrement pi ON st.FKPIID = pi.PIID WHERE pi.PIStartDate >= [Start Date] AND pi.PIEndDate <= [End Date] GROUP BY t.TeamName, pi.PIName

Let me know if you need further clarification.

Best Regards,

Mohsin

[dimitrios.tsiolakis]

Hi Mohsin,

Thank you for your response and the sample queries. I noticed that the field names in your examples (e.g., SprintStartDate, SprintEndDate, PIStartDate, PIEndDate ) do not match the actual fields in our Jira Align EI schema. 

Could you please provide sample queries using the actual field names from our EI schema? This would help ensure the logic works correctly with our data.

Thanks again for your support!

Best regards,
Dimitris

[Mohsin Shaikh]

Hello Dimitris,

Yes I understand your concern about the field names being different as we would not know your Jira Align EI schema.

Unfortunately we are not able to provide SQL queries, build custom dashboards as it is out of our support scope.

Let me know if I can be of any more help or if there are questions for me related to Analytics.

Regards,

Mohsin

[Mohsin Shaikh]

Hello Dimitris,

I hope you are doing well.

Here's some information on how to create Points Committed vs Accepted and Planned vs Actual Velocity reports in Atlassian Analytics using data from Jira Align Enterprise Insights (EI), based on the screenshots and best practices:

1. Connect Jira Align Enterprise Insights to Atlassian Analytics

Before building reports, ensure your Jira Align EI instance is connected to Atlassian Analytics:

• Prerequisites: Your org admin must work with Atlassian/Jira Align support to associate your EI instance with your Analytics workspace.

• Connect Data Source:

  1. Go to Analytics → Data sources → Add data source.

  2. Select Jira Align Enterprise Insights.

  3. Choose your instance, name it, and connect.

  4. Connect to Jira Align Enterprise Insights | Atlassian Analytics | Atlassian Support

• Templates: Atlassian provides dashboard templates for EI to help you get started.

  • Dashboard templates for Jira Align Enterprise Insights | Atlassian Analytics | Atlassian Support

---

2. Report 1: Iteration Metrics (M1) – Points Committed vs Accepted

Goal: Show, per team and iteration, the % of story points committed vs accepted, and % of stories committed vs accepted.

Structure (based on your screenshot):

• Rows: Scrum Teams

• Columns: Iterations/Sprints (with date ranges)

• Metrics:

  • Stories Committed vs Accepted:
    (Count of Accepted Stories / Count of Committed Stories) * 100

  • Points Committed vs Accepted:
    (Sum of Accepted Story Points / Sum of Committed Story Points) * 100

• Conditional Formatting:

  • Green for 100% or above, red for low percentages.

How to Build in Atlassian Analytics:

1. Data Source: Use the connected Jira Align EI data.

2. Table Visualization:

   • Rows: Group by Team (from the Teams table).

   • Columns: Group by Iteration (from the Iterations/Sprints table), display iteration name and date range.

   • Metrics:

     • Committed Stories: Stories planned for the iteration.

     • Accepted Stories: Stories with status “Accepted” or “Done” in the iteration.

     • Committed Story Points: Story points of committed stories.

     • Accepted Story Points: Story points of accepted stories.

   • Calculated Fields:

     • Add calculated columns for the % formulas above.

   • Conditional Formatting: Use Analytics’ formatting options to color cells based on thresholds.

3. Filters:

   • Filter by Program Increment (PI), team, or other relevant fields.

Best Practices:

• Ensure your EI schema is up to date and you’re using the correct fields for “committed” and “accepted” (these may be custom in your Jira Align instance).

• Use calculated fields for percentages.

• Use color formatting to highlight performance.

---

3. Report 2: Planned vs Actual Velocity (Story Point Progress by Team)

Goal: Compare planned vs accepted story points per Program Increment (PI) for each team.

Structure (based on your screenshot):

• X-axis: Program Increment (PI)

• Y-axis: Story Points

• Series:

  • Points Planned: Sum of story points planned for the PI.

  • Points Accepted: Sum of story points accepted in the PI.

• Chart Type: Grouped Bar Chart (Planned vs Accepted side by side for each PI).

How to Build in Atlassian Analytics:

1. Data Source: Jira Align EI.

2. Bar Chart Visualization:

   • X-axis: Program Increment (PI).

   • Y-axis: Story Points.

   • Series:

     • Points Planned: Sum of story points for stories planned for the PI.

     • Points Accepted: Sum of story points for stories accepted in the PI.

   • Grouping: By PI, optionally by team.

   • Legend: “Points Planned” (blue), “Points Accepted” (green).

3. Filters:

   • Filter by team, PI, or other relevant fields.

Best Practices:

• Use consistent definitions for “planned” and “accepted” (see below).

• Validate your data model:

  • Points Planned: SUM(story points) for all stories assigned to the team and PI.

  • Points Accepted: SUM(story points) for all stories accepted and assigned to the PI.

---

4. Definitions & Data Model Tips

• Points Planned:
  SUM(story points) for all stories assigned to the team and PI (not including stories outside the PI).

• Points Accepted:
  SUM(story points) for all stories accepted and assigned to the PI.

• Committed Stories: Stories planned for the iteration at the start.

• Accepted Stories: Stories with status “Accepted” or “Done” at the end of the iteration.

References:

• Dashboard templates for Jira Align Enterprise Insights | Atlassian Analytics | Atlassian Support

• Connect to Jira Align Enterprise Insights | Atlassian Analytics | Atlassian Support

• Customizing the Jira Align Enterprise Insights data source schema in Atlassian Analytics

I hope this information helps. 

Best regards,
Mohsin Shaikh
ATLASSIAN | Cloud Senior Support Engineer

[dimitrios.tsiolakis]

Here’s how you can craft a clear and professional response back to Shaikh that addresses the inaccuracies and clarifies your constraints:

---

Hi Shaikh,

Thank you for your detailed response and guidance. I wanted to clarify a few points regarding the Jira Align EI schema and available fields:

1. “Committed” Field
   The EI schema does not include a “committed” field, and there isn’t a relevant equivalent in EI. Creating a metric for “committed sprint that was actually completed” is quite challenging because this concept isn’t directly represented in the data model.

2. Filtering Stories Planned for Iteration & PI
   For filtering:

   • Stories planned for the iteration at the start: We can use the iteration assignment in the Story table, but there’s no explicit flag for “planned at start.”
   • Sum of story points planned for the PI: EI does not have a “story points” field. Instead, we have Level of Effort or Level of Value, which can be used as proxies depending on the reporting goal.

3. Accepted Field
   There is no “accepted” field in Jira Align EI. The closest is the Accepted Status Flag in the Story table, which can be used to identify accepted stories but there is also field "State". Which one is the correct.

Given these constraints, is this the recommended approach:

• Use Accepted Status Flag for accepted stories.
• Use Level of Effort for sizing instead of story points.
• For “committed,” which filed is should I use?
  

  Based on the fields available in your Story table, can these fields used for identifying the "committed sprint" of a story:

  • FK Sprint ID: This field represents the Sprint to which the story is assigned. 
  • FK Team Sprint Assignment ID: T

Quick question—do you have access to Jira Align’s built-in metrics and reports? If so, it would be extremely helpful if you could share the SQL or relevant query logic behind the reports you mentioned earlier (such as Iteration Metrics, Planned vs Actual Velocity, etc.).
Having the actual queries or logic would help ensure we’re using the correct fields and calculations, especially given the differences in available fields in the EI schema.
Thanks in advance for any help you can provide!

Best regards,
Dimitris