Monthly Billing Report and Timesheet Billing Report

[ss028955]

We are using Project Server 2007. I need to write couple of queries for our
monthly billing report.

Below are the fields that I need for my report.( All the Enterprise custom
fields are Project Fields.)

SSR_Number (Custom Field), Application name (Custom field) , Project Name,
Department code (Custom Field), GL_Code (Custom Field), Project Code,
(Custom Field), Actual Hrs spent within the last month, Actual Amount spent
within the last month.

SSR_Number (Custom Field), Application name (Custom field) , Project Name,
Department code (Custom Field), GL_Code (Custom Field), Project Code (Custom
Field), Actual Hours, Actual Amount,Estimated Hrs, Estimated Amount ,
Variance filter by filter by Date user entered their Hours in Timesheet
(Timesheet Period date)

Can some one Help?

Regards
ss028955

 

[Piet Remen]

Hello there. This should hopefully give you a good head start. Assuming you
have SQL knowledge you can change this stored procedure to suit your needs. I
tried to guess your custom field names for the environment but you may need
to tweak the SQL until it works. The main views in project server dynamically
generate the custom field data with the same name you created for that field.
This query allows you to pass in a start and end date, or even a single
project UID so you can accrue cost data from daily timephased project server
data. Once you get the query working, take a look at each view I am linking
on to get an idea of what data is contained within them

/*********************************************************/

CREATE PROCEDURE [dbo].[BillingReportExample]
(
@vuidProjectID UNIQUEIDENTIFIER = NULL
,@vdtmStartDate DATETIME = NULL
,@vdtmEndDate DATETIME = NULL
)
AS

SET NOCOUNT ON
--******************************************************
-- T E S T I N G V A R I A B L E D E C L A R A T I O N
--******************************************************

--DECLARE @vuidProjectID UNIQUEIDENTIFIER
--DECLARE @vdtmStartDate DATETIME
--DECLARE @vdtmEndDate DATETIME
--
--SET @vuidProjectID = NULL
--SET @vdtmStartDate = NULL
--SET @vdtmStartDate = '01 Jan 2008'
--SET @vdtmEndDate = NULL
--SET @vdtmEndDate = '31 Mar 2008'

--******************************************************

