Project Report with SQL Query

Added by Klaas Wüllner almost 10 years ago

Hi,

i use redmine since some months for a project because it matches most of my features.
But now i need your help. I want to creat a project report.

The report contains the ticket subject, description and status, but also the history of the ticket.

At first a sql query would help me.

#       subject description     status  comment     status_old      status_new
1234    test    test test test  closed  comment 1   new             review
                                        comment 2   review          closed
5678    test2   texttexttext    review  comment 1        
                                        comment 2   new             review
                                        comment 3        
                                        comment 4        

Or a solution like this http://www.redmine.org/boards/1/topics/27962

Thx for help

Replies (3)

RE: Project Report with SQL Query - Added by Klaas Wüllner almost 10 years ago

i tried a little around and create a sql query

select i.subject, i.status_id,s3.name, j.notes , jd.*,s1.name,s2.name
from  ((((issues as i join journals as j on i.id = j.journalized_id) 
join journal_details as jd on j.id = jd.journal_id)
join issue_statuses as s1 on  jd.old_value = s1.id)
join issue_statuses as s2 on  jd.value = s2.id)
join issue_statuses as s3 on i.status_id = s3.id
where i.tracker_id=5 and jd.prop_key='status_id'
union
select i.subject, i.status_id,s3.name, j.notes , jd.*,jd.old_value,jd.value
from  ((issues as i join journals as j on i.id = j.journalized_id) 
join journal_details as jd on j.id = jd.journal_id)
join issue_statuses as s3 on i.status_id = s3.id

where i.tracker_id=5 and jd.prop_key='done_ratio'
order by subject ASC

The query uses only status and done ratio at the moment. It also needs more work for the needed columns and filters.

RE: Project Report with SQL Query - Added by Kyle Johnson almost 10 years ago

I also just got a request from one of our project managers asking for a similar report. They didn't want the new/old status, just the comment history.

Since my requirements were different, I used a different approach. However, this was one of my first search hits, so it seems appropriate that anyone looking for this type of report might stumble across this as well and be able to decide which solution works for them.

Mine only shows a list of comments and a bug's current status.

SELECT DISTINCT `i`.`id` AS `ID`
    , `p`.`name` AS `Project Name`
    , `is`.`name` AS `Status`
    , `i`.`subject` AS `Subject`
    , `i`.`description` AS `Description`
    , `i`.`start_date` AS `Start Date`
    , `i`.`due_date` AS `Due Date`
    , `j`.`notes` AS `Notes` 
FROM `redmine`.`issues` AS `i`
    INNER JOIN `issue_statuses` AS `is` ON `i`.`status_id` = `is`.`id`
    INNER JOIN `projects` AS `p` ON `i`.`project_id` = `p`.`id`
    LEFT OUTER JOIN `journals` AS `j` ON `i`.`id` = `j`.`journalized_id`
WHERE ((LENGTH(`j`.`notes`) > 0 AND (SELECT COUNT(*) FROM journals WHERE LENGTH(notes) > 0 AND journalized_id = `i`.`id`) > 0)
OR ((SELECT COUNT(*) FROM journals WHERE LENGTH(notes) > 0 AND journalized_id = `i`.`id`) = 0))
-- below this line is where you can define your own query parameters
AND `p`.`id` = 1
ORDER BY `p`.`name` DESC, `i`.`due_date`, `i`.`id`, `j`.`id`

RE: Project Report with SQL Query - Added by Klaas Wüllner almost 10 years ago

--UPDATE--

...okay, thanks Kyle for support i build a "best of" both queries. First for journal_details and second for notes.
For me the tracker is the important filter.

SELECT i.id as ID,s1.name,  i.subject, i.description ,i.due_date, concat_ws(" ",jd.prop_key, jd.old_value, jd.value) as "Änderungen",  j.id AS ID2
    FROM issues AS i         
        INNER JOIN issue_statuses AS s1 ON i.status_id = s1.id              
        INNER JOIN trackers AS t ON i.tracker_id = t.id              
        LEFT OUTER JOIN journals AS j ON i.id = j.journalized_id         
        LEFT OUTER JOIN journal_details as jd ON j.id = jd.journal_id                  
            WHERE ((LENGTH(j.notes) > 0          
                AND (SELECT COUNT(*) FROM journals WHERE LENGTH(notes) > 0          
                AND journalized_id = i.id) > 0) OR ((SELECT COUNT(*) FROM journals WHERE LENGTH(notes) > 0          
                AND journalized_id = i.id) = 0))
                and jd.prop_key is not null                          
                AND t.id = 5
UNION
    SELECT i.id AS ID,s1.name,  i.subject, i.description ,i.due_date,j.notes AS "Änderungen", j.id AS ID2                  
        FROM issues AS i         
            INNER JOIN issue_statuses AS s1 ON i.status_id = s1.id              
            INNER JOIN trackers AS t ON i.tracker_id = t.id              
            LEFT OUTER JOIN journals AS j ON i.id = j.journalized_id                  
        WHERE ((LENGTH(j.notes) > 0          
            AND (SELECT COUNT(*) FROM journals WHERE LENGTH(notes) > 0          
            AND journalized_id = i.id) > 0) OR ((SELECT COUNT(*) FROM journals WHERE LENGTH(notes) > 0          
            AND journalized_id = i.id) = 0))                           
            AND t.id = 5          

    ORDER BY ID ASC, ID2 ASC
Next step is join for status, categorie, ... in column "Änderungen", and preformatting for Excel.

I think such a view is helpful for reporting in huger project enviroment. Because the ticket list with "done_ratio" and "subject" without description and history is not informative enough.
...At least in our projects :-)

Maybe it is useful for this topic http://www.redmine.org/boards/1/topics/27962

Another question is: How to prevent breaks if i put the query in a file? Often in descriptions are breaks. In file outputs these breaks are included. Currently is copy and paste from webmin gui.

Greetings
Klaas

(1-3/3)