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 curios about the projects which consumes most of the labor time.

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

1.RedmineIssueSummary

RedmineIssueSummary provides you the amount of issues grouped by the followings

Tracker
Priority
User to which the issue is assigned to
User who opened the issue (the author)
Version
Category

It is good to see the current number of issues, but when you need get the numbers of issues within a time interval, you do not have an option. It has also limited functionality about asignee table. You can only see the number of issues on the current projects, it does not show the sub projects. After creating our custom reports, I saw that we couldn’t have taken non 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 oficial designer for JasperReports anymore. You can use Jaspersoft Studio)

Database Tool: HeidiSQL – My favorite 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 of a projects with id=2


SELECT T.hours,
(
SELECT CONCAT(firstname," ", lastname)
FROM users
WHERE users.id = T.user_id) User,
(
SELECT name
FROM projects
WHERE projects.id = 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.

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 then 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 approval of customer or manager)
  7. Report card per project (customer) which includes 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 customer, last visit date etc..
  8. How many issues a 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 row for 4th report.

The most difficult part is 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 personel projects as well. Openshift provides up tp 3 application for free. You can run a Redmine application in Openshift in minutes.  You may need https://uptimerobot.com for idle status of your app.

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

 

Advertisements
This entry was posted in Business Intelligence, Jaspersoft Studio, Redmine, SpagoBI and tagged , , , , , , , , , , , . Bookmark the permalink.

One Response to Redmine Reports & Business Intelligence

  1. asdfasdec wsfws says:

    Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s