dotProject: report for total actual costs (from task logs) for each project

dP comes with some nice reports to get you started, but in any system like this reports is a very personal thing, an area where you will need to spend some time to create a set of customized reports that will meet your needs.

The app’s reports module seems easy enough to work with (and it will be nice when it is moved over to it’s own module rather than a subsection of projects).

We started using the billing codes on our tasks, thinking this could be totalled up for either invoicing or tracking actual internal costs against a budgeted project (most of our projects are budgeted and not time and materials). 

So the first thing I wanted to know is the total costs incurred for a project so we can manage within our budgets and maintain a healthy margin.  Well, no report for that.  It would be nice to have a field on the project view similar to actual budget, but nothing there either (I think I saw on the forum they intend to add it…??).

Well, after a brief inspection of the database schema, here’s a rough query to get the answer.

select p.project_id, p.project_name, p.project_target_budget, p.project_actual_budget, p.project_percent_complete,
sum(t.task_duration) as tot_duration,
round(sum(l.task_log_hours),1) as actual_hours,
round(sum(l.task_log_hours * b.billingcode_value),2) as actual_cost
from projects p, tasks t, task_log l, billingcode b
where p.project_id = t.task_project
and t.task_id = l.task_log_task
and l.task_log_costcode = b.billingcode_id
group by p.project_id, p.project_name, p.project_target_budget, p.project_actual_budget, p.project_percent_complete

Now the only thing left to do is create a report in dP, will do that next when I have time (I have to get a project deliverable out to a client today…back to work.)