How to create a Historical Report

Historical Data Basics

Manually creating a historical report requires a deep understanding of Targetprocess data’s structure. This structure is more or less clear in the case of entities, because you already work with this data regularly. On the other hand, the structure of historical data is mostly hidden from users.

Let’s see how history is represented in Targetprocess.
A History record is just a copy of an entity with a time period definition.
For example, User Story #1 'Shopping bag' was created on 01/01/2017 in the Project 'E-commerce' and is now in the 'In Testing' state.

The history for this User Story may look like this:

As you can see, every record represents a changed field (they are marked in red).

The 'Date' and 'DateTo' fields represent periods in which the record is valid.
The 'UserStoryId' field is a reference to our User Story.
The 'IsChanged...' columns show which fields were changed compared to the previous record.

Viewing historical data on a Timeline-based graphical report makes everything clear:

At any given point in time, only one historical record is valid for an entity. This exact approach is used by the Timeline axis in reports.

Historical Report Setup

Historical report setup is quite similar to entity report setup:

• Source type: The Entity Reports option is used for a report with current data, while the Cumulative, Burn up & down reports option is used for a report with historical data.
• Projects and Teams: Determines which Projects and Teams’ work will be shown in the report.
• Entities: Shows what kind of entities will be used as a data source for the report.

A historical report is built for a particular period of time. You can use one of  the following fields to specify the date range:

• Release: the list of Releases.
• Iteration: the list of Iterations.
• Team Iteration: the list of Team Iterations

If you select a particular Release / Iteration/ Team Iteration, then the entities that were ever assigned to the corresponding scope of work and all the related modification records will be used as a data source for the report.

Date Range: Specifies the period of time not tied to a particular scope of work, for which the history report will be built.

Filter: By default it allows filtering by historical properties. To filter by actual values, you need to use the Current prefix for the entity. For example, for a bug history report, it would use the CurrentBug field.

If you select several types of entities as Entities, you will see Advanced filtering options. They allow you to filter the history of each type of entity separately.

More on filters for historical reports:

• Filters by Date Ranges
• Filters by Entity Fields

You can create a report using one of the available templates, or choose to create a report from scratch. Use text search to look through the templates. The templates will be automatically filtered once a data source is selected:

When you enter the charts editor, a new panel 'Data Fields' will appear. The two most important fields for a report by history are:

Timeline - This is a time scale showing the historical records valid at each point of time. When you use Timeline for the X-axis, you can build the following reports:

• Cumulative Flow - Count of records as the Y-axis, State as Color;
• Burn Up - Effort and Effort Completed as the Y-axis
• Burn Down - Effort To Do as the Y-axis

Duration - This helps to answer the question of how long an entity should stay in a particular state.

Using Duration as the Y-axis and State as the X-axis allows you to see the average time entities spend in each state.

Creating Cumulative Flow Chart

The predefined Cumulative Flow chart found in the top bar under the Reports menu always counts Done entities from zero:

That makes sense when the Cumulative Flow chart is built for a long-term Project with a limited date range. At the moment you may have a lot of Done entities in your Project, and if they are not filtered out of the chart, the information displayed will not be very descriptive.

To filter out Done entities that were completed before the 'Date Range Start', you can use the filter:
?EndDate is None or EndDate >= 'Date Range Start'

With this filter we see the history records in which entities were not Done or became Done after 'Date Range Start'. Here 'Date Range Start' is shorthand for the Start Date of your selected date range: for example if you select a Release which starts on 14 Jun 2017, 'Date Range Start' will be set to 14 Jun 2017.

The main disadvantage of the old predefined Cumulative Flow chart is the absence of custom filters. Now you can use your own filters for the Cumulative Flow report. For example, you may want to see the number of open Bugs per day only. The filter will be:
?EntityState.IsInitial is True

Or, you may be interested in the number of bugs closed every day. The filter in this case will be:
?EndDate >= 'Date Range Start'

Burn Up/Down chart

By clicking a point on the chart line, you can browse data related to that particular moment in time:

Advantages of the new Burn Down chart:

• Using the predefined Burn Down chart, it was not possible to see detailed information by day or by month - The Release Burn Down chart was showed progress by week only. With Visual reports you can adjust the Timeline axis to group changes by Day or by Month.
• It is now possible to create a Burn Up chart.
• The predefined Burn Down chart doesn’t allow you to see the progress of several Releases on the same chart. With Visual reports, several Releases can be selected.
• From now on, having a Release/Iteration/Team Iteration is not mandatory. You can create a Burn Down chart to see the progress by Feature or by Project.
• If your team is using Time tracking, you can create a Burn Down chart by Time Remaining instead of Effort.

Burn Down chart limitations:

• Visual Reports will not show the Ideal line, they do not calculate the Velocity and Forecasted End Date. There is no way to exclude weekends.
• If you use Time Tracking and post time to past dates, the change will be reflected the day when the time was posted, not in the past.

Duration chart

A typical user requirement is to know the average amount of time that entities spend in a particular state. Imagine a User Story having the following flow: Open -> In Dev -> In Testing -> Reopen-> In Dev -> In Testing -> Done. In this example, the User Story was moved to the 'In Dev' state twice.

To retrieve the total time the User Story spent in the 'In Dev' state, we take all the historical records for this entity where the Entity State is 'InDev', and sum up their duration. To do the same for a group of User Stories, first we need calculate the duration per entity per state, and then calculate the average based on all User Stories.

More on this type of charts: Historical Visual Reports for Processing Time and Custom Cycle Time

Bonus

There might be situations where you need to identify which User Stories have been added into an Iteration or a Release after it was started, to track the increase in scope. To do this, apply the following settings:

X-axis: Date of Modification
Y-axis: Entity Name
Filter: ?IsChangedRelease is True and Date >='Date Range Start'