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

Sverrir has over over 3 years experience in JIRA consulting and is responsible for documentation and testing for the Tempo plugin team.

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 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 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 here:

https://tempoplugin.jira.com/wiki/display/TEMPO076/Tempo+API+Guide

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:
http://office.microsoft.com/en-gb/excel-help/import-xml-data-HP010206405.aspx#BMimport_an_xml_file_as_an_xml_list_wit

  1. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button 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.
    Excel XML panel
    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 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 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.

Step 8: Refresh Your Data

Step 8: Refresh Your Data

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.

Related Posts

Sverrir is the QA and Support Manager for Tempo. Computer Science graduate from the University of Iceland and has been a member in the Tempo team from the beginning. Sverrir is an active music and film collector and known as 'Iceland´s no 1 Frank Zappa fan'

4 Responses

  1. Brad says:

    Is this really the only way to get the data out? No onscreen button to click for export? Thanks

  2. […] first tutorial updates our previous tutorial on exporting Tempo data to create custom Excel reports using high-level permissions. Since […]

  3. Aron Gombas says:

    Thanks for the article, Sverrir.

    The JIRA add-on called Better Excel Plugin offers an alternative for those who need more flexibility, and less follow-up work on the resulted Excel file.
    You can export any issue fields, including Tempo managed accounts, teams, worklogs to Excel, and apply any Excel features to that (from formulas and function through conditional formatting to pivot charts). The nicety is that with this plugin you need to prepare a template Excel file (including issue lists, pivot tables, pivot charts) only once, which will be filled out with the most current data for a click any time later. It integrates really nicely with Tempo, and you can even generate and email periodical report to your mailbox.

    See: http://www.midori-global.com/products/jira-better-excel-plugin/documentation/integrations#tempo-timesheets
    Automation: http://www.midori-global.com/products/jira-better-excel-plugin/documentation/automation

    If there is interest, I’m happy to provide a guest blog post about this.

Leave a Reply

Plan, Budget, Track & Adapt with Tempo for JIRA.


Subscribe to our blog

Get the latest posts delivered to your inbox:

Thank you for signing up!

Please check your email to confirm your subscription.

Select the Tempo products you'd like to hear about:

Tempo Planner Tempo Budgets Tempo Timesheets