My Task Reporting

[TLBartz]

Does anyone know which tables and fields in the Reporting Database I would
use to extract the following data?

I am using PS2007 and I am not using the Data Analysis tool within PWA. I am
going to be extracting the data to use for capitalization calculations.

Project
Task
Resource
Date (month/day/year) or similiar
Hours Entered by resource on this given day

Thanks!

 

[Guidho]

MSP_EpmProject
MSP_EpmTask
MSP_EpmResource
MSP_EpmAssignment
MSP_EpmAssignmentByDay

The main table you will need is MSP_EpmAssignmentByDay as this stores
assignment info by day, so also the values entered by resources.

You can find all available tables and columns with a description in the
Project 2007 SDK.

Guidho

 

[TLBartz]

I see to be on the correct path then. My issue is...

I am pulling the information by day but it is showing the hours for every
task level of the project and if I pull in the resource information it
multiplies it. I am thinking I have my linking incorrect.

 

[Guidho]

Make sure that you join the tables correctly (INNER JOIN) and join on the
correct columns (GUID).

Also, if you join them, make sure to add a GROUP BY to summarize all hours
per day for each resource.

If you post your SQL query here, we might be able to tell you what is going
wrong.

Guidho

 

[TLBartz]

Here is the SQL info. Please note that I am not a DBA or programmer. I have
been assigned to figure this out so, I am using MS Access and I have copied
this from the SQL View. I have enough understanding to get through this.

I seem to be pulling the correct data but what I am seeing is that not all
entries for a day & resource are being displayed. (ie..PS2007User posted 5
hours against Capitalized Project D - Support and 3 hours against Capitalized
Project D - Test Scripts but I am only seeing the 3 hours in the query and
not the 5 OR I am seeing entired for one project but none for another
project).


SELECT dbo_MSP_EpmProject.ProjectName,
dbo_MSP_EpmAssignmentByDay.AssignmentUID, dbo_MSP_EpmTask.TaskName,
dbo_MSP_EpmResource.ResourceName,
dbo_MSP_EpmAssignmentByDay.AssignmentActualWork,
dbo_MSP_EpmAssignmentByDay.TimeByDay
FROM dbo_MSP_EpmResource INNER JOIN (((dbo_MSP_EpmAssignmentByDay INNER JOIN
dbo_MSP_EpmProject ON dbo_MSP_EpmAssignmentByDay.ProjectUID =
dbo_MSP_EpmProject.ProjectUID) INNER JOIN dbo_MSP_EpmTask ON
dbo_MSP_EpmAssignmentByDay.TaskUID = dbo_MSP_EpmTask.TaskUID) INNER JOIN
dbo_MSP_EpmAssignment ON dbo_MSP_EpmAssignmentByDay.AssignmentUID =
dbo_MSP_EpmAssignment.AssignmentUID) ON dbo_MSP_EpmResource.ResourceUID =
dbo_MSP_EpmAssignment.ResourceUID
WHERE (((dbo_MSP_EpmAssignmentByDay.AssignmentActualWork)>0))
ORDER BY dbo_MSP_EpmAssignmentByDay.TimeByDay;

 

[Stephen Sanderlin]

If you're not seeing data in the Reporting database, make sure the
updates were applied against the project and that the project was
republished. Pretty much everything that shows in PWA should show in
the RDB unless there is a problem.

If you are seeing 3 hours in the RDB when you should be seeing 5, try
opening up the project in PRO from the Published store. Look to see if
the Published store version shows 3 or 5 hours.

As a side note, your JOIN type should depend on what you are trying to
do -- INNER JOIN is not appropriate for every situation.

--
Stephen Sanderlin
Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.com/

EPM Solutions Architect / Principal Consultant - BT Professional
Services
http://bt.ins.com/

This electronic message, along with any information, advice, and
opinions it contains, are mine alone and are not representative of my
employer. All information is provided in "GOOD FAITH" and on an "AS IS"
basis only. I provide no presentations or warranties, express or
implied, including implied warranties of fitness for a particular
purpose, merchantability, title, and noninfringement. I strongly advise
you to extensively test any changes, workarounds, or techniques
described herein on a development system prior to implementation in a
production environment, and you are hereby notified that I bear no
responsibility whatsoever for any loss, harm, or otherwise negative
outcomes resulting from your actions, whether or not said actions were
a result of this electronic message, directly or indirectly.

 

[Guidho]

Your query seems to be OK (although not organized in very nice to read
manner, but that's Access I guess).

The reason why you might not see some actual work enterend in the My Tasks
page, is that the reporting database only contains published data.
So as long as actual work is not approved, updated in the plan and the plan
is not published again, you will not see this data in the reporting database.

Guidho

 

[Chris Boyd]

Try this out:


SELECT ProjectOwner.ResourceName AS PM,
Resource.ResourceName AS Resource,
MSP_EpmProject_UserView.ProjectName AS Project,
MSP_EpmTask_UserView.TaskName AS Task,
CONVERT(varchar(10), MSP_EpmTask_UserView.TaskStartDate, 101) AS [Task
Start Date],
CONVERT(varchar(10), MSP_EpmTask_UserView.TaskFinishDate, 101)
AS [Task Finish Date],
CONVERT(varchar(10), MSP_TimeByDay.TimeByDay, 101) AS [Assignment Date],
MSP_EpmTask_UserView.TaskActualWork AS [Actual Work],
MSP_EpmTask_UserView.TaskRemainingWork AS [Remaining Work],
MSP_EpmTask_UserView.TaskWork AS [Work],
MSP_EpmProject_UserView.ProjectUID,
MSP_EpmTask_UserView.TaskUID,
MSP_EpmAssignment_UserView.AssignmentUID,
Resource.ResourceUID,
ProjectOwner.ResourceUID AS PMUID

FROM MSP_EpmTask_UserView INNER JOIN
MSP_EpmProject_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID INNER
JOIN
MSP_EpmAssignment_UserView ON
MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID INNER JOIN
MSP_EpmResource_UserView AS Resource ON
Resource.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
MSP_EpmResource_UserView AS ProjectOwner ON
ProjectOwner.ResourceUID = MSP_EpmProject_UserView.ProjectOwnerResourceUID
LEFT JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignmentByDay_UserView.AssignmentUID =
MSP_EpmAssignment_UserView.AssignmentUID LEFT JOIN
MSP_TimeByDay ON MSP_EpmAssignmentByDay_UserView.TimeByDay =
MSP_TimeByDay.TimeByDay


--
Chris Boyd
MS Project
Program Manager

Blog: http://blogs.msdn.com/project_programmability/