Number of issue types on certain dates in the past - how to?

Rumceisz

Hi All,

I have a weird question: some project leads approached me and they need a report which shows the number of several issue types on certain dates in the past. Specifically they need info of particular projects from the last 12 months and these "snapshots" would show 15th day of each month: X issue types number of 200, y issue types number of 150, etc.

Is it possible to make this report somehow? Theoretically the JIRA stores every single information to make these reports. But how to get?

Thanks in advance!

Regards,

Rumi

Vidic Florjan

The easiest way (prepared in 5 minutes by normal user) is to prepare filter and use this data to prepare report in excel (or Microsoft Access).

Procedure:

1. Prepere JQL ( they need info of particular projects from the last 12 months )
For example: project in (JIRA, CCONF, PROJX, PROJY) AND created >= -365d

2. Save JQL as a filter and add some fields to filter (button for adding Columns on the upper right side of the filter)
You need to have Created, Issue Type, Project, Status Columns in filter.

3. Export filter data to excel (select Export on the upper right side of the filter, and select Excel (Current fields)) and open excel file with prepared data.

4. Add new Column (e.g. Month) after Create column to calculate month ("snapshots" would show 15th day of each month).

Calculate month with formula like this: =YEAR(F5) & " " & IF(DAY(F5)>15;MONTH(F5)+1;MONTH(F5))
In this example is Create field in column F.

5. Prepare Pivot Table
- Click on Insert, Pivot Table.
- In create Pivot table select Table/Range of the exported data from jira, click on Ok.
- Add Issue Type field in Row Labels area, Sum of Key to Values and Month to column labels.
That's all :-)

6. Present data in Confluence
If you are using Confluence you can also display excel on for example "Reports for managers" page.
(and you can update data from filter each month).

---------------------------------------------------------------------

Another aproach is to use aplications to design and generate reports from a wide range of data sources, for example Crystal Reports:
- In that case you need access to jira database.
- You can get data from jiraissue table (see Mehmet Kazgan's answer).
- Approach to prepare report is "similar" as for Pivot Table (add data from jiraissue table, add Month field,...)
- You can also schedule preparation of reports ("snapshots"). Crystal Reports can send reports to selected managers by mail on 15th day of each month.

Regards,
Florjan