Overview: JIRA Query Builder

With JIRA, you can track and visualize a variety of data using the Query Builder tool in Databox. In this article, we will outline the basics of the JIRA Query Builder and work through an example based on one specific use case.

IN THIS ARTICLE

1. Custom Query Overview

add_user_popup@2x

A
  Custom Query Name (required) : In this field you can create a custom name for your query. This name will then be the metric title in the Designer tool after saving the query.
B
  Metric (required): Select a metric from your connected JIRA account, such as "Story Points," "Value Points" or "Number of Issues." This metric can be used in conjunction with dimensions in the next step.
C
  Dimensions (optional) The dimensions parameter breaks down metrics by common criteria, such as  assignee or project. The metric output is then recategorized based on the selected dimension value. You can insert the word "by" before the dimension to create the title (i.e., "Value Points by Project").

If a query has no dimension selected as a parameter, the returned metric will provide aggregate values, such as overall Story Points in the given date.
D
  Date field (required): Set a date property based on how you want the data  sorted. This could be by due date, last updated, created, resolved, etc..
E
  Date range (required) To display in the most optimal way, you will need to choose time intervals. Click on the "Date range" field and select the intervals (up to 8) that you want as options when you pull this into the databoard.
F
 Compare With (optional) : Select a date range to compare your previously selected interval to. This date range will be used for "previous line" on line and bar charts, and for calculating percent change. Options are: None and Previous period.
G
  Filter by (optional) : You can filter results by dimensions or by specific metrics. Filters can be combined using  AND, OR boolean logic. Filtered queries restrict the rows that are included or excluded in the result. Each row in the result is tested against the filter. If the filter matches, the row is retained. If it doesn't match, the row is dropped.

When you click on the  Value field of the filter, a drop down of selections will appear based on the dimension/ metric parameter you have selected. Because of limitations in JIRA's API, we can only list the first 15 dimensions as filter value options. For a workaround, check out the bottom of this article

add_user_popup@2x

2. Results

The " Data Preview" allows you to see all values that we're pulling in for the query you have created. At the bottom, you can find the total values bolded. To see data previews for different date ranges, click on the different tabs at the top of the preview.

add_user_popup@2x

3. Example

In this example, we'll go through one basic use case that highlights how to use the Query Builder to access JIRA data.

Let's say we are working on a project for a client and we want to track all of John Stone's Value Points that have been completed. To get a better understanding of those Value Points, we want to split up this data based on the project he was working on.

1
 First create a name for the custom query. For this example, we will name it "Value points Completed (by John Stone) by Project".
2
  Select the high level metric that you want to track. In this case, we want to see    Value points so we search and select it from the dropdown menu..

3
 Because we want to split the results up based on the project John worked on, we need to add a dimension parameter. In this case, it will be project.
4
 For the Date Field we will choose Created. We only want to report on this for one date range ("Last week"), and we will compare with the "previous period." 

add_user_popup@2x

5
 When we run the query, we get results a list of the Value Points for last week, split up by the project names.

add_user_popup@2x

6
 Since we want to display data for a specific assignee and project status, we need to add a few filters. Click on the + icon and select a parameter that you want to filter the data by. In this example, our first filter will recognize that the Status equals  done and the second filter will only include data where the assignee is    John Stone.

add_user_popup@2x

7
 Everything is now set up so we can run the query one final time. This is a great way to troubleshoot your custom query. In the results section, we can see aggregated data for Value Points created last week split up by project, where the assignee is John Stone and the status is set to done.

When your query is ready, click " Save query." This creates a new custom metric in Databox that is accessible in the Designer tool. 

add_user_popup@2x

After you select the correct JIRA source in the Designer, you'll be able to find your new custom metric in the Custom tab of the metric dropdown list. 

add_user_popup@2x

This will now populate the visualization type on your databoard with the custom query values .

What do you do if the filter is not listed in the Value dropdown list?

There is a limitation in JIRA's API that only allows Databox to display the first 15 result values in the filter dropdown menu. Because of this, you may sometimes be looking for a parameter that is not naturally available. 

Let's say we want to filter by "Assignee is Mark Freeman," but "Mark Freeman" isn't showing up in the list of Assignee value options. As a workaround, you will first want to run the query with "Assignee" as the optional Dimension (next to the metric). This will display all available results for the parameter "Assignee." 

add_user_popup@2x

Next, copy the name of the missing assignee (in this case, Mark Freeman), create a new filter and paste the name directly into the value field where the dropdown menu would normally appear. 

add_user_popup@2x

Select the value "Mark Freeman" from the blue dropdown item that is now displayed. This will allow you to setup the query with more than just the first 15 value results. Continue building your query and at the end you will successfully be able to filter by the value that you want. 

Still need help? Contact Us Contact Us