Tutorial - How to Get Your JIRA Tempo Data into Excel Pivot Report

* Note: this blog post is from 2010 and only applicable for Tempo on server. Please check out our more recent documentation for API integration

This tutorial walks through the steps necessary for enabling Tempo services, creating an XML export, and then importing into Microsoft Excel. The process allows a user to create custom reports and views of both JIRA and Tempo data. This can of course be done with other tools as well, and if you have experience with these steps, we would love to get feedback from you. Anyone who needs to analyze a project or cost breakdown from various perspectives should find this tutorial useful.

Step 1 - Enable Your IP

Start by enabling the IP address of the machine that will access Tempo services. This is done under Administration:Tempo Settings:Access Control.

Step 1: Enable Your IP

Step 2 - Configure Custom Attributes for Export

Configure what custom attributes you want to include in the XML export. You can access these options in Administration:Tempo Settings:Billing Configuration

Step 2: Configure Custom Attributes for Export

Step 3 - Define Your API String

Define your export string for use with the Excel import function through the Tempo API. More information can be found in our documentation here.

We’re going use the GetWorklogs function, and we need to define the the following URL string:

• http://jira.happyTown.com/plugins/servlet/tempo-getWorklog/?dateFrom=2010-05-01&dateTo=2010-07-01&format=xml&diffOnly=false&addBillingInfo=true

For this demo, we’re going to use only the default parameters and limit the date range to two months. Because Excel can handle only limited data, it’s a good idea to keep your parameters, including date ranges, as conservative as possible. My string might look like this:

• http://jira.happyTown.com/plugins/servlet/tempo-getWorklog/?dateFrom=2010-05-01&dateTo=2010-07-01&format=xml&diffOnly=false&addBillingInfo=true

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

Step 4 - Test Your 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.

1013110027INT-18.02010-06-09daviddavid201410v10010Employee IssuesVacationpetur0.0d811862aa2b43eef314bf71fbc7c894277a6fcbd1024610027INT-18.02010-06-09lauralaura201410v10010Employee IssuesVacationpetur0.0452828d2e084f0f0e8555267811bf0b32e2174931013210027INT-18.02010-06-10daviddavid201410v10010Employee IssuesVacationpetur0.02935b6b31b89f81e4da408fa30bd5f8a06eac82b1024710027INT-18.02010-06-10lauralaura201410v10010Employee IssuesVacationpetur0.0ee0064dbb388bac5879da9550d9c857f0c97f0c31013310027INT-18.02010-06-11daviddavid201410v10010Employee

Step 5 - Configure Excel to Handle XML Import2Table

Set Excel to handle XML importing as a table. You can view a tutorial here.

1. If the Developer tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options. In the Popular category, under the Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
2. NOTE: The Ribbon is a component of the Microsoft Office Fluent user interface.
3. On the Developer tab, in the XML group, click Import.
   
   The Import XML dialog box is displayed.
4. Paste your URL string and select XML and import.

For advanced features such as creating specific schema, see the linked tutorial above.

Step 6 - Create PivotTable

Now you should have your XML data in an Excel table. Click on Summarize with PivotTable.

Step 6: Create PivotTable

Step 7 - Create Custom Report and Views

Build your views, and generate charts or graphs. Creating Pivot Functions is not covered by this tutorial.

Step 7: Create Custom Reports and Views

Step 8 - Refresh Your Data

You can refresh your data by clicking the refresh button. If you have set specific dates, your report will always work within the specified range.

Conclusion

Excel can provide a good view of JIRA and Tempo billing data, however there might be limitations if you are importing a large volume of information. We‘d like to hear from you if you’ve found a way to improve this process or are using other tools that provide similar results.