Project management dashboard report

Question

How do I create and/or manage a project management dashboard report?

Answer

When you setup your Work Breakdown Structure to have multiple WBS levels (e.g. Project > Task Group > Task), the system will enable a Project Management Dashboard for each project. This article gives instructions on how to setup a report on the Reports tab for this data. For newer project and job-oriented customer systems, this report will have already been setup as a standard report that you can publish to project managers. Before setting up or using this report, make sure you have already setup project management dashboards. 

To create the report:

1. Navigate to Reports 
2. Follow the instructions and create a Custom Timesheet Summary Report that will group hours data for Projects > Task Groups >Tasks.
3. Complete the normal housekeeping tasks for creating the report (e.g. Set a report Name, and preset Time Period). 
4. Because the report will be wide, set the Page Size to "Tabloid" and the Orientation to "Landscape."
5. Your Report Columns should have four fields: Project.name, Taskgroup. name, Task.name, and Hours.
6. Next, you want to add these fields from the Project Management Dashboard: Planned Work, Planned Start, Actual Start, Planned Finish, Actual Finish, and Percent Complete.
7. You will add these one at a time by creating and SQL Expression for each data field.
8. Click on the Plus (+) button and scroll down to the bottom of the field list and select "Add SQL Expression."
9. A SQL Expression Dialog will pop up.
10. To create the Planned Work field in the report, enter the following sql into the text area of the dialog:
    select pd.work from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0)
11. Set this SQL Expression field so Data Type = "Decimal," and Decimal Digits = "2" for Hours data.
12. You can also enable "Show total in report" if appropriate.  
13. You will follow the same steps to create Planned Start, Planned Finish, Actual Start, and Actual Finish. For each of these Date fields, you will set the SQL Expression so the Data Type = "Timestamp" and the Format = "Date." 
14. To create the Planned Start field in the report, enter the following sql into the text area of the dialog:
    select pd.strt from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0)
15. To create the Planned Finish field in the report, enter the following sql into the text area of the dialog:
    select pd.finish from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0)
16. To create the Actual Start field in the report, enter the following sql into the text area of the dialog:
    select pd.actualstrt from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0)
17. To create the Actual Finish field in the report, enter the following sql into the text area of the dialog:
    select pd.actualfinish from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0)
18. To create Actual Work, Use the Hours field and change its label to "Actual Work." 
19. To create the % Complete field in the report, enter the following sql into the text area of the dialog:
    (100*sum(t_timeentry.hours)/(select pd.work from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0)))
20. Sql defintions: 1) pd = an sql abbreviation for project detail 2) t_project_detail is the project management dashboard table where your project dashboard data settings are stored 3) the isnull statements allow the report to handle null values when any names or hours for any projects, task groups or tasks are null.
21. If you want the report to convert all hours values to days, you need to divide planned work and actual hours (the Hours field) expressions by your system's scheduled hours per day. This needs to be one value for the system such as 8.0.
    
    For example, for Planned Work your sql would look like this:
    
    (select pd.work from t_project_detail as pd where isnull(pd.projectid,0)=isnull(t_project.id,0) and isnull(pd.taskgroupid,0)=isnull(t_taskgroup.id,0) and isnull(pd.taskid,0)=isnull(t_task.id,0))/8
    
    Instead of using the built-in Hours field (Time Entry.Hours), remove that field and replace it with a SQL Expression named Actual Days.
    
    Your Actual Work sql would look like this:
    
    select sum(t_timeentry.hours)/8