Daily time report

[BillinMN]

I need to create report that displays the daily time entered for each task
for each resource. What tables do I need to pull together?

 

[Gérard Ducouret]

Hello Billin,

Have you tried the "Task Usage" view with the "Work" field:
View / Task Usage

NB : you can add some other fields : right Click a yellow cell...

Hope this helps,

Gérard Ducouret

 

[Steven Yetter]

See the Report Hours worked for a resource during a period by project thread
but the SQL below did the trick for me.

SELECT
TOP 100 PERCENT p.PROJ_NAME, r.RES_NAME, a.TASK_NAME, a.ASSN_ACT_WORK /
60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000 AS assigned_work,
a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work, a.WASSN_SEND_UPDATE_DATE AS
update_sent_date,
(w.wwork_value * (DATEDIFF(day, w.WWORK_START, w.WWORK_FINISH)+1 ))/60000 as
[work],
w.WWORK_START as work_start, w.WWORK_FINISH as work_finish
FROM MHCCSCP1.ProjectServer.dbo.MSP_WEB_RESOURCES r INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_ASSIGNMENTS a ON
a.WRES_ID = r.WRES_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_WORK w on
a.WASSN_ID = w.WASSN_ID
WHERE w.wwork_type = 1 and w.wwork_value > 0
ORDER BY w.WWORK_START, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME

 

[BillinMN]

Thanks!! That query runs jsut fine. How would I include the summary tasks
that the lowlevel tasks are associated to?

See the Report Hours worked for a resource during a period by project thread
but the SQL below did the trick for me.

SELECT
TOP 100 PERCENT p.PROJ_NAME, r.RES_NAME, a.TASK_NAME, a.ASSN_ACT_WORK /
60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000 AS assigned_work,
a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work, a.WASSN_SEND_UPDATE_DATE AS
update_sent_date,
(w.wwork_value * (DATEDIFF(day, w.WWORK_START, w.WWORK_FINISH)+1 ))/60000 as
[work],
w.WWORK_START as work_start, w.WWORK_FINISH as work_finish
FROM MHCCSCP1.ProjectServer.dbo.MSP_WEB_RESOURCES r INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_ASSIGNMENTS a ON
a.WRES_ID = r.WRES_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_WORK w on
a.WASSN_ID = w.WASSN_ID
WHERE w.wwork_type = 1 and w.wwork_value > 0
ORDER BY w.WWORK_START, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME

Hello Billin,

Have you tried the "Task Usage" view with the "Work" field:
View / Task Usage

NB : you can add some other fields : right Click a yellow cell...

Hope this helps,

Gérard Ducouret

 

[Steven Yetter]

To be honest with you I do not have the answer to your question. The extent
of my knowledge was reached building the query below and most of that came
from answers from this group. Look through the thread "Report Hours worked
for a resource during a period by project thread" and then contact those
that helped get me this far and maybe they can get you the rest of the way.

Thanks!! That query runs jsut fine. How would I include the summary tasks
that the lowlevel tasks are associated to?

See the Report Hours worked for a resource during a period by project thread
but the SQL below did the trick for me.

SELECT
TOP 100 PERCENT p.PROJ_NAME, r.RES_NAME, a.TASK_NAME, a.ASSN_ACT_WORK /
60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000 AS assigned_work,
a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work, a.WASSN_SEND_UPDATE_DATE AS
update_sent_date,
(w.wwork_value * (DATEDIFF(day, w.WWORK_START, w.WWORK_FINISH)+1 ))/60000 as
[work],
w.WWORK_START as work_start, w.WWORK_FINISH as work_finish
FROM MHCCSCP1.ProjectServer.dbo.MSP_WEB_RESOURCES r INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_ASSIGNMENTS a ON
a.WRES_ID = r.WRES_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_WORK w on
a.WASSN_ID = w.WASSN_ID
WHERE w.wwork_type = 1 and w.wwork_value > 0
ORDER BY w.WWORK_START, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME

Hello Billin,

Have you tried the "Task Usage" view with the "Work" field:
View / Task Usage

NB : you can add some other fields : right Click a yellow cell...

Hope this helps,

Gérard Ducouret

"BillinMN" <[email protected]> a écrit dans le message de
I need to create report that displays the daily time entered for

each
task

for each resource. What tables do I need to pull together?