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:

http(s)://yourserver.yourdomain
/plugins/servlet/tempo-getWorklog/?dateFrom=2012-01-01&dateTo=2012-12-31&format=xml
&parameter=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.

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

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!

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'

15 Responses

  1. […] this series, we have already explored how to create Excel exports using high-level permissions. The next two tutorials will explore what kinds of data can be generated into custom reports using […]

  2. Martin says:

    Excel can effectively choke when there is too much data in the XML. We’ve encountered issues doing custom reports in XML or CSV with some of our larger clients, especially when trying to get an annual view where we would need to analyse a few thousands issues and tens of thousands of hours in worklogs.

    We’re currently planning and testing BIRT (http://www.eclipse.org/birt/phoenix/) as a reporting engine. So far it is very promising.

  3. Logi Helgu says:

    I’d love to have the “parent summary” field in the export…any chance of getting that field in there?

  4. Marl Jons says:

    How’s things, I just seen that occasionally this webpage renders a 403 server error. I thought you would be keen to know. Regards

    • Bjarki Bjarki says:

      Hi Marl,

      Things are great, regarding the 403 errors you’ve been receiving they’ve been appearing because of changes on our DNS host. However that matter should be fixed now. If you’re still running into this problem then please contact our support team, support (at) tempoplugin.com with more details.

  5. […] Availing features in the realm of Excel platform, such as the drop down lists, pivot tables, and other amazing controls do not lock or limit your spectators to just single view. Moreover, the marvelous Excel workbook consists of various worksheets, so users have the opportunity to add or make notes of their unique data. This way, it leads to better collaboration between multiple users for perfect data analysis and reporting. […]

  6. Reid says:

    Is there a way to include the issue security level field as part of the export?

    • Bjarki Bjarki says:

      Hi Reid,

      This is not supported in our current Tempo Timesheets version. We value feedback and comments on how we can make Tempo Timeshets better, so if you have a feature request, please create an issue in our Atlassian JIRA (https://tempoplugin.jira.com/browse/JTMPO).
      Tempo product owners are reviewing all requests for Tempo products and if the request is within the scope of the roadmap it is transitioned from the Needs Triage status.
      Other users with similar requests can vote on these issues and contribute by adding comments that will help Tempo developers implement the request.

      However, You can add information fields to JIRA issues that can be exported to excel. To do this you must create a custom field. In your case your custom field can be called X and you can add information to it in the issue view. Adding a custom field is explained in detail here:https://confluence.atlassian.com/display/JIRA/Adding+a+Custom+Field. For more information on configuring fields please taka a look at: https://tempoplugin.jira.com/wiki/display/TEMPO079/Configuring+Fields+and+Properties

      Hope this helps

      Best regards,
      Bjarki

    • Bjarki Bjarki says:

      Hi Reid,

      This is not supported in our current Tempo Timesheets version. We value feedback and comments on how we can make Tempo better, so if you have a feature request, please create an issue in our Atlassian JIRA (https://tempoplugin.jira.com/browse/JTMPO).
      Tempo product owners are reviewing all requests for Tempo products and if the request is within the scope of the roadmap it is transitioned from the Needs Triage status.
      Other users with similar requests can vote on these issues and contribute by adding comments that will help Tempo developers implement the request.

      However, you can add information fields to Jira issues that can be exported to excel. To do this you must create a custom field. In your case your custom field can be called X and you can add information to it in the issue view. Adding a custom field is explained in detail here:https://confluence.atlassian.com/display/JIRA/Adding+a+Custom+Field. For more information on configuring fields please taka a look at: https://tempoplugin.jira.com/wiki/display/TEMPO079/Configuring+Fields+and+Properties

      Hope this helps

      Best regards,
      Bjarki

  7. Berkin Cetin says:

    Is there a way to not hard code the dates you want to generate reports from and only generate reports from the past week so that if you refresh the data it always brings you the past week. I know if you leave it blank it gives you data from the current period but that is more data than I need to export.

    • Bjarki Bjarki says:

      Hi Berkin,

      Unfortunately, this is not supported in our current Tempo Timesheets version. We value feedback and comments on how we can make Tempo better, so if you have a feature request, please create an issue in our Atlassian JIRA (https://tempoplugin.jira.com/browse/JTMPO).
      Tempo product owners review all requests for Tempo products and if the request is within the scope of the roadmap it is transitioned from the Needs Triage status.
      Other users with similar requests can vote on these issues and contribute by adding comments that will help Tempo developers implement the request.

      Best regards,
      Bjarki

  8. Ken says:

    I have gone into the Fields menu and enabled several custom fields I want on the XML output but the fields do not appear in the XML. What else must be done to get these fields in the XML? Thanks.

    • Bjarki Bjarki says:

      Hi Ken,

      A support ticket has been created in our customer service portal and we’ll contact you as soon as possible with a solution to your problem.

      Best regards,
      Bjarki

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