<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=825597734225432&amp;ev=PageView&amp;noscript=1">

Creating Excel Reports Using High-Level Permissions API Export

This tutorial is the first in a four-part blog series on exporting Tempo data to create customized reports. This has been a highly requested feature, and one that has generated a good amount of feedback and requests for further information from our users.

Through this series, we will explore how to create Excel exports using high-level permissions; how to do the same using limited permissions; what kinds of data can be generated into customized reports using Excel; and how to create Excel reports using a Mac or something other than Microsoft Office.

This first tutorial updates our previous tutorial on exporting Tempo data to create customized Excel reports using high-level permissions. Since it's been a while, we've used an updated version of Microsoft Office with updated screenshots. This tutorial walks you through the steps necessary for enabling Tempo services, creating an XML export, and then importing into Microsoft Excel. This process enables users to create customized reports and views of both JIRA and Tempo data. Those who require the ability to analyze projects and/or cost breakdowns from various perspectives should find this functionality useful. (We'd love to get feedback from you on how you find the ability to use this data to better help your decision making!)

Step 1 – Enable Your IP Using Tempo Access Control

JIRA administrators will need to start by enabling the IP address(es) of the machine(s) that will be able to access Tempo services. Admins can add IP addresses to the list of Allowed addresses using Tempo Access Control.

Users' IP addresses must be included in the list in order to use the GetWorklogs function in the Tempo API. The Account Manager needs to be enabled with at least one Account configured.

Tempo Admin Services Access IP

The security token is also required, and must be included in the URL, as shown further below in this post.

Tempo Admin Security Token String

Step 2 – Configure JIRA Custom Field(s) for Export

If you would like to include JIRA custom fields to your XML export, you will need to select them from the Tempo Administration's Fields section, as shown below.

Tempo Custom Fields to XML

Step 3 – Define Your API String

Select the date range and additional parameters to the URL that will be used to receive your data. An example string and a list of available parameters can be viewed on our API documentation page.

For purposes of this demo, we’ll use the GetWorklogs function, and the URL string will consist of the following:


We'll also use only the addIssueDetails=true parameter, and will limit the date range to four months.

Here's what the string looks like:


NOTE: If you leave out the dates, Tempo will return the current period. This might be useful, however, as you can always refresh the report in Excel for live data.

Step 4 – Test Your API String in a Browser

Test the string in a browser to see if the service is returning your data. It should look something like the output below, and will potentially be quite long.

API XML Example

Once you’ve seen your data output, you can move on to the next step.

Step 5 – Import Your Data

The examples below apply to Excel 2010. If you are using an older version, please take a look at our previous tutorial on exporting Tempo data.

Open Microsoft Excel and click the Data tab. Then click the From Web button to import your data.

Import from Web

Paste your search URL to the Address field and click the Go button to display the XML in the New Web Query dialog.

Import from web dialog

Click the Import button to import the data to Excel.

Import from web import data

Your data should now be displayed in an Excel table. On this worksheet, you can summarize the data according to your needs by creating a PivotTable (see below).

import from web Excel

Step 6 – Create a Customized PivotTable

First, click the Summarize with PivotTable link.

Import from web pivot

Next, select your table or range in the Create PivotTable dialog and then click the OK button.

Import from web create pivot

Select the fields you want from the PivotTable Field List.

Import from web pivot fields

Step 7 – Create Custom Reports and Views

Now you can build your views, generate charts or graphs, and analyze your data. In the example below, three fields were selected from the PivotTable Field List: Hours, User Name, and Project Category.

Import from web import data pivot

(info) Creating Pivot Functions is beyond the scope of this tutorial. However, you may want to refer to this tutorial from Microsoft for further information.

Step 8 – Refresh Your Data

You can refresh your data by clicking the Refresh All button. If you have selected specific dates, your report will always work within that specified date range.

Import from web refresh


Excel can provide an excellent overview of both JIRA and Tempo data. However, there may be some limitations, depending on the volume of data you are working with. We‘d love to hear from those of you who have discovered a solution for processing and analyzing large amounts of data, or are using other tools that provide similar results. Feel free to leave further questions and feedback below!