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

  1. 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

#business-intelligence #redmine #redmine-reports