In this page

Filtering, sorting the issues passed to the Excel template

Filtering issues

In most of the cases, you don't need a secondary filter. JQL, the query language used by Jira, is extremely flexible and allows implementing complex searches. You should just set up a saved filter in Jira, run that and export the result set.

In those cases when JQL is not sufficient, or you really need to filter the issues once again, you can do that in the template.

How? Each template contains a main loop that iterates over the issues like this:

<jt:forEach items="${issues}" var="issue">...</jt:forEach>

You should rewrite that in the following way to evaluate an extra condition and to export only if that condition evaluates to true:

<!-- only export the issues in the 'FOO' project and ignore others -->
<jt:forEach items="${issues}" var="issue" where="${issue.key.contains('FOO')}">...</jt:forEach>

Sorting issues to export those in a different order

In most of the cases, you can flexibly sort issues in JQL using ORDER BY.

If you need to sort according to some more complex logic, follow this pattern:

  1. Create a sorter class in Groovy that implements your ordering and save it as sorter-tool.groovy:
    sorter = new SorterTool()
    
    public class SorterTool {
    	public sort(issues) {
    		return issues.sort { a, b -> a.summary <=> b.summary } // sort by summary
    	}
    }
    
  2. Execute it in your template:
    <mt:execute script="sorter-tool.groovy"/>
  3. Pass the incoming collection $issues (a Velocity context parameter) to the Groovy code and iterate over the re-sorted collection like this:
    <jt:forEach items="${sorter.sort(issues)}" var="issue">

You can implement any kind of flexible sorting logic based on this example.

Sorting the comments as "newest first"

Comments for an issue are shown in ascending order by default, i.e. the most recent one is displayed at the bottom. To reverse this order:

  1. Write a short sorter class in Groovy that implements your ordering and save it as comment-sorter.groovy:
    commentSorter = new CommentSorterTool()
    
    class CommentSorterTool {
    	def descending(comments) {
    		return comments.sort { a, b -> b.created <=> a.created } // sort in reverse order
    	}
    }
    
  2. Execute it in your template:
    <mt:execute script="comment-sorter.groovy"/>
  3. Change the expression:
    <jt:forEach items="${issue.getCommentsRaw()}" var="comment">
    to:
    <jt:forEach items="${commentSorter.descending(issue.getCommentsRaw())}" var="comment">

Although the most typical examples are the issue key or summary cells, you can generate hyperlinks into any Excel cell using the example below. This allows users intuitively click issue keys or summaries to jump to the Jira page of the corresponding issue.

The default behaviour is that the issue key and summary columns will be exported as hyperlinks. To export those as static texts with no links, set these configuration variables to false in field-helper.groovy:

// field-helper-tool.groovy

def issueKeyAsLink = true
def summaryAsLink = true

In custom templates, you can use the hyperlink tag like this:

<jt:hyperlink type="url" address="${requestContext.canonicalBaseUrl}/browse/${issue.key}" value="${issue.key}"/>
<jt:hyperlink type="url" address="${requestContext.canonicalBaseUrl}/browse/${issue.key}" value="${issue.summary}"/>

Sub-tasks

An issue can return its sub-tasks using the following getter:

Collection<Issue> getSubTaskObjects()

Exporting sub-tasks

If you want to iterate over sub-tasks and export those, the iteration looks like this:

<jt:forEach items="${issues}" var="issue"><jt:forEach items="${issue.subTaskObjects}" var="subTask">${subTask.key}</jt:forEach></jt:forEach>

Exporting sub-tasks exactly the same way as top-level issues

If you want to export sub-tasks in the same way (same fields, same formatting, etc.) as the top-level issues that were passed to the export, you can use a simple trick: prepare a list by merging the top-level issues and their sub-tasks, and then iterate over the merged list!

  1. Create the Groovy script sub-task-helper-tool.groovy:
    subTaskHelper = new SubTaskHelperTool()
    
    public class SubTaskHelperTool {
    	/**
    	 * Returns the passed issues merged with their sub-tasks.
    	 */
    	public getIssuesAndSubtasks(issues) {
    		def issuesAndSubtasks = []
    		for(issue in issues) {
    			issuesAndSubtasks.add(issue)
    			for(subTask in issue.subTaskObjects) {
    				issuesAndSubtasks.add(subTask)
    			}
    		}
    		return issuesAndSubtasks
    	}
    }
    
  2. Execute it in your template:
    <mt:execute script="sub-task-helper-tool.groovy"/>
  3. Call the new method, and iterate over the merged list in the template:
    <jt:forEach items="${subTaskHelper.getIssuesAndSubtasks(issues)}" var="issue">

Tip: make sure to filter out sub-tasks from the input by using a JQL like this, otherwise those will appear twice in the export:

project = FOO AND type != Sub-task

