How to automatically create a release report

Today we’ll tell you how to automate the creation of release documentation based on data import from TFS, Redmine and JIRA tracking systems and from the Microsoft Project Server project management system.

The article will be of interest primarily to managers of IT-projects.

What is the problem?

The main problems were errors and inconsistencies in the design of release notes. This is the accompanying document that we send to the customer with the next release. Release notes should be as accurate as the instructions for launching a spacecraft. Every release-related bugfix, every closed user story and all user feedback information should be clearly reflected in the report. This large and important document was compiled by hand, copy-paste from tracking programs. This is where problems arose.

Problem 1. Errors.

When manually compiling a report sometimes errors occurred. Some of our projects are very voluminous and, for technological reasons, are conducted in two trackers at once (for example, part of the project for creating a mobile application is maintained in Redmine, and for creating a web application in TFS). Collecting a huge report on them - for a long time and the human factor made it difficult to make all reports 100% verified.

Problem 2. Discrepancy in design.

Some of the project managers wrote detailed comments on the release, someone sent a simple list of works. The beauty of the design also suffered as the release notes looked like a regular e-mail, although this is one of the most important letters that comes to the customer.
Gradually, we came to the understanding that the generation of release reports should be automated.

What did we get after automation?

A web designer has appeared on our internal portal, which allows collecting a ready release report in a few clicks. You log in to the application, choose a project, release, user story and task you need, press a button - and it only remains to add comments if necessary. According to project feedback, these simple actions bring a lasting feeling of happiness.

The designer is integrated with task trackers and automatically pulls all release information into a ready-made release notes. At the output, the application generates a beautifully crafted e-mail report for the customer. In it all information is divided into categories and each item is provided with a link leading to the corresponding page in the tracker.

Here is what finished finished release notes look like:

The first combat report on the released release included about 50 user stories. In a Word, such a document would have taken 11 pages. And to create it in the application took only about 2 minutes. But the main thing is not to save time, but that we are confident in the accuracy of the data.

Managerial bonuses

1. Eliminated errors in release reports.
   Now the customer promptly receives a 100 percent verified release notes.
2. Standardized release report design.
   Now our release notes look like beautifully designed html files in the corporate style.
3. Increased quality of project management in trackers.
   In order to release notes with one click of the magic button, the project manager must follow the perfect order in the tracker. A nice bonus for this is the automatic completion of a report. Of course, we started automation not for the sake of saving 1 hour of working time. The main goal was to ensure order and transparency in the conduct of projects and a beautiful correct release report.
4. Create a template for the customer, in which he can edit the release notes and send to his.
   For some customers, our html-file with the report seemed so convenient that they asked to make a similar template in which you can edit the report to send the full report or its parts with comments to new recipients.

Automation step by step

Before making our solution for automation, we looked for ready-made options. Theoretically, we could use plugins for the trackers in which we work (TFS, Redmine and JIRA) to automate the production of release notes. But the TFS and Redmine plug-ins were inconvenient. And to build a single report from two or three trackers at once, there was no ready solution at all. Therefore, we had to make our own.

Step 1. Put things in order in the trackers: they have introduced uniform standards for the project managers for whom they lead projects.

For technological reasons, our projects are conducted in three trackers (TFS, JIRA, Redmine) and the standards for managing projects in them are slightly different. To integrate trackers, we had to work on the implementation of common standards for all trackers.
In addition, it was also necessary to brush those blocks of information in the trackers, which are directly exported to release notes. The project should be broken down by releases, releases are divided into tasks and they have statuses put down, all user stories are correctly described and closed, all feedback tasks are classified, all sprints are entered. In addition, clear tasks should be given to all tasks and releases, because they will then be included in the report for the customer.

Step 2. Collected Data WareHouse based on data from trackers and MS Project Server

