Stakeholders want to know projects' monthly distribution data related to cost and work fields from Project Server, for accomplishing the requirement, and I have to find relevant columns from the following views in RDB, and cooperate with the stored procedure dbo.usp_Distribution_By_Periodic_Proportion:
1. MSP_EpmProject_UserView
2. MSP_EpmTask_UserView
3. MSP_EpmAssignment_UserView
I start to retrieve projects' information:
DECLARE @Projects VARCHAR(500)Although the view provides projects' cost, work, and occurred period data, even it can be distributed by monthly period from its date related columns, it won't be a precise information, so stakeholders wish to retrieve tasks' information to achieve the goal:
SET @Projects = 'ProjectName_A,'
SET @Projects = @Projects + 'ProjectName_B,'
SET @Projects = @Projects + 'ProjectName_C,'
SET @Projects = @Projects + 'ProjectName_D,'
SET @Projects = @Projects + 'ProjectName_E,'
SELECT ProjectUID
, ProjectName
, ProjectBaseline1Cost
, ProjectBaseline1Work
, ProjectBaseline0Cost
, ProjectBaseline0Work
, ProjectActualCost
, ProjectActualWork
, ProjectBaseline1StartDate
, ProjectBaseline1FinishDate
, ProjectBaseline0StartDate
, ProjectBaseline0FinishDate
, ProjectActualStartDate
, ProjectActualFinishDate
, ProjectCost
, ProjectWork
, ProjectRemainingCost
, ProjectRemainingWork
, ProjectStartDate
, ProjectFinishDate
FROM RDB.dbo.MSP_EpmProject_UserView
WHERE ProjectName IN (SELECT * FROM dbo.udfSplitStringArray(@Projects))
ORDER BY ProjectName
SELECT PUV.ProjectNameMy original idea is to retrieve more precise date information from MSP_EpmAssignment_UserView, unfortunately, only few assignments' aggregation result mathch with projects' value, not even mention to search for in-depth data from MSP_EpmAssignmentByDay_UserView, otherwise, it could be easier to retrieve monthly distribution result, so I go back to focus on MSP_EpmTask_UserView.
, TUV.TaskOutlineLevel
, TUV.TaskBaseline1Cost
, TUV.TaskBaseline1Work
, TUV.TaskBaseline0Cost
, TUV.TaskBaseline0Work
, TUV.TaskActualCost
, TUV.TaskActualWork
, TUV.TaskCost
, TUV.TaskWork
, TUV.TaskRemainingCost
,TUV.TaskRemainingWork
FROM RDB.dbo.MSP_EpmProject_UserView PUV
JOIN RDB.dbo.MSP_EpmTask_UserView TUV
ON TUV.ProjectUID = PUV.ProjectUID
WHERE PUV.ProjectName IN (SELECT * FROM dbo.udfSplitStringArray(@Projects))
ORDER BY
PUV.ProjectName
, TUV.TaskOutlineLevel
I still found that not every aggregation value of each task level is equal to project's value. For fixing the issue, here is the brief explanation of the logic used in the query:
1. If project’s value is not NULL, then proceed the following steps.
2. Use full-vale comparing to identify which sum value of the task level matches with the project’s, and focus on the level as low as possible that it can provide more accurate date information to distribute by month; if no result, proceed to next step.
3. Some of aggregation value are very close to the projects’, only have slight difference in decimal place. Instead of using full-value comparing, rounding up to integer place could increase possibility of success comparing, and focus on the level as low as possible, for example:
Project Name: ProjectName_A
Project’s Baseline0Cost value: 359.999849
Sum value of Baseline0Cost of its task is 359.999848
Task Outline Level: 2
If no result, proceed to next step.
4. Find the closest result, use its date information and calculate by the proportion of each task’s value against project’s, and focus on the level as low as possible, for example:
(1) Project Name: ProjectName_B
(2) Project’s Baseline0Cost value: 360,709
(3) The closest sum value of Baseline0Cost value: 310,709
(4) Task Outline Level: 2
Calculation: (Each task's Baseline0Cost value) / (3) * (2)
If no result, proceed to next step.
5. Sum value of task is equal to 0 or no data exist in MSP_EpmTask_UserView, use project’s information to distribute value by month, which those relevant columns are calculated from MSP_EpmProject_UserView, lack of detailed date information from task view, it will be expected that its trend lines illustrate smoother than others'.
Once I have the logic for data retrieving process, then I start to generate the query:
DECLARE @ProjectUID UNIQUEIDENTIFIER,After successfully retrieve monthly distribution dataset for projects, I can create a table to display project summary information from MSP_EpmProject_UserView, and further use the dataset to illustrate monthly distribution result by configuring charts in the report. Please see the example as follows:
@ProjectName VARCHAR(500),
@Project_Baseline1Cost FLOAT,
@Project_Baseline1Work FLOAT,
@Project_Baseline0Cost FLOAT,
@Project_Baseline0Work FLOAT,
@Project_ActualCost FLOAT,
@Project_ActualWork FLOAT,
@Project_Baseline1StartDate DATETIME,
@Project_Baseline1FinishDate DATETIME,
@Project_Baseline0StartDate DATETIME,
@Project_Baseline0FinishDate DATETIME,
@Project_ActualStartDate DATETIME,
@Project_ActualFinishDate DATETIME,
@Project_Cost FLOAT,
@Project_Work FLOAT,
@Project_RemainingCost FLOAT,
@Project_RemainingWork FLOAT,
@Project_StartDate DATETIME,
@Project_FinishDate DATETIME,
@Task_TargetLevel_B1C INT,
@Task_TargetLevel_B1W INT,
@Task_TargetLevel_B0C INT,
@Task_TargetLevel_B0W INT,
@Task_TargetLevel_AC INT,
@Task_TargetLevel_AW INT,
@Task_TargetLevel_C INT,
@Task_TargetLevel_W INT,
@Task_TargetLevel_RC INT,
@Task_TargetLevel_RW INT,
@Task_Baseline1Cost FLOAT,
@Task_Baseline1Work FLOAT,
@Task_Baseline0Cost FLOAT,
@Task_Baseline0Work FLOAT,
@Task_ActualCost FLOAT,
@Task_ActualWork FLOAT,
@Task_Baseline1StartDate DATETIME,
@Task_Baseline1FinishDate DATETIME,
@Task_Baseline0StartDate DATETIME,
@Task_Baseline0FinishDate DATETIME,
@Task_ActualStartDate DATETIME,
@Task_ActualFinishDate DATETIME,
@Task_Cost FLOAT,
@Task_Work FLOAT,
@Task_RemainingCost FLOAT,
@Task_RemainingWork FLOAT,
@Task_StartDate DATETIME,
@Task_FinishDate DATETIME,
@TUV_ProportionToProject FLOAT,
@TUV_Baseline1Cost FLOAT,
@TUV_Baseline1Work FLOAT,
@TUV_Baseline0Cost FLOAT,
@TUV_Baseline0Work FLOAT,
@TUV_ActualCost FLOAT,
@TUV_ActualWork FLOAT,
@TUV_Cost FLOAT,
@TUV_Work FLOAT,
@TUV_RemainingCost FLOAT,
@TUV_RemainingWork FLOAT
DECLARE @Monthly_Distribution TABLE
(
ProjectName VARCHAR(500),
Period VARCHAR(50),
Baseline1Cost FLOAT,
Baseline1Work FLOAT,
Baseline0Cost FLOAT,
Baseline0Work FLOAT,
ActualCost FLOAT,
ActualWork FLOAT,
Cost FLOAT,
Work FLOAT,
RemainingCost FLOAT,
RemainingWork FLOAT
)
DECLARE @TUV TABLE
(
ProjectUID UNIQUEIDENTIFIER,
ProjectName VARCHAR(500),
TaskOutlineLevel INT,
TaskBaseline1Cost FLOAT,
TaskBaseline1Work FLOAT,
TaskBaseline0Cost FLOAT,
TaskBaseline0Work FLOAT,
TaskActualCost FLOAT,
TaskActualWork FLOAT,
TaskCost FLOAT,
TaskWork FLOAT,
TaskRemainingCost FLOAT,
TaskRemainingWork FLOAT
)
INSERT INTO @TUV
SELECT PUV.ProjectUID
, PUV.ProjectName
, TUV.TaskOutlineLevel
, SUM(TUV.TaskBaseline1Cost)
, SUM(TUV.TaskBaseline1Work)
, SUM(TUV.TaskBaseline0Cost)
, SUM(TUV.TaskBaseline0Work)
, SUM(TUV.TaskActualCost)
, SUM(TUV.TaskActualWork)
, SUM(TUV.TaskCost)
, SUM(TUV.TaskWork)
, SUM(TUV.TaskRemainingCost)
, SUM(TUV.TaskRemainingWork)
FROM RDB.dbo.MSP_EpmProject_UserView PUV
JOIN RDB.dbo.MSP_EpmTask_UserView TUV
ON TUV.ProjectUID = PUV.ProjectUID
WHERE PUV.ProjectName IN (SELECT * FROM dbo.udfSplitStringArray(@Projects)
GROUP BY
PUV.ProjectUID
, PUV.ProjectName
, TUV.TaskOutlineLevel
--SELECT * FROM @TUV ORDER BY ProjectName, TaskOutlineLevel
--Retrieve project's information one by one
DECLARE cur_projects CURSOR FOR
SELECT ProjectUID
, ProjectName
, ProjectBaseline1Cost
, ProjectBaseline1Work
, ProjectBaseline0Cost
, ProjectBaseline0Work
, ProjectActualCost
, ProjectActualWork
, ProjectBaseline1StartDate
, ProjectBaseline1FinishDate
, ProjectBaseline0StartDate
, ProjectBaseline0FinishDate
, ProjectActualStartDate
, ProjectActualFinishDate
, ProjectCost
, ProjectWork
, ProjectRemainingCost
, ProjectRemainingWork
, ProjectStartDate
, ProjectFinishDate
FROM RDB.dbo.MSP_EpmProject_UserView
WHERE ProjectName IN (SELECT * FROM dbo.udfSplitStringArray(@Projects))
ORDER BY ProjectName
OPEN cur_projects
FETCH NEXT FROM cur_projects INTO @ProjectUID,
@ProjectName,
@Project_Baseline1Cost,
@Project_Baseline1Work,
@Project_Baseline0Cost,
@Project_Baseline0Work,
@Project_ActualCost,
@Project_ActualWork,
@Project_Baseline1StartDate,
@Project_Baseline1FinishDate,
@Project_Baseline0StartDate,
@Project_Baseline0FinishDate,
@Project_ActualStartDate,
@Project_ActualFinishDate,
@Project_Cost,
@Project_Work,
@Project_RemainingCost,
@Project_RemainingWork,
@Project_StartDate,
@Project_FinishDate
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @Project_Baseline1Cost, @Project_Baseline1Work, @Project_Baseline0Cost, @Project_Baseline0Work
--Reset target level value for each loop
SELECT @Task_TargetLevel_B1C = NULL,
@Task_TargetLevel_B1W = NULL,
@Task_TargetLevel_B0C = NULL,
@Task_TargetLevel_B0W = NULL,
@Task_TargetLevel_AC = NULL,
@Task_TargetLevel_AW = NULL,
@Task_TargetLevel_C = NULL,
@Task_TargetLevel_W = NULL,
@Task_TargetLevel_RC = NULL,
@Task_TargetLevel_RW = NULL
--==============================================================
--For Baseline1Cost
--For logic 1, identify if project value is not NULL
IF @Project_Baseline1Cost IS NOT NULL
BEGIN
/*
For logic 2, use differential value to identify if they are the same.
For logic 3, use rounding to prevent differential value in decimal place.
For logic 4, use absolute value and cooperate with TOP 1 and ORDER BY clause to identify the closest result.
For focusing on the level as low as possible, which means looking for the highest value of TaskOutlineLevel based on the coditions above.
Once found the sum value of task level match the conditions, then retrieve target level and sum value.
*/
SELECT @Task_TargetLevel_B1C = A.TaskOutlineLevel, @TUV_Baseline1Cost = A.TaskBaseline1Cost
FROM (
SELECT TOP 1 TaskOutlineLevel, TaskBaseline1Cost
FROM @TUV
WHERE ProjectUID = @ProjectUID
ORDER BY ABS(CONVERT(INT, ISNULL(TaskBaseline1Cost, 0)) - CONVERT(INT, @Project_Baseline1Cost)), TaskOutlineLevel DESC
) A
IF @TUV_Baseline1Cost > 0
BEGIN
--Retrieve the records which belong the target level, and retrieve its cost/work and date information.
--Retrieve tasks' informaton one by one, then calculate each task's monthly distribution by its occurred period.
--In my case, if TaskBaseline1's date is NULL, then use TaskBasline0's.
DECLARE cur_tasks CURSOR FOR SELECT TaskBaseline1Cost, ISNULL(TaskBaseline1StartDate, TaskBaseline0StartDate), ISNULL(TaskBaseline1FinishDate, TaskBaseline0FinishDate)
FROM RDB.dbo.MSP_EpmTask_UserView
WHERE ProjectUID = @ProjectUID
AND TaskOutlineLevel = @Task_TargetLevel_B1C
OPEN cur_tasks
FETCH NEXT FROM cur_tasks INTO @Task_Baseline1Cost, @Task_Baseline1StartDate, @Task_Baseline1FinishDate
WHILE @@FETCH_STATUS = 0
BEGIN
--For logic 2, if they are the same, then use sum value of the task.
--For logic 3 and 4, if they are different, then calculate each task’s proportion of task aggregation value, and further against project’s.
IF @TUV_Baseline1Cost = @Project_Baseline1Cost
BEGIN
INSERT INTO @Monthly_Distribution
(Period, Baseline1Cost)
EXEC dbo.usp_Distribution_By_Periodic_Proportion 'Monthly', @Task_Baseline1Cost, @Task_Baseline1StartDate, @Task_Baseline1FinishDate
END
ELSE
BEGIN
SET @TUV_ProportionToProject = @Task_Baseline1Cost/@TUV_Baseline1Cost*@Project_Baseline1Cost
INSERT INTO @Monthly_Distribution
(Period, Baseline1Cost)
EXEC dbo.usp_Distribution_By_Periodic_Proportion 'Monthly', @TUV_ProportionToProject, @Task_Baseline1StartDate, @Task_Baseline1FinishDate
END
FETCH NEXT FROM cur_tasks INTO @Task_Baseline1Cost, @Task_Baseline1StartDate, @Task_Baseline1FinishDate
END
CLOSE cur_tasks
DEALLOCATE cur_tasks
END
ELSE
BEGIN
--If project's value is not NULL, but no data exist in MSP_EpmTask_UserView, then use project's information to calculate monthly distribution rsult.
INSERT INTO @Monthly_Distribution
(Period, Baseline1Cost)
EXEC dbo.usp_Distribution_By_Periodic_Proportion 'Monthly', @Project_Baseline1Cost, @Project_Baseline1StartDate, @Project_Baseline1FinishDate
END
END
--For other cost/work columns, they all use the same structure above, only need to change column name, parameter, and date.
UPDATE @Monthly_Distribution
SET ProjectName = @ProjectName
WHERE ProjectName IS NULL
FETCH NEXT FROM cur_projects INTO @ProjectUID,
@ProjectName,
@Project_Baseline1Cost,
@Project_Baseline1Work,
@Project_Baseline0Cost,
@Project_Baseline0Work,
@Project_ActualCost,
@Project_ActualWork,
@Project_Baseline1StartDate,
@Project_Baseline1FinishDate,
@Project_Baseline0StartDate,
@Project_Baseline0FinishDate,
@Project_ActualStartDate,
@Project_ActualFinishDate,
@Project_Cost,
@Project_Work,
@Project_RemainingCost,
@Project_RemainingWork,
@Project_StartDate,
@Project_FinishDate
END
CLOSE cur_projects
DEALLOCATE cur_projects
--SELECT * FROM @Monthly_Distribution
SELECT ProjectName,
Period,
[Year] = CONVERT(INT, LEFT(Period, 4)),
[Month] = dbo.udfConvert_Datetime(SUBSTRING(Period, 6, LEN(Period)-5)), --Convert January to 1, February to 2, etc.
Baseline1Cost = SUM(ISNULL(Baseline1Cost, 0)),
Baseline1Work = SUM(ISNULL(Baseline1Work, 0)),
Baseline0Cost = SUM(ISNULL(Baseline0Cost, 0)),
Baseline0Work = SUM(ISNULL(Baseline0Work, 0)),
ActualCost = SUM(ISNULL(ActualCost, 0)),
ActualWork = SUM(ISNULL(ActualWork, 0)),
Cost = SUM(ISNULL(Cost, 0)),
Work = SUM(ISNULL(Work, 0)),
RemainingCost = SUM(ISNULL(RemainingCost, 0)),
RemainingWork = SUM(ISNULL(RemainingWork, 0))
FROM @Monthly_Distribution
WHERE Period IS NOT NULL
GROUP BY
ProjectName,
Period
ORDER BY ProjectName, [YEAR], [MONTH]
--For debugging, check if the sum values are mathch with projects' value
/*
SELECT ProjectName ,
[Baseline1Cost] = SUM(Baseline1Cost),
[Baseline1Work] = SUM(Baseline1Work),
[Baseline0Cost] = SUM(Baseline0Cost),
[Baseline0Work] = SUM(Baseline0Work),
[ActualCost] = SUM(ActualCost),
[ActualWork] = SUM(ActualWork),
[Cost] = SUM(Cost),
[Work] = SUM(Work),
[RemainingCost] = SUM(RemainingCost),
[RemainingWork] = SUM(RemainingWork)
FROM
(
SELECT ProjectName,
Period,
[Year] = CONVERT(INT, LEFT(Period, 4)),
[Month] = dbo.udfConvert_Datetime(SUBSTRING(Period, 6, LEN(Period)-5)), --Convert January to 1, February to 2, etc.
Baseline1Cost = SUM(ISNULL(Baseline1Cost, 0)),
Baseline1Work = SUM(ISNULL(Baseline1Work, 0)),
Baseline0Cost = SUM(ISNULL(Baseline0Cost, 0)),
Baseline0Work = SUM(ISNULL(Baseline0Work, 0)),
ActualCost = SUM(ISNULL(ActualCost, 0)),
ActualWork = SUM(ISNULL(ActualWork, 0)),
Cost = SUM(ISNULL(Cost, 0)),
Work = SUM(ISNULL(Work, 0)),
RemainingCost = SUM(ISNULL(RemainingCost, 0)),
RemainingWork = SUM(ISNULL(RemainingWork, 0))
FROM @Monthly_Distribution
WHERE Period IS NOT NULL
GROUP BY
ProjectName,
Period
ORDER BY ProjectName, [YEAR], [MONTH]
) A
GROUP BY ProjectName
*/
No comments:
Post a Comment