Does the Intelligent Reports Plugin allow us to report on the average value of a custom field?

[Daniel Weishoff]

This Plugin is designed to produce legible and robust graphs, but we need graph an average, and this seems like a feature that JIRA doesn't handle. Will this plugin do it?

https://marketplace.atlassian.com/plugins/com.clariostechnology.intelligentreports

More Details:

We have 3 custom fields that contain numeric values related to our incident response.

We need to report on the average value of each of these fields over the course of a month, and right now Excel is the only way we can get that number. A JIRA plugin that could handle this would make our reporting process MUCH MUCH easier.
 

[mwarton]

Hi Daniel,

Intelligent Reports can certainly produce this chart for you.

It is more than a basic chart, so you will need to enter some calculations to get exactly the chart that you want. It was not clear whether you wanted three charts showing one average each, or one chart showing all three averages, so the calculations here are for the more complicated three averages on one chart case.

You can see how to create a basic chart in part 5 of the template creation tutorial. Once you have a basic chart, Change the chart type to calculated chart. You will then be able to enter the following calculations.

The calculations in a calculated chart actually apply to the rows and columns in the spreadsheet underlying the chart. In this case, using a bar chart in the Microsoft Word document, we want one row per date of interest, and one column per custom field. The values will be the average of the custom field on that day.

The first calculation is the row data. For this we want one row per date in the range. Calculations make this easy:

listDaysBetween(datetime(questions."Start Date"), datetime(questions."End Date"))

This will produce one row for each day between the users answer to the question "Start Date" and the users answer to the question "End Date".

The second calculation should produce the label to show for the row on the chart. In this case we want the date in our preferred format. For example:

row.format("MMMM d")

Here row is the row data for the row (in our case the date for the day). We format it with the format string "MMMM d", which produces dates like "January 1".

The third calculation is the column data. In our case there is not really any data for the column, so we will simply number them:

[1,2,3]

The fourth calculation is the label to display for the column. On a bar chart this will appear in the chart key. We can use if functions to give the appropriate label for each field:

if(column==1, "Field 1", if(column==2, "Field 2", "Field 3"))

The final calculation calculates the actual values for each cell in the table, and thus each data point on the chart. This is a lengthy calculation in this chart, but is not particularly complicated. The full calculation is:

if(column==1, average(jql("project={Project} and created >= {row.format(\"yyyy-MM-dd\")} and created <= {row.addDays(1).format(\"yyyy-MM-dd\")}")."Time spent"), if(column==2, average(jql("project={Project} and created >= {row.format(\"yyyy-MM-dd\")} and created <= {row.addDays(1).format(\"yyyy-MM-dd\")}")."Original estimate"), average(jql("project={Project} and created >= {row.format(\"yyyy-MM-dd\")} and created <= {row.addDays(1).format(\"yyyy-MM-dd\")}")."Remaining estimate")))

A similar calculation is repeated three times for the three different fields, inside if statements just like in the column names calculation. Looking at one of these individual calculations:

average(jql("project={Project} and created >= {row.format(\"yyyy-MM-dd\")} and created <= {row.addDays(1).format(\"yyyy-MM-dd\")}")."Time spent")

The jql function returns a list of issues that match the JQL query. The ."Time spent" changes the list of issues into a list of numbers by getting the Time spent field for each issue. You would use your custom field name here instead of "Time spent". Finally the average function calculates the average of all of these numbers.

The JQL query uses Intelligent Reports calculations to restrict the issues selected to those created on the day of interest. Intelligent Reports will perform the calculations inside {} before sending the query to JIRA. In this query:

{Project}

Intelligent Reports gets the key of the project that the user selected as the answer to the question "Project".

{row.format(\"yyyy-MM-dd\")}

Intelligent Reports gets the day for this row in the format "2016-02-07" which is the format JIRA understands in JQL queries.

{row.addDays(1).format(\"yyyy-MM-dd\")}

Intelligent Reports gets the next day after this day in the format JIRA understands in JQL queries.

You can find more information on all of the calculation functions available in the calculations reference documentation.

I have uploaded an example report template with the above calculations so you can see it in action.

I hope this lets you create the chart you need. If you have any further questions please respond here or contact our support directly through our website.