SELECT
p.[SSR_Number]
,p.[Application name]
,p.ProjectName
,p.[Department code]
,p.[GL_Code]
,p.[Project Code]
,p.[SSR_Number]
,p.[Department code]
,p.ProjectVariance
,r.ResourceName
,SUM(ISNULL(abd.AssignmentCost,0)) AS AssignmentForecastedCost
,SUM(ISNULL(abd.AssignmentWork,0)) AS AssignmentForecastedWork
,SUM(ISNULL(abd.AssignmentActualCost,0)) AS AssignmentActualCost
,SUM(ISNULL(abd.AssignmentActualWork,0)) AS AssignmentActualWork
FROM ProjectServer_Reporting.dbo.MSP_EpmProject_UserView p
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmTask_UserView t
ON p.ProjectUID = t.ProjectUID
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView a
ON p.ProjectUID = a.ProjectUID
AND t.TaskUID = a.TaskUID
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmResource_UserView r
ON a.ResourceUID = r.ResourceUID
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView AS abd
ON a.ProjectUID = abd.ProjectUID
AND a.TaskUID = abd.TaskUID
AND a.AssignmentUID = abd.AssignmentUID
WHERE
((abd.TimeByDay >= @vdtmStartDate OR @vdtmStartDate IS NULL)
AND (abd.TimeByDay <= @vdtmEndDate OR @vdtmEndDate IS NULL)
AND (p.ProjectUID = @vuidProjectID OR @vuidProjectID IS NULL)
AND t.TaskIsProjectSummary = 0
GROUP BY
p.[SSR_Number]
,p.[Application name]
,p.ProjectName
,p.[Department code]
,p.[GL_Code]
,p.[Project Code]
,p.[SSR_Number]
,p.[Department code]
,p.ProjectVariance
,r.ResourceName
ORDER BY
p.ProjectName

/*********************************************************/


Happy reporting

Piet Remen
Solutioin Specialist
Strategic Data Management

 

[ss028955]

Thanks Piet. It worked great. I really appriciate your help.

Ss028955

Hello there. This should hopefully give you a good head start. Assuming you
have SQL knowledge you can change this stored procedure to suit your needs. I
tried to guess your custom field names for the environment but you may need
to tweak the SQL until it works. The main views in project server dynamically
generate the custom field data with the same name you created for that field.
This query allows you to pass in a start and end date, or even a single
project UID so you can accrue cost data from daily timephased project server
data. Once you get the query working, take a look at each view I am linking
on to get an idea of what data is contained within them

/*********************************************************/

CREATE PROCEDURE [dbo].[BillingReportExample]
(
@vuidProjectID UNIQUEIDENTIFIER = NULL
,@vdtmStartDate DATETIME = NULL
,@vdtmEndDate DATETIME = NULL
)
AS

SET NOCOUNT ON
--******************************************************
-- T E S T I N G V A R I A B L E D E C L A R A T I O N
--******************************************************

--DECLARE @vuidProjectID UNIQUEIDENTIFIER
--DECLARE @vdtmStartDate DATETIME
--DECLARE @vdtmEndDate DATETIME
--
--SET @vuidProjectID = NULL
--SET @vdtmStartDate = NULL
--SET @vdtmStartDate = '01 Jan 2008'
--SET @vdtmEndDate = NULL
--SET @vdtmEndDate = '31 Mar 2008'

--******************************************************

SELECT
p.[SSR_Number]
,p.[Application name]
,p.ProjectName
,p.[Department code]
,p.[GL_Code]
,p.[Project Code]
,p.[SSR_Number]
,p.[Department code]
,p.ProjectVariance
,r.ResourceName
,SUM(ISNULL(abd.AssignmentCost,0)) AS AssignmentForecastedCost
,SUM(ISNULL(abd.AssignmentWork,0)) AS AssignmentForecastedWork
,SUM(ISNULL(abd.AssignmentActualCost,0)) AS AssignmentActualCost
,SUM(ISNULL(abd.AssignmentActualWork,0)) AS AssignmentActualWork
FROM ProjectServer_Reporting.dbo.MSP_EpmProject_UserView p
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmTask_UserView t
ON p.ProjectUID = t.ProjectUID
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmAssignment_UserView a
ON p.ProjectUID = a.ProjectUID
AND t.TaskUID = a.TaskUID
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmResource_UserView r
ON a.ResourceUID = r.ResourceUID
INNER JOIN ProjectServer_Reporting.dbo.MSP_EpmAssignmentByDay_UserView AS abd
ON a.ProjectUID = abd.ProjectUID
AND a.TaskUID = abd.TaskUID
AND a.AssignmentUID = abd.AssignmentUID
WHERE
((abd.TimeByDay >= @vdtmStartDate OR @vdtmStartDate IS NULL)
AND (abd.TimeByDay <= @vdtmEndDate OR @vdtmEndDate IS NULL)
AND (p.ProjectUID = @vuidProjectID OR @vuidProjectID IS NULL)
AND t.TaskIsProjectSummary = 0
GROUP BY
p.[SSR_Number]
,p.[Application name]
,p.ProjectName
,p.[Department code]
,p.[GL_Code]
,p.[Project Code]
,p.[SSR_Number]
,p.[Department code]
,p.ProjectVariance
,r.ResourceName
ORDER BY
p.ProjectName

/*********************************************************/


Happy reporting

Piet Remen
Solutioin Specialist
Strategic Data Management

 

[Piet Remen]

Awesome. Glad to help. You'll find that query a great template for general PS
reporting.

Piet Remen
Solutioin Specialist
Strategic Data Management