We built views on the databases of our tracking systems (TFS, Redmine and JIRA), also pulled up some data from Microsoft Project Server (for example, customer data). At the same time, we had to solve some problems with the integration of data from trackers. For example, for each tracker I had to find an individual solution to the problem with authorization to get data. We have already written about this in detail.

We used SQL Server Integration Services (SSIS) to collect data.

Data from the three tracking systems (TFS, Redmine, JIRA) and Microsoft Project are collected in a single data warehouse Data Warehouse.

First, download data from Microsoft Project.

SELECT proj.PROJ_NAME AS ProjectName ,proj.PROJ_UID as ProjectUID ,CT.Customer ,PCC.CostCenter ,PP.ProjectAccount ,pr_owner.RES_NAME AS ProjectOwner ,pr_owner.RES_UID as OwnerResourceNameUID ,CASE WHEN PPS.ProjectStatus IS NULL THEN 'Undefined' ELSE PPS.ProjectStatus END AS ProjectStatus FROM (SELECT PROJ_UID ,PROJ_NAME ,WRES_UID FROM pub.MSP_PROJECTS UNION SELECT 'E38038FA-F8CA-47D1-BFD4-6B45B8462972' AS Expr1 ,'Administrative' AS Expr2, NULL AS Expr3) AS proj LEFT OUTER JOIN pub.MSP_RESOURCES AS pr_owner ON pr_owner.RES_UID = proj.WRES_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID ,psLV.LT_VALUE_TEXT AS Customer FROM ProjectWebApp_PSWA.pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN ProjectWebApp_PSWA.pub.MSP_CUSTOM_FIELDS AS pspCF ON pspCF.MD_PROP_UID = pspPrjCFV.MD_PROP_UID LEFT JOIN ProjectWebApp_PSWA.pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'Customer') ) AS CT ON CT.PROJ_UID = proj.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS CostCenter FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'Cost_Center') ) AS PCC ON PCC.PROJ_UID = proj.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, pspPrjCFV.TEXT_VALUE AS ProjectAccount FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID WHERE (pspCF.MD_PROP_NAME = 'Project Account') ) AS PP ON PP.PROJ_UID = proj.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS ProjectStatus FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'Project Status') ) AS PPS ON PPS.PROJ_UID = proj.PROJ_UID 

Next, in order not to bother with deleting old data, we clear the tables associated with the tasks. And we proceed to the collection of data from tracking systems. For each system, data is collected in its own way. As already written in this article , for Redmine and TFS, we wrote submissions that return all the necessary data to us.

Since we do not have access to the JIRA database, we used the JIRA REST APIi. As you understand, this is the slowest part of the script and a lot of possibilities for optimization. But this is another story.
After downloading data from trackers, you need to fill in the "gaps" in the tables of projects and employees. These gaps arise due to the fact that not all projects and employees are created in the Microsoft Project due to some circumstances (either they didn’t manage to start or historically). Missing data is immediately visible in the report from the OLAP cube, they are labeled in a separate column as Mismatch, and they can be immediately processed and put in order.

Then it remains to put everything together. Putting together a task list.

And fill in the fact table (who, where and how much time was spent).

After that, we filed a Job, which runs the SSIS package every 3 minutes. And in the end, our Data Warehouse is ready. Then you can do a lot of useful things with it. For example, collect an OLAP-cube from it for building reports, or take data for the Release Notes, or use it to build a vacation schedule (yes, yes, we'll tell you about that too soon).

Step 3. Make a beautiful release of our dream.

Everything is simple: the designer and layout designer made an html-file in which all the information is divided into categories, all the necessary links are added.

Step 4. We made a web application that, based on data from Data Warehouse, creates a single html document with a report for the customer.

Step 5. The project does not stop.

The very first letters provoked an enthusiastic customer response, and they asked us to adapt the template to their brand book and give the opportunity to independently edit our release notes, sorting out the information and adding comments to send to the new recipients.

And we think what labor costs and business processes still optimize ...