Hi

The below query lets you to generate report about project details such as percentage of project completion , title , status , projected end and its milestone details such as title , description ,comments , owner , status , projected end , gantt color.

 First execute the query    select paramvalue from globalconfig where parameter like 'TaskCompletionStatus'   

If the param value is ' closed' use the first query

Else if the param value is ' completed' use the second query

First query

select pd.projectid , pd.projectcode "Project Code", pd.title "Project Title" , ps1.statusname "Project Status" , poc.popc "Percentage Of Completion" , longtodate(pd.projectedend) "Project ProjectedEnd", md.milestoneid , md.title "Milestone Title", mds.description "MileStone Description", mc.comment "MileStone Comments ", aau.first_name "MileStone Owner", longtodate(md.projectedend) "MileStone ProjectedEnd", ps2.statusname "MileStone Status", ps2.statuscolor from projectdetails pd

LEFT JOIN milestonedetails  md on md.projectid = pd.projectid

LEFT JOIN milestonedescription  mds on mds.milestoneid = md.milestoneid

LEFT JOIN milestonecomments  mc on mc.milestoneid = md.milestoneid

LEFT JOIN sduser sdu ON sdu.userid = md.ownerid

LEFT JOIN aaauser aau ON sdu.userid=aau.user_id

LEFT JOIN projectstatus  ps1 on ps1.statusid = pd.statusid

LEFT JOIN projectstatus  ps2 on ps2.statusid = md.statusid

LEFT JOIN (select ((c1*100) / c2) "popc",cc1.projectid "projectid" from (select count(*) c1 , top.projectid from taskdetails td 

JOIN tasktoprojects top on top.taskid = td.taskid

JOIN statusdefinition sd on sd.statusid = td.statusid

where sd.ispending = false and sd.statusname = 'closed'

group by top.projectid) cc1

JOIN (select count(*) c2 , top.projectid from taskdetails td 

JOIN tasktoprojects top on top.taskid = td.taskid

group by top.projectid) cc2

ON cc1.projectid = cc2.projectid) poc ON poc.projectid = pd.projectid

order by 1

Second query

select pd.projectid , pd.projectcode "Project Code", pd.title "Project Title" , ps1.statusname "Project Status" , poc.popc "Percentage Of Completion" , longtodate(pd.projectedend) "Project ProjectedEnd", md.milestoneid , md.title "Milestone Title", mds.description "MileStone Description", mc.comment "MileStone Comments ", aau.first_name "MileStone Owner", longtodate(md.projectedend) "MileStone ProjectedEnd", ps2.statusname "MileStone Status", ps2.statuscolor from projectdetails pd

LEFT JOIN milestonedetails  md on md.projectid = pd.projectid

LEFT JOIN milestonedescription  mds on mds.milestoneid = md.milestoneid

LEFT JOIN milestonecomments  mc on mc.milestoneid = md.milestoneid

LEFT JOIN sduser sdu ON sdu.userid = md.ownerid

LEFT JOIN aaauser aau ON sdu.userid=aau.user_id

LEFT JOIN projectstatus  ps1 on ps1.statusid = pd.statusid

LEFT JOIN projectstatus  ps2 on ps2.statusid = md.statusid

LEFT JOIN (select ((c1*100) / c2) "popc",cc1.projectid "projectid" from (select count(*) c1 , top.projectid from taskdetails td 

JOIN tasktoprojects top on top.taskid = td.taskid

JOIN statusdefinition sd on sd.statusid = td.statusid

where sd.ispending = false

group by top.projectid) cc1

JOIN (select count(*) c2 , top.projectid from taskdetails td 

JOIN tasktoprojects top on top.taskid = td.taskid

group by top.projectid) cc2

ON cc1.projectid = cc2.projectid) poc ON poc.projectid = pd.projectid

order by 1

regards,

Dhiyan,

SDP