none
Resource Availability report via Excel services RRS feed

  • Question

  • Hello, 

    I want to create a report similar to the resource availability report in resource center, i've done assignment work and capacity parts but i cannot calculate availability. İ tried to make calculation (capacity - assignmentwork) results are not correct.

    Here is my code;

    (SELECT     MSP_EpmResource_UserView.ResourceName, 
    MSP_EpmProject_UserView.ProjectName as [RowType], 
    DATENAME(month, MSP_EpmAssignmentByDay_UserView.TimeByDay) as [Month],
    YEAR(MSP_EpmAssignmentByDay_UserView.TimeByDay)  AS [Year],
    
                       
    ISNULL(SUM(MSP_EpmAssignmentByDay_UserView.AssignmentWork),0) AS [Value]
                         
    
    FROM         MSP_EpmResource_UserView INNER JOIN
                          MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON 
    MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID 
    INNER 
    JOIN
                          MSP_EpmAssignment ON MSP_EpmTask_UserView.ProjectUID = 
    MSP_EpmAssignment.ProjectUID AND
                          MSP_EpmTask_UserView.TaskUID = 
    MSP_EpmAssignment.TaskUID ON
                          MSP_EpmResource_UserView.ResourceUID = 
    MSP_EpmAssignment.ResourceUID INNER JOIN
                          MSP_EpmAssignmentByDay_UserView ON 
    MSP_EpmAssignment.AssignmentUID = 
    MSP_EpmAssignmentByDay_UserView.AssignmentUID
    WHERE      (MSP_EpmAssignmentByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, - 3, CURRENT_TIMESTAMP), 102))               
     AND (MSP_EpmAssignmentByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 10, CURRENT_TIMESTAMP), 102))
    
    GROUP BY MSP_EpmResource_UserView.ResourceName, 
    DATENAME(month, MSP_EpmAssignmentByDay_UserView.TimeByDay),
    YEAR(MSP_EpmAssignmentByDay_UserView.TimeByDay) ,
                          MSP_EpmProject_UserView.ProjectName)
                          
      UNION
    
    
    (SELECT     
    MSP_EpmResource_UserView.ResourceName, 
    'Capacity' as [RowType],
    DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay) as [Month],  
    YEAR(MSP_EpmResourceByDay_UserView.TimeByDay) as [Year], 
                 
    SUM(MSP_EpmResourceByDay_UserView.Capacity) AS [Value]
    FROM         
    MSP_EpmResource_UserView 
    LEFT OUTER JOIN MSP_EpmResourceByDay_UserView 
    ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmResourceByDay_UserView.ResourceUID
    WHERE     
    (MSP_EpmResourceByDay_UserView.TimeByDay > CONVERT(DATETIME, DATEADD(month, - 3, CURRENT_TIMESTAMP), 102))               
     AND (MSP_EpmResourceByDay_UserView.TimeByDay < CONVERT(DATETIME, DATEADD(month, 10, CURRENT_TIMESTAMP), 102))
    
    
    GROUP BY MSP_EpmResource_UserView.ResourceName, 
    DATENAME(month, MSP_EpmResourceByDay_UserView.TimeByDay),
    YEAR(MSP_EpmResourceByDay_UserView.TimeByDay))
    
    

    Tuesday, March 5, 2013 3:01 PM