(You can, alternatively, filter out sub-tasks while creating the merged list, but doing that with JQL is simpler.)

Exporting sub-task custom fields

Custom field values can be accessed using the $fieldHelper tool. This works the same way for sub-tasks as for top-level issues.

You can export custom field values using the custom field ID:

| Custom field (by ID)                                       |
| ${fieldHelper.getFieldValue(subTask, "customfield_10100")} |

...or using the custom field name:

| Custom field (by name)                                   |
| ${fieldHelper.getFieldValueByName(subTask, "My Field")}  |

Exporting parent issues of sub-tasks

Typically you export sub-tasks while exporting their parents, but sometimes the situation may be reversed. In that case, any field of the parent issue is accessible from a sub-task through its parentObject property.

You can, for example, get the key of the parent issue with this expression:

${subTask.parentObject.key}

Searching for issues

In addition to the issues passed to the template, it is possible to execute further JQL searches and also use those issues in your template.

Searching with JQL queries

Execute a JQL query and iterate through the results:

<jt:forEach items="${jqlSearch.searchByJql('project=FOO ORDER BY summary')}" var="issue">[${issue.key}] ${issue.summary}</jt:forEach>

Searching with saved filters

Execute the saved filter with the ID=13100 and iterate through the results:

<jt:forEach items="${jqlSearch.searchBySavedFilter(13100)}" var="issue">[${issue.key}] ${issue.summary}</jt:forEach>

Connecting to REST APIs

It's pretty easy to connect to REST API based services to include information from that data source in your Excel files.

Connecting to the Jira REST API

This example demonstrates connecting to the Jira REST API using BASIC authentication and getting an issue.

  1. Create the script called jira-rest-api-tool.groovy that implements the REST API invocation:
    import groovy.json.JsonSlurper
    import org.apache.commons.io.IOUtils
    
    def user = "admin"
    def password = "admin"
    def urlConnection = new URL("https://mysite.atlassian.net/rest/api/3/issue/DEMO-1").openConnection()
    urlConnection.setRequestProperty("Authorization", "Basic " + (user + ":" + password).bytes.encodeBase64().toString())
    def jsonString = IOUtils.toString(urlConnection.inputStream)
    
    issueFromApi = new JsonSlurper().parseText(jsonString)
    
  2. Execute it in your template:
    <mt:execute script="jira-rest-api-tool.groovy"/>
  3. Use the issueFromApi object to access the returned issue's fields in the template:
    ${issueFromApi.key}
    ${issueFromApi.fields.summary}
    ${issueFromApi.fields.status.name}
    

Connecting to external REST APIs

This is an example of calling an external REST API without authorization:

  1. Create the script called external-rest-api-tool.groovy that implements the REST API invocation:
    import groovy.json.JsonSlurper
    
    def jsonSlurper = new JsonSlurper()
    dataFromApi = jsonSlurper.parseText(new URL("https://www.foo.com/rest/1/user/123").text)
    
  2. Execute it in your template:
    <mt:execute script="external-rest-api-tool.groovy"/>
  3. If the REST API returns a user object like this, for instance:
    { "id": 123, "name": "John Doe", "email": "john.doe@example.com" }
    ...then use the $dataFromApi object to access the returned information in the template:
    ${dataFromApi.id}
    ${dataFromApi.name}
    

More on REST authentication

Some thoughts on REST authentication:

  • If you need to pull data from the running Jira instance only, prefer using our helpers and tools over the REST API. That's faster, easier and you completely eliminate the need for authentication.
  • If you are worried about using BASIC authentication, it is basically fine if used over HTTPS. If that's the case in your environment, keep it simple and just use BASIC.
  • You have full control over the user account used for making REST calls. This means, you can set up a dedicated, restricted Jira user for REST. For instance, create a user account named rest-client-account, remove all "write" permissions, only add "read" permissions for certain projects, and then use this account in REST calls.

Issue Navigator template modes

Better Excel Exporter is shipped with several variations of the issue-navigator.xlsx template, which offer further configuration options detailed below. Remember that you can flexibly use any combination of these modes, giving solutions to every kind of worksheet needs.

All fields vs. selected fields modes

Templates can either export all fields or only the ones currently visible in Issue Navigator. Technically speaking, there is a customizable logic that decides whether the export should utilize the table layout associated with the current search (JQL, saved filter) or use a table layout that contains all fields.

The default logic is simple and intuitive: if the Excel view name case-insensitively contains the word "all", then it will export all fields. You can freely change the default logic by modifying the following method in issue-navigator.groovy:

private isAllFieldsMode() {
	return viewNameContains("all")
}

private viewNameContains(s) {
	return (boolean) xlsView?.name?.toLowerCase()?.contains(s)
}

