PS2007 - How do I extract Administrative time from reporting datab

B

Bill Busby

I've crafted a few reports from the timesheet data and gotten basically what
I'm looking for, a breakdown of each timesheet, resource, projects, tasks,
actuals, custom fields, etc. Just as I was about to do my victory dance I
noticed none of the reports had any references to the Administrative time on
my timesheets. I've done dug through the whole dang schema and can't find any
other hiding places. Does anyone have a sample query on extracting timesheet
administrative details? I can see the data in the cube but the cube is
missing some of the stuff I gotta have, like resource custom fields.

William Busby
Project Management
 
J

Jonathan Sofer

The hours should be there in the timesheet actuals table, the issue is
probably your joins. You will probably have to do an outer join on that
table because it won't have a matching project to join to. I would start
with a single example for one resources timesheet that has hours both
against true projects and administrative time and I would start from the
timesheet actuals table and a few other timesheet tables and build on that
until you build the correct joins and outer joins in order to capture all
the timesheet hours.

Jonathan
 
B

Bill Busby

Johnathan, that was it. All I had to do was an inner join to
MSP_TimesheetProject (so I can pull the project name 'Administrative') and an
outer join to MSP_EpmProjectUserView (so I can pull in the rest of the
project info). Sorry you can't see the victory dance, it's a sight to behold!
 
M

mike.mahoney

Johnathan, that was it. All I had to do was an inner join to
MSP_TimesheetProject (so I can pull the project name 'Administrative') and an
outer join to MSP_EpmProjectUserView (so I can pull in the rest of the
project info). Sorry you can't see the victory dance, it's a sight to behold!
--
William Busby
Project Management





- Show quoted text -
Click to expand...

Bill

It looks as though you are using SRS for timesheet reporting which
clearly requires some sql skills. I thought I would piggy back this
thread to highlight how pathetic out-of-the-box timesheet reporting is
for the less skilled person. The concept of outer joins was clearly
missed by the guys who designed the Timesheet cubes, as it is
impossible to get complete project and admin data together in a single
report and reference enterprise fields.
My frustration was further raised when I tried to get a cost report
based on timesheet actuals. You can't, you can only get one based on
actuals from MSP. This issue gets even more frustrating when you
think about cost admin timesheets or activity plan timesheet based
costs.

The project server 2007 timesheet facility is only 1/2 a solution!

regards

Mike
 
J

Jonathan Sofer

Bill, glad you were able to figure it out.

Mike, I completely simpathize with you and agree that timesheet reporting
out of the box will never really get the job done. I have to do a lot of
SRS reporting to meet the requirements of a lot of organizations. Another
option would be to customize the cube builds to create a master cube with
both the MSP and Timesheet tables but I am not very familiar with that
route.

Jonathan
 
E

Elsa L.

Hi Bill,
would it be asking too much, if I asked you to share the query you worked so
hard to build?...
Thank you in advance.
Elsa
 
Top