Redmine Reports & Business Intelligence

We have been using Redmine for 3 months actively and the number of issues is now over 11000 with 90 projects and 200 users. When it comes to getting various reports for different objectives, Redmine’s capability is very limited. Especially if you are working with customers from very different fields, the reports you need for each project become unique. You may even have to create a report for human resources department as they are also interested in what is going on with the projects and the time spent on them, or even they may be curious about the projects which consumes most of the labour time.

You have various options like RedmineIssueSummary and some plugins exist for reporting. If you have a high number of issues and projects, you will definitely need a custom report at the end.


RedmineIssueSummary provides you with the number of issues grouped by the followings

User to which the issue is assigned to
User who opened the issue (the author)

It is good to see the current number of issues, but when you need to get the numbers of issues within a time interval, you do not have an option. It has also limited functionality about the asignee table. You can only see the number of issues on the current projects, it does not show the subprojects. After creating our custom reports, I saw that we couldn’t have taken none of those reports from Redmine.

Reporting of the projects and issues is vital for us. Thus, we considered using BI(Business Intelligence) tools.

2.Redmine Reporting with SpagoBI,JasperServer and Jaspersoft Studio

Being easy to use and learn, each of the tools I used to create the reports requires time to use properly.

BI Server: JasperServer or SpagoBI

Report Design: iReport Designer or Jaspersoft Studio (iReport Designer is not the official designer for JasperReports anymore. You can use Jaspersoft Studio)

Database Tool: HeidiSQL – My favourite database tool for MySQL and MSSSQL, you can also use other database tools such as Toad for MySQL which is also free

Firstly, you need to install your server. As we are going to design our reports in Jaspersoft, It is better to use JasperServer. SpagoBI also uses JasperReports engine, but it may cause problems with Jaspersoft Studio as its jar engine is a bit old. If you are going to use SpagoBI, try to use iReport or if you really want to use JasperStudio, you need to update some jar files of SpagoBI.

Both JasperServer and SpagoBI are easy to install. Just follow how to install docs of each server.

Once your server is ready, now it is time to connect and learn more about the database of Redmine with HeidiSQL. The schema below looks complicated but once you start to study it, you will get familiar with all relationships of tables of Redmine.

Redmine Tables and Relationships

Redmine Tables and Relationships

For example, if you want to get the issues of an assignee with id equals 213:

select * from issues where assigned_to_id = 213;

Or if you want to get spent time on a project with id=2

SELECT T.hours,
SELECT CONCAT(firstname," ", lastname)
FROM users
WHERE = T.user_id) User,
FROM projects
WHERE = T.project_id) ProjectName
FROM time_entries T
WHERE T.project_id =2;

After creating your SQL, open report designer (iReport or Jaspersoft Studio) and create the report based on the SQL you create.

See my other post for more Redmine queries:

How to use those BI servers and designers would take long to explain here so I will share their tutorials in separate articles. But if you are so eager to learn right now, you can follow these tutorials

Designing a Report with Jaspersoft Studio

iReport Designer Getting Started

As a conclusion, these are some of  the reports I created for Redmine:

  1. Open Issues Report – Issues which are not closed for the project with id equals 2
  2. Unassigned issues or assigned issues whose last update time was more than 10 days ago
  3. List of solved issues by a user in a time interval
  4. List of projects and issue statuses (Number of issues)
  5. Average solving time of issues (this one was a bit tricky and challenging)
  6. Issues waiting for approval (Solved but waiting for approval of customer or manager)
  7. Report card per project (customer) which includes the total number of issues, solved issues, open issues, approved issues, average solution time for issue type ‘Bug’, average solution time for issue type ‘Feature’, number of visits done to the customer, last visit date etc..
  8. How many issues users solved and how long time spent by user (it includes percentages too.)

I mostly used CrossTab in the reports. For example, to get the number of issues grouped by issue statuses in column and project name in the row for the 4th report.

The most difficult part is the calculation of average solution time. You have to use the tables journals, journal details to calculate. It is same with last report too.

If you are using SpagoBI, you can also create Cocpit to show real-time graphics and more. You can already use graphics within JasperStudio or iReport.

I like Redmine so much so that I started using it for my personal projects as well. .

That’s all for now. Share any ideas, comments and questions.



#business-intelligence, #cocpits, #creating-graphs-with-redmine, #crosstab, #heidisql, #ireport, #jasperserver, #jaspersoft, #jaspersoft-studio, #redmine, #redmine-reports, #spagobi