The problem: data consolidation for a Jira status report email
MoroSystems, an Atlassian Platinum Solution Partner, took on a project at a large European telecom company. Their task was to consolidate worklog reports from different systems into a central Jira instance. This data set is then used to create a Jira status report and automatically email it to the management. It arrives by their regular morning meeting, so they can sit in prepared.
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."
Martin and his team created a solution that collects worklog data from Jira and other sources, compiles a project status report and sends email notifications to the management.
This Jira reporting solution is in Excel, but MoroSystems delivered a similarly resourceful Jira reporting solution in PDF format, too. If PDF Jira reports would fit your use case better, read about the creative Jira portfolio reporting solution MoroSystem delivered.
Contents of a daily Jira project status report
The executives needed a Jira project status report, so they can have an overview of all the past, ongoing and planned projects. They wanted an automated report from Jira and didn't want to log in to any system. They decided to generate Excel reports from Jira as that was the most flexible and familiar platform to the end-users.
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.
Jira status report requirements
- The report must list the biggest projects by estimated work time investment in a (filterable) time period and their billability.
- The status report must communicate the length of projects delivery from the date of approval to the acceptance.
- It must contain the total number of running, delivered and unfinished projects in the organization and their details.
- One should be able to read the total number of capitalized and operational activities assigned to teams.
- Finally, the status report must have the planned and logged work ratio by the department or type of activity.
The solution: Jira worklog report with multiple data sources
The basis of the above report is accurate worklogs.
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.
Normally, it would have been enough input for a daily Jira report email. At this company, however, 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.
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 Jira report. After this, the report just needs to be distributed automatically every morning via email.
Read on to learn how the team automated the delivery of the daily Jira report email to the management!
Selecting the right app for Jira status reports
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. What we missed was 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 needed 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. We appreciate 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, current phase of projects, time allocated, the status of tasks and other custom fields."
"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 report emails
Automated emails sending the Excel status 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.
They set up the system to send an email to the management every morning with a link to the latest Jira 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!"
There are a lot of apps in the market, yet it's hard to find one with similar amount of functions.
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.
Embrace what Better Excel Exporter provides for automation. 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!