Reports for Redmine

After some requests I have received, I will share some of the SQL commands that I used for creating reports for Redmine. You need to change projectIDs, journelIDs etc.  Here they are:

1 – Approximate Solution Time


a.istipi IsTipi,
COUNT(a.isnumarasi) ToplamIsSayisi,
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id IN (45,
64,
65) OR
projects.parent_id IN (64,
45,
65)) AND
journal_details.value = 3 AND
issues.status_id =12

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
GROUP BY
a.tracker_id

2. Current Project Analysis


(select name from projects where projects.id = issues.project_id) ProjeAdi,
count(*) ToplamIsSayisi,
(SELECT count(*) from issues where issues.status_id=2 and issues.project_id= $P{PROJECT_ID} ) IslemdeIsSayisi,
(SELECT count(*) from issues where issues.status_id=12 and issues.project_id=$P{PROJECT_ID}) CozumOnaylandiIsSayisi,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id = 1

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuHata,

(SELECT
ROUND(
AVG(
DATEDIFF(
a.makstarih,
a.created_on)),
1)
OrtalamaCozumGunuYuvarlanmis
FROM
(SELECT
max(journals.created_on) AS makstarih,
issues.created_on,
issues.id isNumarasi,
issues.tracker_id,
trackers.name AS istipi
FROM
issues
JOIN journals ON issues.id = journals.journalized_id
JOIN journal_details ON journals.id = journal_details.journal_id
JOIN projects ON issues.project_id = projects.id
JOIN trackers ON issues.tracker_id = trackers.id
WHERE
(projects.id =$P{PROJECT_ID} OR
projects.parent_id = $P{PROJECT_ID}) AND
journal_details.value = 3 AND
issues.status_id =12 AND
issues.tracker_id =2

GROUP BY
isnumarasi
ORDER BY
isnumarasi) AS a
)OrtalamaCozumGunuIstek,

(select
SUM(DATEDIFF(issues.due_date,issues.start_date)+1) IsGunuSayisi
FROM issues
where issues.tracker_id =16
and issues.project_id = $P{PROJECT_ID}) ToplamZiyaretSayisi,
(select max(due_date) FROM issues where issues.tracker_id =16 and issues.project_id = $P{PROJECT_ID}) SonZiyaretTarihi
FROM issues where issues.project_id = $P{PROJECT_ID}
and issues.tracker_id != 16

3. Status of an Assignee

Change assignee id for your project.


SELECT id,
(SELECT name
FROM issue_statuses
WHERE issue_statuses.id = issues.status_id)
issueStatus,
(SELECT name
FROM projects
WHERE projects.id =issues.project_id)
ProjeAdi,
(select concat(firstname,' ',lastname) from users where users.id=issues.assigned_to_id) as AssigneeName
FROM issues
WHERE issues.assigned_to_id = $P{ASSIGNEE__ID}

Thanks a lot to Taner Yurdunkulu

Advertisements

#business-intelligence, #redmine, #redmine-reports

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. Continue reading

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