Redmine

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.

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`

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