Timsheet Billing Report

S

ss028955

Hi,

I am having trouble creating query to produce Timeseet billing report with
the fields below.

SSR_Number (Custom Field), Application name (Custom field) , Project Name,
Department code (Custom Field), GL_Code (Custom Field), Capex Code (Custom
Field), Product Code (Custom Field), Actual Hours, Actual Amount, Estimated
Hrs, Estimated Amount , Timesheet Period

Additional Information:

The first query below is without Timesheet Period Name which is working
fine.But as soon as I link the query with timesheets table (Query 2 below) to
add timesheet period, the value just doubles up. Can someone help?

Query1: Query without timesheet.

SELECT TOP 100 PERCENT p.SSR_Number, p.Application_Name, p.ProjectName,
p.GL_Code, p.Project_Code, p.Property, p.Project_Status, p.Department,
CONVERT(varchar(7), abd.TimeByDay, 111) AS TimeByDay,
abd.AssignmentCost AS Estimated_Cost, abd.AssignmentWork AS Estimated_Hour,
abd.AssignmentActualCost AS Actual_Cost,
abd.AssignmentActualWork AS Actual_Hour
FROM dbo.MSP_EpmProject_UserView p INNER JOIN
dbo.MSP_EpmTask_UserView t ON p.ProjectUID =
t.ProjectUID INNER JOIN
dbo.MSP_EpmAssignment_UserView a ON p.ProjectUID =
a.ProjectUID AND t.TaskUID = a.TaskUID INNER JOIN
dbo.MSP_EpmAssignmentByDay_UserView abd ON
a.ProjectUID = abd.ProjectUID AND a.TaskUID = abd.TaskUID AND
a.AssignmentUID = abd.AssignmentUID
WHERE (t.TaskIsProjectSummary = 0)
ORDER BY p.ProjectName

Query 2: Modified Query with Timesheet Period Name: It is not returning
correct value

SELECT TOP 100 PERCENT p.SSR_Number, p.Application_Name, p.ProjectName,
p.GL_Code, p.Project_Code, p.Property, p.Project_Status, p.Department,
CONVERT(varchar(7), abd.TimeByDay, 111) AS TimeByDay,
abd.AssignmentCost AS Estimated_Cost, abd.AssignmentWork AS Estimated_Hour,
abd.AssignmentActualCost AS Actual_Cost,
abd.AssignmentActualWork AS Actual_Hour, dbo.MSP_TimesheetPeriod.PeriodName
FROM dbo.MSP_Timesheet INNER JOIN
dbo.MSP_TimesheetLine ON
dbo.MSP_Timesheet.TimesheetUID = dbo.MSP_TimesheetLine.TimesheetUID INNER JOIN
dbo.MSP_TimesheetProject ON
dbo.MSP_TimesheetLine.ProjectNameUID =
dbo.MSP_TimesheetProject.ProjectNameUID INNER JOIN
dbo.MSP_EpmProject_UserView p INNER JOIN
dbo.MSP_EpmTask_UserView t ON p.ProjectUID =
t.ProjectUID INNER JOIN
dbo.MSP_EpmAssignment_UserView a ON p.ProjectUID =
a.ProjectUID AND t.TaskUID = a.TaskUID INNER JOIN
dbo.MSP_EpmAssignmentByDay_UserView abd ON
a.ProjectUID = abd.ProjectUID AND a.TaskUID = abd.TaskUID AND
a.AssignmentUID = abd.AssignmentUID ON
dbo.MSP_TimesheetProject.ProjectUID = abd.ProjectUID INNER JOIN
dbo.MSP_TimesheetPeriod ON dbo.MSP_Timesheet.PeriodUID
= dbo.MSP_TimesheetPeriod.PeriodUID
WHERE (t.TaskIsProjectSummary = 0)
ORDER BY p.ProjectName


Thanks in Advance.

ss028955
 
S

Stephen Sanderlin

Your query is WAY too complicated. You have TONS of joins that you
don't need.

Try using this query against your Reporting DB:

SELECT
EPUV.SSR_Number,
EPUV.Application_Name,
EPUV.ProjectName,
EPUV.GL_Code,
EPUV.Project_Code,
EPUV.Property,
EPUV.Project_Status,
EPUV.Department,
CONVERT(varchar(7), EABDUV.TimeByDay, 111) AS TimeByDay,
EABDUV.AssignmentCost AS Estimated_Cost,
EABDUV.AssignmentWork AS Estimated_Hour,
EABDUV.AssignmentActualCost AS Actual_Cost,
EABDUV.AssignmentActualWork AS Actual_Hour,
(SELECT
TPOV.PeriodName
FROM
MSP_TimesheetPeriod_OlapView TPOV
WHERE
EABDUV.TimeByDay >= TPOV.StartDate
AND
EABDUV.TimeByDay <= TPOV.EndDate) AS Period_Name
FROM
MSP_EpmAssignmentByDay_UserView EABDUV
INNER JOIN
MSP_EpmProject_UserView EPUV ON EPUV.ProjectUID = EABDUV.ProjectUID
WHERE
(SELECT
TPOV.PeriodName
FROM
MSP_TimesheetPeriod_OlapView TPOV
WHERE
EABDUV.TimeByDay >= TPOV.StartDate
AND
EABDUV.TimeByDay <= TPOV.EndDate) IS NOT NULL
ORDER BY
EPUV.ProjectName

Let me know how it works out for you.

--
Stephen Sanderlin
Principal Consultant
MSProjectExperts
For Project Server Consulting: http://www.msprojectexperts.com
For Project Server FAQS: http://www.projectserverexperts.com

Owner/Founder - EPMFAQ
http://www.epmfaq.com/
http://forums.epmfaq.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.
 
S

ss028955

Thanks Stephen, I appriciate your help.

I am testing the query at the moment. I will let you know by tomorrow.

ss028955
 
S

ss028955

Hi Stephen,

The report seems to double up the timesheet billing hours. Example I have
added in "Project Server Project 30 hours for month of june but the report
shows 60 hours in total. This is happening to every single project. Any idea?

Thanks in advance.
 
S

ss028955

Thanks Stephen. You are right. The Query is returning correct values.

I had a very close look and figured out that the query was capturing actual
time i.e. billable and non billable time. Thats what I missed.

I approciate your help.

Sorry one more question: I now need to seperate nonbillable and billable
hours/cost with all the fields that we included in that field? Any help?

ss028955
 
Top