Automate a daily Jira status report email to your managers

In an enterprise organization using a mix of software tools is just business as usual. It's also very common to use inherited legacy tools just because it's the platform that has always been used.

Using multiple tools does not only create friction in collaboration, but it also doesn't help in reporting about the state of work. Management is only interested in the big picture, which can only be pieced together by bringing information together from each tools.

The problem: data consolidation and reporting

MoroSystems, an Atlassian Platinum Solution Partner, took on a project at a large telecom company in Europe to consolidate worklog reporting from different systems into a central Jira instance. This data set is then used to automatically produce and email a custom business report to management by their regular morning meeting.

Martin Bayer of MoroSystems led the consultant team. "We've been working with our client, a market-leading telecom company for more than 3 years now" - explains Martin. "We've been working with their subsidiary in the Czech Republic, where more than 5 million subscribers use their mobile and landline services every day."

What should a daily project status report include?

The executives needed a project status report delivered for their morning status meeting so they can have an overview of all the past, ongoing and planned projects. They didn't want to login to any system and wanted a flexible solution, so they asked for an Excel report.

Excel brings versatility and it can handle a lot of data if needed. Excel also provides the freedom to extract any part of the data and reuse it in any form.

They asked for a Jira Excel report with specific parameters and content:

  1. The report must list the biggest projects by estimated work time investment in a (filterable) time period and their billability.
  2. The report must communicate the length of projects delivery from the date of approval to the acceptance.
  3. The report is to contain the total number of running, delivered and unfinished projects in the organization and their details.
  4. One should be able to read the total number of capitalized and operational activities assigned to teams .
  5. Finally, the report must have the planned and logged work ratio by the department or type of activity.

The solution

Worklog report with multiple data sources

Most of the above items revolve around metrics derived from accurately logged work time.

The MoroSystems team found that Tempo Timesheets provides the most user-friendly interface for time logging which requires minimal effort from users. On top of that, Tempo Timesheets has a feature to define Teams and Roles for different users. It helped to recreate the organizational structure in the final report and also to calculate custom hourly rates based on project roles.

It would have been enough input for a project report, but at this company, people are logging hours in multiple systems, not just Tempo Timesheets. That's why they needed a well-designed solution with a larger scope to report on project status.

Integrating the data sources

Martin and his team started working on an integration of these systems that are used for work time logging, like another Jira instance, SAP, and other CRM systems. They decided to create a custom app that pulls and cumulates the worklogs from all these different systems into Jira.

A custom app pulls the worklog information from different systems into Jira and prepares it for exporting to Excel

Once they brought all worklog data under one roof inside Jira, they would create an Excel report that fulfills the above-listed requirements of the daily report. After this, the report just needs to be distributed automatically every morning.

Read on to learn how the team automated the delivery of reports to the management!

Selecting the right app for Jira to Excel export

The team started out using ScriptRunner to export to CSV format from Jira, but then the customer kept adjusting their needs and ask for a more complex report.

"The requirements changed so much, that we simply weren't able to generate it without killing Jira or slowing it down for everyone" - says Frantisek Spacek, a member of the consultant team.

They searched for a Jira app that exports data through prepared and reusable templates, that doesn't wear Jira down all the time.

Our original idea was to use the Xporter app because that provides templates. "However, we encountered problems with data preparation" - remembers Frantisek. "Xporter had nice templates for simple Excel exports, but it doesn’t support extensive scripting for a complex enterprise use case, where one app must communicate with other tools in the company. We couldn’t pull all that huge amount of data prepared by our custom Jira app into an Xporter Excel template."

To solve this problem, a robust, scalable and flexible reporting solution was needed that supports custom scripting. Nice and simple templates were not enough here, this use case needed real power.

"When our app finished collecting all information for the day's report, it had tens of thousands of records in the database. We had to find a reliable app to export those to Excel, so we started to evaluate the Better Excel Exporter for Jira" - Frantisek continued.

"The main reason for eventually choosing Better Excel Exporter for this project is the direct support of Groovy scripts and that this app was built to handle large amounts of data. Our own app is now only used for displaying and preparing data coming from multiple sources and it calls the Better Excel Exporter API to generate the Excel reports."

"The reports include the state and length of projects, the phase that projects are currently in, how much time is allocated for specific projects, how much time is spent on projects, the state of tasks and other custom fields."

Illustration of the Jira project status report created by MoroSystems for management meetings

"Thanks to the "Allowed for Group" and "Allowed for users" feature in Better Excel Exporter, it's easy to set which users or user groups have the permission to access specific reports. This restriction also decreases the server workload to a minimum."

Automating daily Jira status reports

Automated delivery of the Excel reports is the final step of the solution.

Frantisek's team built a custom Groovy script which uploads the generated Excel reports automatically to Confluence, then sends out email notifications.

Collecting all these data from a mix of platforms and further processing for an Excel report is a pretty resource-intensive operation. The MoroSystems team avoids Jira performance degradation by using CRON triggers to run these report generation and distribution services outside of busy work hours.

This way, an email lands in the inboxes of members of the management every morning with a link to the latest project status report.

Start creating worklog reports from Jira

When Frantisek and his team discovered Better Excel Exporter, they didn’t hesitate too much: I recommend you just go for it and try it out!

Even though there are a lot of apps in the market, it's impossible to find any similar one which has the same amount of great functions and possibilities to offer.

As Frantisek explains, "the biggest benefit of Better Excel Exporter is its versatility. It can work with prepared templates but at the same time, it's designed to use custom Groovy scripts during data generation. This gives us the possibility to create a library of scripts containing the most often used commands for obtaining the data from any part of the system."

Try to embrace the maximum of what Better Excel Exporter app provides in case of automation options: it integrates perfectly with ScriptRunner or you can just use the free Better Excel Automation app. It offers many new possibilities for automation without the necessity to know ScriptRunner or scripting at all.

Email Jira status reports automatically!

 

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

Subscribe now