Please note that there are default views created for each combination: "current columns" / "all columns" modes for columns only, columns plus comments, columns plus worklogs, columns plus Tempo worklogs, columns plus change history. You can use all these views immediately after installing the app without any configuration.

Repeating fields vs. non-repeating fields modes

When using the issue-navigator-with-worklogs.xlsx or the issue-navigator-with-tempo-worklogs.xlsx templates, there can be multiple rows created for each issue that has multiple worklogs. For instance, if an issue has 3 worklogs added, then the issue will be exported to 3 physical spreadsheet rows: the field values will only be exported to the topmost row, and then each worklog will appear in its own row.

In some situations, this would be useful to repeat the field values for each of those 3 rows. We call this the repeating fields mode:

(default mode)
| Issue key | Priority | Worklog Author | Worklog Time | Time Spent (h) | Worklog comment        |
| FOO-1     | Low      | alice          |  21 Jan 2017 |           1.50 | First worklog on FOO-1 |
|           |          | bill           |  22 Jan 2017 |           2.33 | Second worklog         |
|           |          | alice          |  26 Jan 2017 |           0.25 | Third worklog          |
| FOO-2     | Critical | cecil          |  20 Jan 2017 |           4.00 | Worklog on FOO-2       |

(repeating fields mode: see the first two columns repeating for each worklog!)
| Issue key | Priority | Worklog Author | Worklog Time | Time Spent (h) | Worklog comment        |
| FOO-1     | Low      | alice          |  21 Jan 2017 |           1.50 | First worklog on FOO-1 |
| FOO-1     | Low      | bill           |  22 Jan 2017 |           2.33 | Second worklog         |
| FOO-1     | Low      | alice          |  26 Jan 2017 |           0.25 | Third worklog          |
| FOO-2     | Critical | cecil          |  20 Jan 2017 |           4.00 | Worklog on FOO-2       |

Since app version 2.4.0, the repeating version of the issue-navigator-with-worklogs.xlsx is available as issue-navigator-with-repeating-worklogs.xlsx among the default templates. Similarly, the repeating version of issue-navigator-with-tempo-worklogs.xlsx is available as issue-navigator-with-repeating-tempo-worklogs.xlsx.

How to use those?

  1. If you need both the default and the repeating mode, then create a new intuitively named Excel view and use the repeating template with that. For instance, add a new view name "Worklogs rep. (Current f.)" with the issue-navigator-with-repeating worklogs.xlsx template.
  2. If you need the repeating mode only, then switch your existing Excel views to the repeating version of the corresponding template. For instance, switch the "Worklogs (Current fields)" and "Worklogs (All fields)" views to the issue-navigator-with-repeating worklogs.xlsx template.

Please remember that you should use the repeating mode primarily for creating pivot tables, pivot charts, other reports and for data transfer in case the target system expects the data this way. Compared to the default mode, the repeating mode Excel files are less human-readable, require more time and more memory to generate, and larger in physical file size.

Other tips & tricks

Alternating row colors (zebra stripes)

If you want to add alternating row colors to your Excel template, just calculate the style based on the loop counter:

  1. Define the two styles in the A1 cell:
    Summary<mt:class name="odd" style="fill-pattern: solid; fill-foreground-color: blue;"/><mt:class name="even" style="fill-pattern: solid; fill-foreground-color: green;"/>
    
  2. Select the style depending on whether the loop counter is odd or even:
    <jt:forEach items="${issues}" var="issue" indexVar="rowIndex"><jt:style class="${rowIndex % 2 ? 'odd' : 'even'}">${issue.summary}</jt:style></jt:forEach>
    

You can implement more complex coloring logic (e.g. choosing the row color by the priority of the issue) based on this example.

Dynamic worksheet names

You can use expressions in Excel worksheet names, too.

For example, to include the number of exported issues, use this expression in the template worksheet name:

${issues.size()} bugs

Note that worksheet names are limited to 31 characters in Excel.

Jira Service Desk SLA status: met vs. breached

SLA time values are exported as signed number values, out of the box. Those numbers are positive if the SLA is met or negative if the SLA is breached. Therefore, to compute the status itself, add another column to your template and use a formula with the IF() function:

| Time to Resolution                                       | Is breached?            |
| ${fieldHelper.getFieldValue(issue, "customfield_10101")} | $[IF(A2>=0,"No","Yes")] |

"A2" is the cell for the Time to Resolution SLA time value, which itself is exported using the $fieldHelper tool. Obviously, an "Is met?" value could be exported as the inverse of the IF() formula in the above example.

Further reads

Unit testing

Learn more about writing unit tests to increase the quality and reliability of your Groovy scripts.

Debugging

Learn more about debugging your Groovy scripts in the IDE or in Jira.

Logging

Learn more about writing to the Jira log from your Groovy scripts.

Troubleshooting

Learn more about finding the root cause of Excel export problems faster.

Questions?

Ask us any time.