Creating Excel Reports Using High-Level Permissions API Export on Tempo for Data Center

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.

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.

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 Servlet API Manual page.

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

http(s)://yourdatacenter.yourdomain
/plugins/servlet/tempo-getWorklog/?dateFrom=2012-01-01&dateTo=2012-12-31&format=excel
&parameter=true&anoterParameter=true
&tempoApiToken=yourToken

We'll also use the diffOnly=false validOnly=false parameters, and will limit the date range to 3 months.

Here's what the string looks like:

http://localhost:8080/plugins/servlet/tempo-getWorklog/?format=excel&diffOnly=false&validOnly=false&dateFrom=2021-10-01&dateTo=2021-12-31&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.

Test the string with format=xml 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.

http://localhost:8080/plugins/servlet/tempo-getWorklog/?format=xml&diffOnly=false&validOnly=false&dateFrom=2021-10-01&dateTo=2021-12-31&tempoApiToken=9e8b61cc-6afb

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

The examples below apply to Microsoft 365. 

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

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

Click the Load 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).

First, click the Summarize with PivotTable link.

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

Select the fields you want from the PivotTable Fields.

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

 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!