Common
In this article we will show some additional examples of using CelesteCS Reporting plug-in abilities for showing Jira issue and Confluence page info.
Jira issues
Showing the time spent on the issue
Task
Need to show the total amount of time spent on each issue in hours.
Short description
Use the following Jira Data Provider parameter values:
- Fields = 'worklog,summary' – this will return the information about the time spent on the issue.
- All other parameters should be specified appropriately to your needs.
Use the following Reporting List Item / Reporting Table Item parameter values:
- Body = '(SUM(JSONARRAYTOARRAY(JSONARRAYEXTRACTPATH(CurrentItem.fields.worklog.worklogs, "timeSpentSeconds"),0)) / 3600) & " hours"'.
Long description
The issue work log information is provided as an array of work log items in CurrentItem.fields.worklog object. You should specify the 'worklog,summary' field in the Fields parameter value of the Jira Data Provider to retrieve the work log. Here is an example of what you will get:
|
"fields": { // ... "worklog": { // ... "worklogs": [{ // ... "timeSpent": "1d", "timeSpentSeconds": 28800, "id": "10000", "issueId": "10114" }, { // ... "timeSpent": "1d", "timeSpentSeconds": 28800, "id": "10001", "issueId": "10114" } ] } // ... } |
Counting the sum of the appropriate array subfield is a little bit tricky, but doable with CelesteCS Reporting. Here is a description of the formula parts:
- JSONARRAYEXTRACTPATH(CurrentItem.fields.worklog.worklogs, "timeSpentSeconds") – in this part we send an array of JSON objects (CurrentItem.fields.worklog.worklogs) and the path to the specific field to JSONARRAYEXTRACTPATH function, which creates a new JSON object array, which consists of the object with specified path only. So we will get an array of timeSpent object values, which are the numbers of seconds, spent on the issue.
- The result of first step is sent to JSONARRAYTOARRAY function, which converts a JSON object array to a flat array. As a result, we will get an array of numbers.
- The result of the previous step is sent to the SUM function, which summarizes the values of the array. The result is the total number of seconds spent for the issue.
- After that we simply divide the result of the previous step by 3600 to get the number of hours spent for the issue. For simplification we do not apply additional formatting, but you may use the TEXT function to do that.
Result
Here is what you should get:
