Custom Formulas and Language for Advanced Report Customization | Targetprocess - Visual management software

Custom Formulas and Language for Advanced Report Customization

Requires Targetprocess 3.6.0+

This article is obsolete for On-demand accounts. Please see our guide section on how to create Visual Reports.  

Custom Formulas

You can specify entities properties, aggregations and custom fields in axes. Moreover, you can specify custom formulas. For example, you have several teams that work on some features. Every Feature has forecasted end date and planned end date. How to quickly find the most lagging features? We can deduct ForecastEndDate and PlannedEndDate and see the difference. For lagging features it will be positive, for the features ahead of schedule this number will be negative.

(ForecastEndDate - PlannedEndDate).TotalDays

Setup looks like this:

Custom Formulas

And chart may look like that:

Custom Formulas and Language for Advanced Report Customization. Image 2

Now we see that Exploited team works on a single feature and almost 55 days behind. While CAT team works on a feature and 5 days ahead of its schedule. Large features have greater effort, so for example it is interesting why NEW TEAM is almost 30 days late with quite small feature.

You can do other arithmetic operations with numeric fields. For example, Effort - TimeSpent may show problems with estimation. Let’s say, you estimate user stories in points and have an agreement that 1 point = 8 hours. In this case you may wonder what User Stories took significantly more time to implement? You can construct a formula to quickly find them:

TimeSpent / (Effort * 8)

Custom Formulas and Language for Advanced Report Customization. Image 3

DSL

This language is complex and it is not recommended to build reports with it. If you are curious and brave, let’s dig into this topic.

Every report has DSL tab. The tab consists of two panes:

  • The Left area contains data source definition. What entities we want to have in a report, what filters, what properties we will use as dimensions.
  • The Right area contains chart definition. It describes chart type, maps axes and dimensions and in some cases has various formatting rules (how to display dates on X axis, whether to show coordinates grid or not, etc).

DSL

Data Source

Data source is specified in JSON. It is easy to understand it using an example.

Data Source

Here we extract the bugs, that were not closed and split them by the value in custom field “Browser”:

{
    "source": { // Defines what we will extract from Targetprocess
        "items": [ // array of entities
            {
                "id": "bug" // here we have just Bugs
            }
        ],
        "filter": "?EntityState.IsFinal != true" // DSL filter, same as in View setup in 
Targetprocess
    },
    "dimensions": [ // array of dimensions, they will be used in chart definition later
        {
            "id": "browser", // dimension id
            "model": "Browser", // entity field name or some valid aggregation
            "filter": "" // DSL filter if you want to select not all Browsers
        },
        {
            "id": "bugsCount",
            "model": "COUNT()",
            "filter": ""
        },
        {
            "id": "entityType",
            "model": "entityType.name",
            "filter": ""
        }
    ]
}
 

In general, data source definition is quite straight forward. Here is another example where we extract average cycle time for User Stories and Bugs by weeks:

{
    "source": {
        "filter": "?EntityState.IsFinal is true and EndDate >= Today - 182(days)",
        "items": [
            {
                "id": "Bug"
            },
            {
                "id": "UserStory"
            }
        ]
    },
    "dimensions": [
        {
            "model": "avg(cycleTime)",
            "id": "cycleTime"
        },
        {
            "model": "project.name",
            "id": "project"
        },
        {
            "model": "entityType.name",
            "id": "entityType"
        },
        {
            "model": "week(endDate)",
            "id": "endDate"
        }
    ]
}

Chart

Chart definition is in JSON format as well. Here is a simple example

{
    "type": "bar", // chart type: bar, horizontalBar, line, scatterplot
    "color": "entityType", // Id of a dimension from left pane
    "size": null, // size is not supported for bar chart
    "x": [
        "browser" // X axis will show Browser dimension
    ],
    "y": [
        "bugsCount" // Y axis shows bugsCount dimension
    ]
}

Another example is more complex. Here we set dimensions and guide as well. Dimensions describe data and define scales. Guide provides advanced chart formatting options:

{
    "x": "endDate",
    "y": [
        "project",
        "cycleTime"
    ],
    "color": "entityType",
    "dimensions": {
        "endDate": {
            "type": "order",
            "scale": "period"
        },
        "project": {
            "type": "category",
            "scale": "ordinal"
        },
        "cycleTime": {
            "type": "measure",
            "scale": "linear"
        },
        "entityType": {
            "type": "category",
            "scale": "ordinal"
        }
    },
    "guide": [
        {
            "y": {
                "label": "Project"
            }
        },
        {
            "x": {
                "label": "End Date by Week",
                "tickPeriod": "week"
            },
            "y": {
                "label": "CycleTime in days"
            }
        }
    ],
    "type": "line"
}

You can dig into more details in taucharts api documentation.

DSL can be useful when you want to send chart configuration to someone else. In this case you can send two JSONs and ask to paste them into left and right panes. For example, you can contact our support (support@targetprocess.com) and ask about some advanced chart you can’t create and we will try to help.

Moreover, with DSL you can create reports that are not possible to create via Setup UI. For example, you can create a report using Time entity, while this entity selection is not available in Setup. However, Custom Reports DSL is a complex thing, so we will not dig into details here.

Still have a question?

We're here to help! Just contact our friendly support team

Find out more about our APIs, Plugins, Mashups and custom extensions. Join our community of passionate users and even discuss directly with our developers.