Check for duplicate assignments as the OLAP cube does not pick these up. If
users have entered time against the dupe then it will not reflect in the cube.
Run the following query against the Project Server database to ascertain
whether there are any dupes:-
--To get the duplicate web-assignments
CREATE PROCEDURE dbo.MS_PSS_GetDuplicateWebAssignmentList_Details
AS
CREATE TABLE #DuplicateAssignmentsList
(
ProjectName nvarchar(500),
ResourceName nvarchar(500),
Assignment nvarchar(500),
WASSN_ID int,
WASSN_REMOVED_BY_RESOURCE int,
CreatedViaSelfAssign bit,
WASSN_CREATED_BY_RES int,
ASSN_ACT_WORK float,
WebActualWorkTotal float,
LastDayAWTracked datetime,
WASSN_SEND_UPDATE_DATE datetime,
NumberOfDuplicates int,
ProjectManager nvarchar(500),
PendingPMApproval int,
PendingResourceSubmission int,
PROJ_CREATION_DATE datetime,
WPROJ_LAST_PUB datetime
)
DECLARE @WASSN_ID int
DECLARE @WPROJ_ID int
DECLARE @WRES_ID int
DECLARE @TASK_NAME varchar(500)
DECLARE @TASK_UID int
DECLARE @TASK_UID_SUMMARY int
DECLARE @TASK_IS_SUMMARY int
DECLARE @WASSN_REMOVED_BY_RESOURCE int
DECLARE @WASSN_DELETED_IN_PROJ int
DECLARE @WASSN_ASSIGNED_TO_EXISTING int
DECLARE @WASSN_CREATED_BY_RES int
DECLARE @ASSN_ACT_WORK decimal
DECLARE @WASSN_SEND_UPDATE_DATE datetime
DECLARE @WRES_ID_MGR int --PM id
DECLARE @WASSN_ACTUALS_PENDING int --manager has pending updates
DECLARE @WASSN_UPDATE_STATUS int -- resource has updated, but not send to
manager
DECLARE WebAssignments CURSOR FOR
SELECT WASSN_ID,
WPROJ_ID,
WRES_ID,
TASK_NAME,
TASK_UID,
TASK_UID_SUMMARY,
TASK_IS_SUMMARY
, WASSN_REMOVED_BY_RESOURCE,
WASSN_DELETED_IN_PROJ,
WASSN_ASSIGNED_TO_EXISTING,
WASSN_CREATED_BY_RES,
ASSN_ACT_WORK,
WASSN_SEND_UPDATE_DATE,
WRES_ID_MGR,
WASSN_ACTUALS_PENDING,
WASSN_UPDATE_STATUS FROM MSP_WEB_ASSIGNMENTS
OPEN WebAssignments
FETCH NEXT FROM WebAssignments
INTO @WASSN_ID,
@WPROJ_ID,
@WRES_ID,
@TASK_NAME,
@TASK_UID,
@TASK_UID_SUMMARY,
@TASK_IS_SUMMARY,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_DELETED_IN_PROJ,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK,
@WASSN_SEND_UPDATE_DATE,
@WRES_ID_MGR,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DuplicateCount int
SET @DuplicateCount = 0
IF @WASSN_DELETED_IN_PROJ <> 1 AND @TASK_IS_SUMMARY = 0
BEGIN
SET @DuplicateCount = (SELECT COUNT(*) FROM MSP_WEB_ASSIGNMENTS WHERE
TASK_UID = @TASK_UID
--AND (TASK_UID_SUMMARY = @TASK_UID_SUMMARY OR @TASK_UID_SUMMARY IS
NULL)
AND TASK_IS_SUMMARY = 0
AND WPROJ_ID = @WPROJ_ID
AND WRES_ID = @WRES_ID
AND WASSN_DELETED_IN_PROJ = 0)
END
--temp code to test by deleting all triplicates/quadruplicates
IF (@DuplicateCount >2)
BEGIN
declare @temp int
--UPDATE MSP_WEB_ASSIGNMENTS SET RESERVED_DATA1 = 121212 WHERE WASSN_ID
= @WASSN_ID
--DELETE FROM MSP_WEB_ASSIGNMENTS WHERE RESERVED_DATA1 = 121212
END
IF (@DuplicateCount >1)
BEGIN
DECLARE @PROJECT_NAME nvarchar(500)
SET @PROJECT_NAME = (SELECT PROJ_NAME FROM MSP_WEB_PROJECTS WHERE
WPROJ_ID = @WPROJ_ID)
DECLARE @RESOURCE_NAME nvarchar(500)
SET @RESOURCE_NAME = (SELECT RES_NAME FROM MSP_WEB_RESOURCES WHERE
WRES_ID = @WRES_ID)
DECLARE @ProjectManager nvarchar(500)
SET @ProjectManager = (SELECT RES_NAME FROM MSP_WEB_RESOURCES WHERE
WRES_ID = @WRES_ID_MGR)
DECLARE @WebActualWorkTotal float
DECLARE @LastDayAWTracked datetime
DECLARE @WPROJ_LAST_PUB datetime
SET @WPROJ_LAST_PUB = (SELECT WPROJ_LAST_PUB FROM MSP_WEB_PROJECTS WHERE
WPROJ_ID = @WPROJ_ID)
DECLARE @PROJ_CREATION_DATE datetime
SET @PROJ_CREATION_DATE = (SELECT PROJ_CREATION_DATE FROM MSP_PROJECTS
WHERE PROJ_ID = (SELECT PROJ_ID FROM MSP_WEB_PROJECTS WHERE WPROJ_ID =
@WPROJ_ID))
-- SELECT PROJ_CREATION_DATE FROM MSP_PROJECTS WHERE PROJ_ID = -1
EXEC MS_PSS_GetWebWorkTotalByWassnID @WASSN_ID, @WebActualWorkTotal
OUTPUT, @LastDayAWTracked OUTPUT
INSERT INTO #DuplicateAssignmentsList
(
ProjectName,
ResourceName,
Assignment,
WASSN_ID,
WASSN_REMOVED_BY_RESOURCE,
CreatedViaSelfAssign,
WASSN_CREATED_BY_RES,
ASSN_ACT_WORK,
WebActualWorkTotal,
LastDayAWTracked,
WASSN_SEND_UPDATE_DATE,
NumberOfDuplicates,
ProjectManager,
PendingPMApproval,
PendingResourceSubmission,
PROJ_CREATION_DATE,
WPROJ_LAST_PUB
)
VALUES(
@PROJECT_NAME,
@RESOURCE_NAME,
@TASK_NAME,
@WASSN_ID,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK/60/1000,
@WebActualWorkTotal/60/1000,
@LastDayAWTracked,
@WASSN_SEND_UPDATE_DATE,
@DuplicateCount,
@ProjectManager,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS,
@PROJ_CREATION_DATE,
@WPROJ_LAST_PUB
)
END
FETCH NEXT FROM WebAssignments
INTO @WASSN_ID,
@WPROJ_ID,
@WRES_ID,
@TASK_NAME,
@TASK_UID,
@TASK_UID_SUMMARY,
@TASK_IS_SUMMARY,
@WASSN_REMOVED_BY_RESOURCE,
@WASSN_DELETED_IN_PROJ,
@WASSN_ASSIGNED_TO_EXISTING,
@WASSN_CREATED_BY_RES,
@ASSN_ACT_WORK,
@WASSN_SEND_UPDATE_DATE,
@WRES_ID_MGR,
@WASSN_ACTUALS_PENDING,
@WASSN_UPDATE_STATUS
END
CLOSE WebAssignments
DEALLOCATE WebAssignments
SELECT NumberOfDuplicates,
ProjectName,
ResourceName,
Assignment,
WASSN_ID,
CreatedViaSelfAssign,
WASSN_CREATED_BY_RES,
WebActualWorkTotal,
LastDayAWTracked,
ProjectManager,
PendingPMApproval,
PendingResourceSubmission,
WASSN_SEND_UPDATE_DATE,
PROJ_CREATION_DATE,
WPROJ_LAST_PUB
FROM #DuplicateAssignmentsList
ORDER BY NumberOfDuplicates, ProjectName, ResourceName, Assignment asc
DROP TABLE #DuplicateAssignmentsList
--To Delete the duplicate web-assignements
CREATE PROCEDURE dbo.MS_PSS_DeleteWebAssignmentByAssnID
@wassn_id_remove varchar(200)
AS
DELETE FROM MSP_WEB_ASSIGNMENTS WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK_ADJUSTED WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORK_APPROVAL WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_WORKGROUP_FIELDS WHERE WASSN_ID=@wassn_id_remove
DELETE FROM MSP_WEB_TRANSACTIONS WHERE WASSN_ID=@wassn_id_remove
