Purpose
This report lists some details of the selected Defects in Xray, enabling them to be extracted in an Excel format. With this ability to extract the report you can use it for analysis of trends and current testing status, or process this information to generate some metrics, for example, or even share it with someone else who still needs access to Jira.
Possible usage scenarios:
- see all the Defect statuses & count for a given version
- see the linked Requirements, Tests, and Defects
- track the Defects that are taking the most time to get resolved
Output Example(s)
The template contains 4 tabs, the following tables show an example of the columns/rows you should expect.
How to use
This report can be generated from different places/contexts, including:
- Issue view screen
- Issue search page (main search page or as a bulk operation)
Learn more
Source data
In the store, you can find 2 subtypes for this template:
- "Bugsormix" - 1 or more Bug issue as well as a mix of Bug and Test Execution issues
- "Testexecs" - 1 or more Test Execution issues
Output format
The standard output format is .XLSX so you can open it in Microsoft Excel, Google Sheets, and other tools compatible with this format. From those tools, you can generate a .CSV file.
Report assumptions
The template has a set of assumptions that you have to make sure your Jira/Xray environment complies with:
- Issue types having the name: "Test Execution", "Bug"
- Bug final status being "Done" or "Resolved"
- Finished Bugs having the "Resolved date" populated
If any of these assumptions is not met, you need to update the template or the environment accordingly.
Usage examples
Export Test Executions of your project
from the Issue Navigator/Search, search by the issueType (i.e., "Test Execution") from your project (e.g., "BOOK") and then use bulk export or Export→Document Generator
project = "BOOK" and issuetype="Test Execution" order BY created DESC
Export Bugs associated with a given fix version
- from the Issue Navigator/Search, search by the release (i.e., "fixVersion") of your project (e.g., "EWB") and then use bulk export or Export->Document Generator
project = "BOOK" and issuetype= "Bug" and fixVersion=1.2 order BY created DESC
Export Test Executions and Bugs obtained in a given Environment
from the Issue Navigator/Search, search by Test Executions or Bugs assigned to that Test Environment (e.g., "chrome") and then use bulk export or Export->Document Generator
project = "BOOK" and issuetype in ("Test Execution", Bug) and environment IS chrome order BY created DESC
Export Bug or Test Execution from its detailed view
- open the Bug/Test Execution issue and export it using this template
Understanding the report
The report shows information about the Defects in a list form as well as provides summary tables.
Layout
The report is composed of 4 sheets - "ReqsImpactSummary", "DefectSummary", "DetailedDefectList", and "Input". By default, all tabs are rendered. The layout is the same regardless of the template subtype used.
"ReqsImpactSummary" sheet
This sheet presents a table listing requirements ("Story" issue type) impacted by defects from your exported issues.
| Column | Notes |
|---|---|
Story Key | Issue key of the Story |
| Summary | Summary of the Story |
| Priority | Priority of the Story |
| Component | Component of the Story |
| Total linked defects | Count of unique defect issues linked to a given requirement story |
"DefectSummary" sheet
| Column | Notes |
|---|---|
Total defects reported | Count of unique defects associated with all the items from the source JQL. Should match the number of content rows on the "DetailedDefectList" sheet. |
| Total defects resolved | Count of unique defects in "Done" or "Resolved" status |
| Total defects unresolved | Difference between the first 2 columns |
| Total found outside of testing | Count of defects from "DetailedDefectList" sheet that have "found outside of testing" value in the Source column (see the description of logic below). |
| Total found in testing | Difference between "Total defects reported" and "Total found outside of testing" |
"DetailedDefectList" sheet
| Column | Notes |
|---|---|
Key | Issue key of the defect |
| Summary | Summary of the defect |
Workflow Status | Workflow status of the defect |
| Affected Version | Affected Version(s) of the defect |
| Fix version | Fix Version(s) of the defect |
| Priority | Priority of the defect |
| Component | Component(s) of the defect |
| Environment | Environment of the defect |
| Reporter | Reporter of the defect |
| Assignee | Assignee of the defect |
| Linked Test Execution | Key for the Test Execution(s) linked to the defect (regardless of the link type) |
| Linked Test Key and Associated Latest Test Run Status | Key for the Test(s) linked to the defect (regardless of the link type) and the latest execution run status for the Test(s) |
| Linked Requirement Story Key and Summary | Key and Summary for the Story(ies) linked to the defect (regardless of the link type) |
| Linked Requirement Epic Key and Summary | Key and Summary for the Epic(s) linked to the defect (regardless of the link type) |
| Source | The value is "found in testing" if there is at least 1 linked Test, Test Execution, or Test Plan. The value is "found outside of testing" otherwise (e.g. if a bug is created directly from the story). |
| Date Created | "Created" date in the "dd-MM-yyyy HH:mm:ss" format |
| Time to fix | Difference between "Resolved" and "Created" dates |
"Input" sheet
| Column | Notes |
|---|---|
Key | Issue key of the item from the source JQL |
| Issue Type | Issue Type of the item from the source JQL |
| Summary | Summary of the item from the source JQL |
| Priority | Priority of the item from the source JQL |
| Component | Component of the item from the source JQL |
| Status | Workflow Status of the item from the source JQL |
| Assignee | Assignee of the item from the source JQL |
Customizing the report
The common customization actions are:
- adding/removing columns
- changing the level of detail in the columns
As this report is column-based, if some columns are not relevant to you, you should be able to delete them. Make sure that no temporary variables are created in the cells of those columns that are used in other subsequent columns.
Keep in mind that only 1 hyperlink will be active per Excel cell (the one associated with the first line item).
You can further finetune the content and formatting via JavaScript, you can find more useful snippets in this tutorial for Document Generator (DC, Cloud).
Exercise 1: extend the report to custom issue types
In the "bugsormix" subtype, find all instances of
#{if (%{'${IssueTypeName}'.equals('Bug')})}
and edit them to include more than 1 type with the OR (||) syntax like this
#{if (%{'${IssueTypeName}'.equals('Bug') || '${IssueTypeName}'.equals('Defect') || '${IssueTypeName}'.equals('Incident')})}
Similar changes would allow you to account for more statuses.
Performance
Performance can be impacted by the information that is rendered and by how that information is collected/processed (especially in the "bugsormix" subtype).
The number of Defects and associated items, depending on scenarios, can be considerably high, especially with CI/CD. As this report sums up quite a lot of information, please use it wisely.