Making Jira to Excel reporting look simple

Guest blog post by Claudio Menetti of GetConnected.

Just recently we concluded work on a complex Jira reporting development project for a large customer, an Italian enterprise group of companies. They have locations across multiple regions of Italy and in other European countries, with more than 1000 employees and consultants. They are also leaders in the hosting and cloud services market.

If you work for a large enterprise or even a medium sized company and you are often challenged with Jira reporting, data consolidation and process optimization related tasks, I believe some of our solutions could work as time savers for you as well. Even if they are not directly applicable to your situation, I trust that sharing our achievements will inspire a new approach in your use case.

Discovering the versatility of Jira

Originally we started working with this customer more than two years ago when they installed Jira for the first time. At that time they only planned to use it for a few, non-business critical purposes.

It wasn’t long until they discovered the many aspects Jira can cover, and the usage of the tool started to pick up.

Eventually, they chose Jira for an important company-wide project: logging the work of all the employees and external T&M (Time and Material) consultants in order to achieve different outcomes, namely:

  • timekeeping for payroll
  • intercompany invoicing
  • reports supporting controllers
  • all the reports needed to obtain some tax advantages for hi-tech R&D projects
  • projects effort control and more

Our task was to enable and extend Jira to fulfill all these requirements and design a solution that is easy to adopt by all member companies of this holding company.

What we delivered is a well-constructed system built on top of Jira, with a few key apps plugged in.

We solved the basic tracking and data gathering requirements with the apps Riada Insight, Tempo Timesheets and a few others that were built internally by our development department. We needed one more component that would allow us to accommodate data from all these different sources and create Jira Excel exports and reports in a standard format.

Cross-project Tempo worklog report sample using an Excel pivot chart for data visualization

Below we highlight two of the many solutions we successfully deployed as part of this development project.

1. Creating a single Excel report from many data sources

Both Insight and Tempo Timesheets are featuring reports of their own data, but in this case we needed to bring these together with other information stored in Jira into a single, unified platform, that is easy to access for all teams.

It was also important to allow users to reuse the data sources to produce different kinds of reports. We wanted to let them select their own perspectives, filters, aggregation levels to build reports in a customized and interactive way.

We had already used Better Excel Exporter for some other projects in the past. It was, simply, the perfect tool for our goals with this customer as well. In addition, among the main stakeholders in the project were the accounting and controlling departments, that - by design - love Excel.

What we did and how:

  1. Using the power of Groovy scripts in the background, we were able to get data from Jira, Tempo and Insight through their APIs, and made it accessible in Better Excel Exporter templates.
  2. Then we were able to bring information from all these different sources into one Excel report.
  3. By adjusting the permissions in Jira, we limited who has access if these reports, as they often contain sensitive information.
  4. Then the advanced analytical tools in Excel, like pivot tables and formulas made it possible for the end users to drill down into the exported data.

2. Supporting the accounting team in intercompany invoicing

Another achievement we had was about helping the accounting department process intercompany invoices faster and with less oversight.

An intercompany invoicing process is never the simplest area of accounting, but if your customer is a group of different companies, the complexity only increases. To help manage invoices for the accounting department, we used Visual Basic scripting in Better Excel Exporter templates.

Intercompany invoicing and financial report created from Tempo Timesheets data

We used it to automatically place each incoming and outgoing invoice into separate worksheets in the Excel spreadsheet. Now when the accountants look at a worksheet, they have an easier job understanding which cost center, project, business line, item code, etc. does that specific invoice belong to.

Conclusion

It was great to take the challenge of this enterprise customer to enable Jira to be their one-stop-shop for data-rich company reporting. Thanks to the flexibility of Jira and the great domain specific products, the data gathering was possible and the complexity was hidden by these easy-to-use Jira apps.

On top of it all, the reporting layer was also possible with Better Excel Exporter, that perfectly integrates with all the other underlying apps and consolidates data coming from all sources into reusable, native Excel reports.

Make sure you discover Better Excel Exporter to solve your Jira reporting needs and reach out to us at GetConnected if you need a hand!

Boost your Jira to Excel reporting!

Learn more about GetConnected on the Midori Solution Partner page.

 

Be the first to hear about the Midori news, Jira, Confluence, Bitbucket guides, and productivity tips that accelerate your team.

Subscribe now