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.
The security token is also required, and must be included in the URL, as shown further below in this post.
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.
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:
http(s)://yourserver.yourdomain
/plugins/servlet/tempo-getWorklog/?dateFrom=2012-01-01&dateTo=2012-12-31&format=xml
¶meter=true&anoterParameter=true
&tempoApiToken=yourToken
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:
http://localhost:8080/plugins/servlet/tempo-getWorklog/?dateFrom=2012-08-01&dateTo=2012-11-30&format=xml&addIssueDetails=true&tempoApiToken=9e8b61cc-6afb
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.
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.
Paste your search URL to the Address field and click the Go button to display the XML in the New Web Query dialog.
Click the Import button to import the data to Excel.
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).
Step 6 – Create a Customized PivotTable
First, click the Summarize with PivotTable link.
Next, select your table or range in the Create PivotTable dialog and then click the OK button.
Select the fields you want from the PivotTable Field List.
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.
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.
Conclusion
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!
