none
status manager field report in pwa 2010 RRS feed

  • Question

  • From Ms project or project server I am trying to get a report on status managers on tasks. 

    is there a way I can get status managers listed on the tasks in a report other than Opening all projects individually?

    Thursday, April 16, 2015 4:16 PM

Answers

  • Hi Bernhard,

    Maybe I'm missing something but as far as I know, the status manager information is actually in the Reporting DB. [dbo.MSP_EpmTask_UserView] table in the Reporting DB contains the TaskStatusManagerUID. The making a join with the [dbo.MSP_EpmResource] with the resourceUID, it should be possible to create a report.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, April 17, 2015 7:30 AM
    Moderator
  • Hi Guillaume,

    you are right. I have unfortunately overlooked this field. Sorry for the confusion!

    @Project help:

    You could develop an Excel Report with the following example of SQL Query:

    Connect with your ProjectServer_Reporting DB

    SELECT     dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName,
                          dbo.MSP_EpmResource_UserView.ResourceName AS [Status Manager]
    FROM         dbo.MSP_EpmTask_UserView INNER JOIN
                          dbo.MSP_EpmResource_UserView ON dbo.MSP_EpmTask_UserView.TaskStatusManagerUID = dbo.MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmTask_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID

    Regards

    Bernhard Fischer


    Bernhard Fischer [MVP] | www.wodey.de

    Friday, April 17, 2015 8:59 AM
  • Ah! did not see your last comment. Anyway, if you need it, it is here http://1drv.ms/1bjfyIM


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, April 22, 2015 5:00 PM
    Moderator
  • The answer to your question is to write your own SQL query. You can write a SQL query to query the data anywhich you want.

    Also, for the SQL query option to appear , You have to pick a template that is Query based. You cannot modify an OLAP based template. So, please use the template that is available directly under BI Center >> Templates, and not an OLAP cube based template.

    You could also start from Excel, connect to the SQL Server, and add your query and then publish the report to BI Center. here is an example: http://www.prasannaadavi.com/2011/08/create-report-of-users-and-security.html

    I think this document will be helpful to understand as to how BI was set up in 2010: http://download.microsoft.com/download/7/7/6/7769F459-8D80-4338-A4E0-E06ABC83C1FE/Microsoft%20Project%20Server%202010%20Reporting%20with%20Excel%20Services.pdf


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Wednesday, April 22, 2015 7:22 PM
    Wednesday, April 22, 2015 6:43 PM
    Moderator
  • try this: http://1drv.ms/1KdEhum

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, April 24, 2015 8:22 PM
    Friday, April 24, 2015 7:31 PM
    Moderator
  • Ah! for some reason my changes did not save.

    try this: http://1drv.ms/1E1Yyhg


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, May 1, 2015 6:12 PM
    Friday, May 1, 2015 4:04 PM
    Moderator
  • Hmm... I am not sure at this point.. seems like the name of the fields is different in the database

    Anyway, try this query one last time, as is: http://1drv.ms/1E1Yyhg


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, May 1, 2015 6:07 PM
    Friday, May 1, 2015 5:43 PM
    Moderator
  • try now. http://1drv.ms/1E1Yyhg

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, May 1, 2015 8:03 PM
    Friday, May 1, 2015 6:25 PM
    Moderator

All replies

  • Hi,

    in Project Server 2010 the Status Manager Information is stored in the ProjectServer_Published DB. But for reporting you have only access to the ProjectServer_Reporting DB.

    So the answer is no. You have to open each schedule.

    Regards

    Bernhard Fischer


    Bernhard Fischer [MVP] | www.wodey.de

    Thursday, April 16, 2015 6:24 PM
  • Hi Bernhard,

    Maybe I'm missing something but as far as I know, the status manager information is actually in the Reporting DB. [dbo.MSP_EpmTask_UserView] table in the Reporting DB contains the TaskStatusManagerUID. The making a join with the [dbo.MSP_EpmResource] with the resourceUID, it should be possible to create a report.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, April 17, 2015 7:30 AM
    Moderator
  • Hi Guillaume,

    you are right. I have unfortunately overlooked this field. Sorry for the confusion!

    @Project help:

    You could develop an Excel Report with the following example of SQL Query:

    Connect with your ProjectServer_Reporting DB

    SELECT     dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName,
                          dbo.MSP_EpmResource_UserView.ResourceName AS [Status Manager]
    FROM         dbo.MSP_EpmTask_UserView INNER JOIN
                          dbo.MSP_EpmResource_UserView ON dbo.MSP_EpmTask_UserView.TaskStatusManagerUID = dbo.MSP_EpmResource_UserView.ResourceUID INNER JOIN
                          dbo.MSP_EpmProject_UserView ON dbo.MSP_EpmTask_UserView.ProjectUID = dbo.MSP_EpmProject_UserView.ProjectUID

    Regards

    Bernhard Fischer


    Bernhard Fischer [MVP] | www.wodey.de

    Friday, April 17, 2015 8:59 AM
  • Thank you for your replies!

    Is there a way I can add this field to Business Intelligence reports because PM would like to see this information anytime they want and they are not familiar with SQL reporting.


    Tuesday, April 21, 2015 3:46 PM
  • Can I use the below method to add status manager By using the query above mentioned

    http://msprojectnow.com/blog/create-a-new-excel-report-from-existing-modifying-odc-excerpt-from-business-intelligence-course

    Tuesday, April 21, 2015 8:17 PM
  • Yes. You can use the query Bernard provided and this same method, to create a new report.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Tuesday, April 21, 2015 8:42 PM
    Moderator
  • Hi Prasanna,

    When I am using the above query it only give me three fields 

    1)project Name

    2) Status manager

    3) Task Name

    I want to add status manager field to system generated report called Project actuals 

    when I follow the method it gives me no query in command text instead  shows MSP_Portfolio_Analyzer

    how Can i add that  filed to this?

    Wednesday, April 22, 2015 3:24 PM
  • Ah! That's because that report was generated using the OLAP cube.

    If you want the status manager field, you will have to modify the query by Bernard above, and add the fields that appear in the report, and build a new excel report like we did on the other report.

    I do not have PS2010 available at the moment, so please give me a screenshot of the report, and I will try to write the query for you.


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, April 22, 2015 3:54 PM
    Moderator
  • I got a Sample template which looks almost same called Projects and assignments

    we can add status manager field there. Below is the query

        SELECT                MSP_EpmProject_UserView.ProjectUID as [ProjectUID],                MSP_EpmProject_UserView.ProjectName as [ProjectName],                MSP_EpmProject_UserView.ProjectAuthorName as [ProjectAuthorName],                MSP_EpmProject_UserView.ProjectOwnerResourceUID as [ProjectOwnerResourceUID],                MSP_EpmProject_UserView.ProjectStartDate as [ProjectStartDate],                MSP_EpmProject_UserView.ProjectFinishDate as [ProjectFinishDate],                MSP_EpmProject_UserView.ProjectStatusDate as [ProjectStatusDate],                MSP_EpmProject_UserView.ProjectManagerName as [ProjectManagerName],                MSP_EpmProject_UserView.ProjectType as [ProjectType],                MSP_EpmProject_UserView.ProjectWorkspaceInternalHRef as [ProjectWorkspaceInternalHRef],                MSP_EpmProject_UserView.ProjectWbsIsStale as [ProjectWbsIsStale],                MSP_EpmProject_UserView.ProjectEarnedValueIsStale as [ProjectEarnedValueIsStale],                MSP_EpmProject_UserView.ProjectRollupsAreStale as [ProjectRollupsAreStale],                MSP_EpmProject_UserView.ProjectHierarchyNotSynchronized as [ProjectHierarchyNotSynchronized],                MSP_EpmProject_UserView.ProjectCalculationsAreStale as [ProjectCalculationsAreStale],                MSP_EpmProject_UserView.ProjectGhostTaskAreStale as [ProjectGhostTaskAreStale],                MSP_EpmProject_UserView.ProjectCurrency as [ProjectCurrency],                MSP_EpmProject_UserView.ResourcePlanUtilizationType as [ResourcePlanUtilizationType],                MSP_EpmProject_UserView.ResourcePlanUtilizationDate as [ResourcePlanUtilizationDate],                MSP_EpmProject_UserView.ProjectCreatedRevisionCounter as [ProjectCreatedRevisionCounter],                MSP_EpmProject_UserView.ProjectModifiedRevisionCounter as [ProjectModifiedRevisionCounter],                MSP_EpmProject_UserView.ProjectCreatedDate as [ProjectCreatedDate],                MSP_EpmProject_UserView.ProjectModifiedDate as [ProjectModifiedDate],                MSP_EpmProject_UserView.ProjectCalendarDuration as [ProjectCalendarDuration],                MSP_EpmProject_UserView.ParentProjectUID as [ParentProjectUID],                MSP_EpmProject_UserView.ProjectFixedCost as [ProjectFixedCost],                MSP_EpmProject_UserView.ProjectCost as [ProjectCost],                MSP_EpmProject_UserView.ProjectOvertimeCost as [ProjectOvertimeCost],                MSP_EpmProject_UserView.ProjectActualCost as [ProjectActualCost],                MSP_EpmProject_UserView.ProjectActualOvertimeCost as [ProjectActualOvertimeCost],                MSP_EpmProject_UserView.ProjectWork as [ProjectWork],                MSP_EpmProject_UserView.ProjectOvertimeWork as [ProjectOvertimeWork],                MSP_EpmProject_UserView.ProjectActualWork as [ProjectActualWork],                MSP_EpmProject_UserView.ProjectActualOvertimeWork as [ProjectActualOvertimeWork],                MSP_EpmProject_UserView.ProjectDurationVariance as [ProjectDurationVariance],                MSP_EpmProject_UserView.ProjectStartVariance as [ProjectStartVariance],                MSP_EpmProject_UserView.ProjectFinishVariance as [ProjectFinishVariance],                MSP_EpmProject_UserView.ProjectDuration as [ProjectDuration],                MSP_EpmProject_UserView.ProjectActualDuration as [ProjectActualDuration],                MSP_EpmProject_UserView.ProjectActualStartDate as [ProjectActualStartDate],                MSP_EpmProject_UserView.ProjectActualFinishDate as [ProjectActualFinishDate],                MSP_EpmProject_UserView.ProjectPercentCompleted as [ProjectPercentCompleted],                MSP_EpmProject_UserView.ProjectPercentWorkCompleted as [ProjectPercentWorkCompleted],                MSP_EpmProject_UserView.ProjectACWP as [ProjectACWP],                MSP_EpmProject_UserView.ProjectBCWP as [ProjectBCWP],                MSP_EpmProject_UserView.ProjectBCWS as [ProjectBCWS],                MSP_EpmProject_UserView.ProjectSPI as [ProjectSPI],                MSP_EpmProject_UserView.ProjectTCPI as [ProjectTCPI],                MSP_EpmProject_UserView.ProjectVAC as [ProjectVAC],                MSP_EpmProject_UserView.ProjectEAC as [ProjectEAC],                MSP_EpmProject_UserView.ProjectCostVariance as [ProjectCostVariance],                MSP_EpmProject_UserView.ProjectCV as [ProjectCV],                MSP_EpmProject_UserView.ProjectCPI as [ProjectCPI],                MSP_EpmProject_UserView.ProjectEarlyFinish as [ProjectEarlyFinish],                MSP_EpmProject_UserView.ProjectEarlyStart as [ProjectEarlyStart],                MSP_EpmProject_UserView.ProjectLateFinish as [ProjectLateFinish],                MSP_EpmProject_UserView.ProjectLateStart as [ProjectLateStart],                MSP_EpmProject_UserView.ProjectSV as [ProjectSV],                MSP_EpmProject_UserView.ProjectWorkVariance as [ProjectWorkVariance],                MSP_EpmProject_UserView.ProjectBudgetCost as [ProjectBudgetCost],                MSP_EpmProject_UserView.ProjectBudgetWork as [ProjectBudgetWork],                MSP_EpmProject_UserView.ProjectResourcePlanWork as [ProjectResourcePlanWork],                MSP_EpmProject_UserView.ProjectRegularCost as [ProjectRegularCost],                MSP_EpmProject_UserView.ProjectRemainingCost as [ProjectRemainingCost],                MSP_EpmProject_UserView.ProjectRemainingOvertimeCost as [ProjectRemainingOvertimeCost],                MSP_EpmProject_UserView.ProjectActualRegularCost as [ProjectActualRegularCost],                MSP_EpmProject_UserView.ProjectRemainingRegularCost as [ProjectRemainingRegularCost],                MSP_EpmProject_UserView.ProjectRegularWork as [ProjectRegularWork],                MSP_EpmProject_UserView.ProjectRemainingWork as [ProjectRemainingWork],                MSP_EpmProject_UserView.ProjectRemainingOvertimeWork as [ProjectRemainingOvertimeWork],                MSP_EpmProject_UserView.ProjectActualRegularWork as [ProjectActualRegularWork],                MSP_EpmProject_UserView.ProjectRemainingRegularWork as [ProjectRemainingRegularWork],                MSP_EpmProject_UserView.ProjectRemainingDuration as [ProjectRemainingDuration],                MSP_EpmProject_UserView.ProjectCVP as [ProjectCVP],                MSP_EpmProject_UserView.ProjectSVP as [ProjectSVP],                MSP_EpmProject_UserView.ProjectOwnerName as [ProjectOwnerName],                MSP_EpmProject_UserView.ProjectCount as [ProjectCount],                MSP_EpmProject_UserView.ProjectBaseline0Cost as [ProjectBaseline0Cost],                MSP_EpmProject_UserView.ProjectBaseline0FixedCost as [ProjectBaseline0FixedCost],                MSP_EpmProject_UserView.ProjectBaseline0Work as [ProjectBaseline0Work],                MSP_EpmProject_UserView.ProjectBaseline0BudgetCost as [ProjectBaseline0BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline0BudgetWork as [ProjectBaseline0BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline0StartDate as [ProjectBaseline0StartDate],                MSP_EpmProject_UserView.ProjectBaseline0FinishDate as [ProjectBaseline0FinishDate],                MSP_EpmProject_UserView.ProjectBaseline0Duration as [ProjectBaseline0Duration],                MSP_EpmProject_UserView.ProjectBaseline1Cost as [ProjectBaseline1Cost],                MSP_EpmProject_UserView.ProjectBaseline1FixedCost as [ProjectBaseline1FixedCost],                MSP_EpmProject_UserView.ProjectBaseline1Work as [ProjectBaseline1Work],                MSP_EpmProject_UserView.ProjectBaseline1BudgetCost as [ProjectBaseline1BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline1BudgetWork as [ProjectBaseline1BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline1StartDate as [ProjectBaseline1StartDate],                MSP_EpmProject_UserView.ProjectBaseline1FinishDate as [ProjectBaseline1FinishDate],                MSP_EpmProject_UserView.ProjectBaseline1Duration as [ProjectBaseline1Duration],                MSP_EpmProject_UserView.ProjectBaseline2Cost as [ProjectBaseline2Cost],                MSP_EpmProject_UserView.ProjectBaseline2FixedCost as [ProjectBaseline2FixedCost],                MSP_EpmProject_UserView.ProjectBaseline2Work as [ProjectBaseline2Work],                MSP_EpmProject_UserView.ProjectBaseline2BudgetCost as [ProjectBaseline2BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline2BudgetWork as [ProjectBaseline2BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline2StartDate as [ProjectBaseline2StartDate],                MSP_EpmProject_UserView.ProjectBaseline2FinishDate as [ProjectBaseline2FinishDate],                MSP_EpmProject_UserView.ProjectBaseline2Duration as [ProjectBaseline2Duration],                MSP_EpmProject_UserView.ProjectBaseline3Cost as [ProjectBaseline3Cost],                MSP_EpmProject_UserView.ProjectBaseline3FixedCost as [ProjectBaseline3FixedCost],                MSP_EpmProject_UserView.ProjectBaseline3Work as [ProjectBaseline3Work],                MSP_EpmProject_UserView.ProjectBaseline3BudgetCost as [ProjectBaseline3BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline3BudgetWork as [ProjectBaseline3BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline3StartDate as [ProjectBaseline3StartDate],                MSP_EpmProject_UserView.ProjectBaseline3FinishDate as [ProjectBaseline3FinishDate],                MSP_EpmProject_UserView.ProjectBaseline3Duration as [ProjectBaseline3Duration],                MSP_EpmProject_UserView.ProjectBaseline4Cost as [ProjectBaseline4Cost],                MSP_EpmProject_UserView.ProjectBaseline4FixedCost as [ProjectBaseline4FixedCost],                MSP_EpmProject_UserView.ProjectBaseline4Work as [ProjectBaseline4Work],                MSP_EpmProject_UserView.ProjectBaseline4BudgetCost as [ProjectBaseline4BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline4BudgetWork as [ProjectBaseline4BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline4StartDate as [ProjectBaseline4StartDate],                MSP_EpmProject_UserView.ProjectBaseline4FinishDate as [ProjectBaseline4FinishDate],                MSP_EpmProject_UserView.ProjectBaseline4Duration as [ProjectBaseline4Duration],                MSP_EpmProject_UserView.ProjectBaseline5Cost as [ProjectBaseline5Cost],                MSP_EpmProject_UserView.ProjectBaseline5FixedCost as [ProjectBaseline5FixedCost],                MSP_EpmProject_UserView.ProjectBaseline5Work as [ProjectBaseline5Work],                MSP_EpmProject_UserView.ProjectBaseline5BudgetCost as [ProjectBaseline5BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline5BudgetWork as [ProjectBaseline5BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline5StartDate as [ProjectBaseline5StartDate],                MSP_EpmProject_UserView.ProjectBaseline5FinishDate as [ProjectBaseline5FinishDate],                MSP_EpmProject_UserView.ProjectBaseline5Duration as [ProjectBaseline5Duration],                MSP_EpmProject_UserView.ProjectBaseline6Cost as [ProjectBaseline6Cost],                MSP_EpmProject_UserView.ProjectBaseline6FixedCost as [ProjectBaseline6FixedCost],                MSP_EpmProject_UserView.ProjectBaseline6Work as [ProjectBaseline6Work],                MSP_EpmProject_UserView.ProjectBaseline6BudgetCost as [ProjectBaseline6BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline6BudgetWork as [ProjectBaseline6BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline6StartDate as [ProjectBaseline6StartDate],                MSP_EpmProject_UserView.ProjectBaseline6FinishDate as [ProjectBaseline6FinishDate],                MSP_EpmProject_UserView.ProjectBaseline6Duration as [ProjectBaseline6Duration],                MSP_EpmProject_UserView.ProjectBaseline7Cost as [ProjectBaseline7Cost],                MSP_EpmProject_UserView.ProjectBaseline7FixedCost as [ProjectBaseline7FixedCost],                MSP_EpmProject_UserView.ProjectBaseline7Work as [ProjectBaseline7Work],                MSP_EpmProject_UserView.ProjectBaseline7BudgetCost as [ProjectBaseline7BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline7BudgetWork as [ProjectBaseline7BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline7StartDate as [ProjectBaseline7StartDate],                MSP_EpmProject_UserView.ProjectBaseline7FinishDate as [ProjectBaseline7FinishDate],                MSP_EpmProject_UserView.ProjectBaseline7Duration as [ProjectBaseline7Duration],                MSP_EpmProject_UserView.ProjectBaseline8Cost as [ProjectBaseline8Cost],                MSP_EpmProject_UserView.ProjectBaseline8FixedCost as [ProjectBaseline8FixedCost],                MSP_EpmProject_UserView.ProjectBaseline8Work as [ProjectBaseline8Work],                MSP_EpmProject_UserView.ProjectBaseline8BudgetCost as [ProjectBaseline8BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline8BudgetWork as [ProjectBaseline8BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline8StartDate as [ProjectBaseline8StartDate],                MSP_EpmProject_UserView.ProjectBaseline8FinishDate as [ProjectBaseline8FinishDate],                MSP_EpmProject_UserView.ProjectBaseline8Duration as [ProjectBaseline8Duration],                MSP_EpmProject_UserView.ProjectBaseline9Cost as [ProjectBaseline9Cost],                MSP_EpmProject_UserView.ProjectBaseline9FixedCost as [ProjectBaseline9FixedCost],                MSP_EpmProject_UserView.ProjectBaseline9Work as [ProjectBaseline9Work],                MSP_EpmProject_UserView.ProjectBaseline9BudgetCost as [ProjectBaseline9BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline9BudgetWork as [ProjectBaseline9BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline9StartDate as [ProjectBaseline9StartDate],                MSP_EpmProject_UserView.ProjectBaseline9FinishDate as [ProjectBaseline9FinishDate],                MSP_EpmProject_UserView.ProjectBaseline9Duration as [ProjectBaseline9Duration],                MSP_EpmProject_UserView.ProjectBaseline10Cost as [ProjectBaseline10Cost],                MSP_EpmProject_UserView.ProjectBaseline10FixedCost as [ProjectBaseline10FixedCost],                MSP_EpmProject_UserView.ProjectBaseline10Work as [ProjectBaseline10Work],                MSP_EpmProject_UserView.ProjectBaseline10BudgetCost as [ProjectBaseline10BudgetCost],                MSP_EpmProject_UserView.ProjectBaseline10BudgetWork as [ProjectBaseline10BudgetWork],                MSP_EpmProject_UserView.ProjectBaseline10StartDate as [ProjectBaseline10StartDate],                MSP_EpmProject_UserView.ProjectBaseline10FinishDate as [ProjectBaseline10FinishDate],                MSP_EpmProject_UserView.ProjectBaseline10Duration as [ProjectBaseline10Duration],                MSP_EpmTask_UserView.TaskUID as [TaskUID],                MSP_EpmTask_UserView.TaskParentUID as [TaskParentUID],                MSP_EpmTask_UserView.FixedCostAssignmentUID as [FixedCostAssignmentUID],                MSP_EpmTask_UserView.TaskName as [TaskName],                MSP_EpmTask_UserView.TaskIndex as [TaskIndex],                MSP_EpmTask_UserView.TaskIsOverallocated as [TaskIsOverallocated],                MSP_EpmTask_UserView.TaskIsProjectSummary as [TaskIsProjectSummary],                MSP_EpmTask_UserView.TaskOutlineLevel as [TaskOutlineLevel],                MSP_EpmTask_UserView.TaskOutlineNumber as [TaskOutlineNumber],                MSP_EpmTask_UserView.TaskIsMilestone  as [TaskIsMilestone],                MSP_EpmTask_UserView.TaskIsCritical as [TaskIsCritical],                MSP_EpmTask_UserView.TaskIsSummary as [TaskIsSummary],                MSP_EpmTask_UserView.TaskFixedCost as [TaskFixedCost],                MSP_EpmTask_UserView.TaskActualFixedCost as [TaskActualFixedCost],                MSP_EpmTask_UserView.TaskCost as [TaskCost],                MSP_EpmTask_UserView.TaskOvertimeCost as [TaskOvertimeCost],                MSP_EpmTask_UserView.TaskActualCost as [TaskActualCost],                MSP_EpmTask_UserView.TaskActualOvertimeCost as [TaskActualOvertimeCost],                MSP_EpmTask_UserView.TaskWork as [TaskWork],                MSP_EpmTask_UserView.TaskOvertimeWork as [TaskOvertimeWork],                MSP_EpmTask_UserView.TaskActualWork as [TaskActualWork],                MSP_EpmTask_UserView.TaskActualOvertimeWork as [TaskActualOvertimeWork],                MSP_EpmTask_UserView.TaskDurationVariance as [TaskDurationVariance],                MSP_EpmTask_UserView.TaskStartVariance as [TaskStartVariance],                MSP_EpmTask_UserView.TaskFinishVariance as [TaskFinishVariance],                MSP_EpmTask_UserView.TaskTotalSlack as [TaskTotalSlack],                MSP_EpmTask_UserView.TaskFreeSlack as [TaskFreeSlack],                MSP_EpmTask_UserView.TaskDuration as [TaskDuration],                MSP_EpmTask_UserView.TaskDurationString as [TaskDurationString],                MSP_EpmTask_UserView.TaskActualDuration as [TaskActualDuration],                MSP_EpmTask_UserView.TaskStartDate as [TaskStartDate],                MSP_EpmTask_UserView.TaskStartDateString as [TaskStartDateString],                MSP_EpmTask_UserView.TaskFinishDate as [TaskFinishDate],                MSP_EpmTask_UserView.TaskFinishDateString as [TaskFinishDateString],                MSP_EpmTask_UserView.TaskDeliverableStartDate as [TaskDeliverableStartDate],                MSP_EpmTask_UserView.TaskDeliverableFinishDate as [TaskDeliverableFinishDate],                MSP_EpmTask_UserView.TaskActualStartDate as [TaskActualStartDate],                MSP_EpmTask_UserView.TaskActualFinishDate as [TaskActualFinishDate],                MSP_EpmTask_UserView.TaskPercentCompleted as [TaskPercentCompleted],                MSP_EpmTask_UserView.TaskPercentWorkCompleted as [TaskPercentWorkCompleted],                MSP_EpmTask_UserView.TaskPhysicalPercentCompleted as [TaskPhysicalPercentCompleted],                MSP_EpmTask_UserView.TaskACWP as [TaskACWP],                MSP_EpmTask_UserView.TaskBCWP as [TaskBCWP],                MSP_EpmTask_UserView.TaskBCWS as [TaskBCWS],                MSP_EpmTask_UserView.TaskLevelingDelay as [TaskLevelingDelay],                MSP_EpmTask_UserView.TaskPriority as [TaskPriority],                MSP_EpmTask_UserView.TaskSPI as [TaskSPI],                MSP_EpmTask_UserView.TaskTCPI as [TaskTCPI],                MSP_EpmTask_UserView.TaskVAC as [TaskVAC],                MSP_EpmTask_UserView.TaskDeadline as [TaskDeadline],                MSP_EpmTask_UserView.TaskDurationIsEstimated as [TaskDurationIsEstimated],                MSP_EpmTask_UserView.TaskEAC as [TaskEAC],                MSP_EpmTask_UserView.TaskIsEffortDriven as [TaskIsEffortDriven],                MSP_EpmTask_UserView.TaskIsExternal as [TaskIsExternal],                MSP_EpmTask_UserView.TaskIsRecurring as [TaskIsRecurring],                MSP_EpmTask_UserView.TaskCostVariance as [TaskCostVariance],                MSP_EpmTask_UserView.TaskCV as [TaskCV],                MSP_EpmTask_UserView.TaskCPI as [TaskCPI],                MSP_EpmTask_UserView.TaskEarlyFinish as [TaskEarlyFinish],                MSP_EpmTask_UserView.TaskEarlyStart as [TaskEarlyStart],                MSP_EpmTask_UserView.TaskLateFinish as [TaskLateFinish],                MSP_EpmTask_UserView.TaskLateStart as [TaskLateStart],                MSP_EpmTask_UserView.TaskSV as [TaskSV],                MSP_EpmTask_UserView.TaskWorkVariance as [TaskWorkVariance],                MSP_EpmTask_UserView.TaskIgnoresResourceCalendar as [TaskIgnoresResourceCalendar],                MSP_EpmTask_UserView.TaskClientUniqueId as [TaskClientUniqueId],                MSP_EpmTask_UserView.TaskIsMarked as [TaskIsMarked],                MSP_EpmTask_UserView.TaskIsActive as [TaskIsActive],                MSP_EpmTask_UserView.TaskIsManuallyScheduled as [TaskIsManuallyScheduled],                MSP_EpmTask_UserView.TaskWBS as [TaskWBS],                MSP_EpmTask_UserView.TaskCreatedRevisionCounter as [TaskCreatedRevisionCounter],                MSP_EpmTask_UserView.TaskModifiedRevisionCounter as [TaskModifiedRevisionCounter],                MSP_EpmTask_UserView.TaskCreatedDate as [TaskCreatedDate],                MSP_EpmTask_UserView.TaskModifiedDate as [TaskModifiedDate],                MSP_EpmTask_UserView.TaskBudgetCost as [TaskBudgetCost],                MSP_EpmTask_UserView.TaskBudgetWork as [TaskBudgetWork],                MSP_EpmTask_UserView.TaskResourcePlanWork as [TaskResourcePlanWork],                MSP_EpmTask_UserView.TaskHyperLinkFriendlyName as [TaskHyperLinkFriendlyName],                MSP_EpmTask_UserView.TaskHyperLinkAddress as [TaskHyperLinkAddress],                MSP_EpmTask_UserView.TaskHyperLinkSubAddress  as [TaskHyperLinkSubAddress],                MSP_EpmTask_UserView.TaskRegularCost as [TaskRegularCost],                MSP_EpmTask_UserView.TaskRemainingCost as [TaskRemainingCost],                MSP_EpmTask_UserView.TaskRemainingOvertimeCost as [TaskRemainingOvertimeCost],                MSP_EpmTask_UserView.TaskActualRegularCost as [TaskActualRegularCost],                MSP_EpmTask_UserView.TaskRemainingRegularCost as [TaskRemainingRegularCost],                MSP_EpmTask_UserView.TaskRegularWork as [TaskRegularWork],                MSP_EpmTask_UserView.TaskRemainingWork as [TaskRemainingWork],                MSP_EpmTask_UserView.TaskRemainingOvertimeWork as [TaskRemainingOvertimeWork],                MSP_EpmTask_UserView.TaskActualRegularWork as [TaskActualRegularWork],                MSP_EpmTask_UserView.TaskRemainingRegularWork as [TaskRemainingRegularWork],                MSP_EpmTask_UserView.TaskRemainingDuration as [TaskRemainingDuration],                MSP_EpmTask_UserView.TaskCVP as [TaskCVP],                MSP_EpmTask_UserView.TaskSVP as [TaskSVP],                MSP_EpmTask_UserView.TaskCount as [TaskCount],                MSP_EpmTask_UserView.TaskBaseline0Cost as [TaskBaseline0Cost],                MSP_EpmTask_UserView.TaskBaseline0FixedCost as [TaskBaseline0FixedCost],                MSP_EpmTask_UserView.TaskBaseline0Work as [TaskBaseline0Work],                MSP_EpmTask_UserView.TaskBaseline0BudgetCost as [TaskBaseline0BudgetCost],                MSP_EpmTask_UserView.TaskBaseline0BudgetWork as [TaskBaseline0BudgetWork],                MSP_EpmTask_UserView.TaskBaseline0StartDate as [TaskBaseline0StartDate],                MSP_EpmTask_UserView.TaskBaseline0StartDateString as [TaskBaseline0StartDateString],                MSP_EpmTask_UserView.TaskBaseline0FinishDate as [TaskBaseline0FinishDate],                MSP_EpmTask_UserView.TaskBaseline0FinishDateString as [TaskBaseline0FinishDateString],                MSP_EpmTask_UserView.TaskBaseline0DeliverableStartDate as [TaskBaseline0DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline0DeliverableFinishDate as [TaskBaseline0DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline0Duration as [TaskBaseline0Duration],                MSP_EpmTask_UserView.TaskBaseline0DurationString as [TaskBaseline0DurationString],                MSP_EpmTask_UserView.TaskBaseline1Cost as [TaskBaseline1Cost],                MSP_EpmTask_UserView.TaskBaseline1FixedCost as [TaskBaseline1FixedCost],                MSP_EpmTask_UserView.TaskBaseline1Work as [TaskBaseline1Work],                MSP_EpmTask_UserView.TaskBaseline1BudgetCost as [TaskBaseline1BudgetCost],                MSP_EpmTask_UserView.TaskBaseline1BudgetWork as [TaskBaseline1BudgetWork],                MSP_EpmTask_UserView.TaskBaseline1StartDate as [TaskBaseline1StartDate],                MSP_EpmTask_UserView.TaskBaseline1StartDateString as [TaskBaseline1StartDateString],                MSP_EpmTask_UserView.TaskBaseline1FinishDate as [TaskBaseline1FinishDate],                MSP_EpmTask_UserView.TaskBaseline1FinishDateString as [TaskBaseline1FinishDateString],                MSP_EpmTask_UserView.TaskBaseline1DeliverableStartDate as [TaskBaseline1DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline1DeliverableFinishDate as [TaskBaseline1DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline1Duration as [TaskBaseline1Duration],                MSP_EpmTask_UserView.TaskBaseline1DurationString as [TaskBaseline1DurationString],                MSP_EpmTask_UserView.TaskBaseline2Cost as [TaskBaseline2Cost],                MSP_EpmTask_UserView.TaskBaseline2FixedCost as [TaskBaseline2FixedCost],                MSP_EpmTask_UserView.TaskBaseline2Work as [TaskBaseline2Work],                MSP_EpmTask_UserView.TaskBaseline2BudgetCost as [TaskBaseline2BudgetCost],                MSP_EpmTask_UserView.TaskBaseline2BudgetWork as [TaskBaseline2BudgetWork],                MSP_EpmTask_UserView.TaskBaseline2StartDate as [TaskBaseline2StartDate],                MSP_EpmTask_UserView.TaskBaseline2StartDateString as [TaskBaseline2StartDateString],                MSP_EpmTask_UserView.TaskBaseline2FinishDate as [TaskBaseline2FinishDate],                MSP_EpmTask_UserView.TaskBaseline2FinishDateString as [TaskBaseline2FinishDateString],                MSP_EpmTask_UserView.TaskBaseline2DeliverableStartDate as [TaskBaseline2DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline2DeliverableFinishDate as [TaskBaseline2DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline2Duration as [TaskBaseline2Duration],                MSP_EpmTask_UserView.TaskBaseline2DurationString as [TaskBaseline2DurationString],                MSP_EpmTask_UserView.TaskBaseline3Cost as [TaskBaseline3Cost],                MSP_EpmTask_UserView.TaskBaseline3FixedCost as [TaskBaseline3FixedCost],                MSP_EpmTask_UserView.TaskBaseline3Work as [TaskBaseline3Work],                MSP_EpmTask_UserView.TaskBaseline3BudgetCost as [TaskBaseline3BudgetCost],                MSP_EpmTask_UserView.TaskBaseline3BudgetWork as [TaskBaseline3BudgetWork],                MSP_EpmTask_UserView.TaskBaseline3StartDate as [TaskBaseline3StartDate],                MSP_EpmTask_UserView.TaskBaseline3StartDateString as [TaskBaseline3StartDateString],                MSP_EpmTask_UserView.TaskBaseline3FinishDate as [TaskBaseline3FinishDate],                MSP_EpmTask_UserView.TaskBaseline3FinishDateString as [TaskBaseline3FinishDateString],                MSP_EpmTask_UserView.TaskBaseline3DeliverableStartDate as [TaskBaseline3DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline3DeliverableFinishDate as [TaskBaseline3DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline3Duration as [TaskBaseline3Duration],                MSP_EpmTask_UserView.TaskBaseline3DurationString as [TaskBaseline3DurationString],                MSP_EpmTask_UserView.TaskBaseline4Cost as [TaskBaseline4Cost],                MSP_EpmTask_UserView.TaskBaseline4FixedCost as [TaskBaseline4FixedCost],                MSP_EpmTask_UserView.TaskBaseline4Work as [TaskBaseline4Work],                MSP_EpmTask_UserView.TaskBaseline4BudgetCost as [TaskBaseline4BudgetCost],                MSP_EpmTask_UserView.TaskBaseline4BudgetWork as [TaskBaseline4BudgetWork],                MSP_EpmTask_UserView.TaskBaseline4StartDate as [TaskBaseline4StartDate],                MSP_EpmTask_UserView.TaskBaseline4StartDateString as [TaskBaseline4StartDateString],                MSP_EpmTask_UserView.TaskBaseline4FinishDate as [TaskBaseline4FinishDate],                MSP_EpmTask_UserView.TaskBaseline4FinishDateString as [TaskBaseline4FinishDateString],                MSP_EpmTask_UserView.TaskBaseline4DeliverableStartDate as [TaskBaseline4DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline4DeliverableFinishDate as [TaskBaseline4DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline4Duration as [TaskBaseline4Duration],                MSP_EpmTask_UserView.TaskBaseline4DurationString as [TaskBaseline4DurationString],                MSP_EpmTask_UserView.TaskBaseline5Cost as [TaskBaseline5Cost],                MSP_EpmTask_UserView.TaskBaseline5FixedCost as [TaskBaseline5FixedCost],                MSP_EpmTask_UserView.TaskBaseline5Work as [TaskBaseline5Work],                MSP_EpmTask_UserView.TaskBaseline5BudgetCost as [TaskBaseline5BudgetCost],                MSP_EpmTask_UserView.TaskBaseline5BudgetWork as [TaskBaseline5BudgetWork],                MSP_EpmTask_UserView.TaskBaseline5StartDate as [TaskBaseline5StartDate],                MSP_EpmTask_UserView.TaskBaseline5StartDateString as [TaskBaseline5StartDateString],                MSP_EpmTask_UserView.TaskBaseline5FinishDate as [TaskBaseline5FinishDate],                MSP_EpmTask_UserView.TaskBaseline5FinishDateString as [TaskBaseline5FinishDateString],                MSP_EpmTask_UserView.TaskBaseline5DeliverableStartDate as [TaskBaseline5DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline5DeliverableFinishDate as [TaskBaseline5DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline5Duration as [TaskBaseline5Duration],                MSP_EpmTask_UserView.TaskBaseline5DurationString as [TaskBaseline5DurationString],                MSP_EpmTask_UserView.TaskBaseline6Cost as [TaskBaseline6Cost],                MSP_EpmTask_UserView.TaskBaseline6FixedCost as [TaskBaseline6FixedCost],                MSP_EpmTask_UserView.TaskBaseline6Work as [TaskBaseline6Work],                MSP_EpmTask_UserView.TaskBaseline6BudgetCost as [TaskBaseline6BudgetCost],                MSP_EpmTask_UserView.TaskBaseline6BudgetWork as [TaskBaseline6BudgetWork],                MSP_EpmTask_UserView.TaskBaseline6StartDate as [TaskBaseline6StartDate],                MSP_EpmTask_UserView.TaskBaseline6StartDateString as [TaskBaseline6StartDateString],                MSP_EpmTask_UserView.TaskBaseline6FinishDate as [TaskBaseline6FinishDate],                MSP_EpmTask_UserView.TaskBaseline6FinishDateString as [TaskBaseline6FinishDateString],                MSP_EpmTask_UserView.TaskBaseline6DeliverableStartDate as [TaskBaseline6DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline6DeliverableFinishDate as [TaskBaseline6DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline6Duration as [TaskBaseline6Duration],                MSP_EpmTask_UserView.TaskBaseline6DurationString as [TaskBaseline6DurationString],                MSP_EpmTask_UserView.TaskBaseline7Cost as [TaskBaseline7Cost],                MSP_EpmTask_UserView.TaskBaseline7FixedCost as [TaskBaseline7FixedCost],                MSP_EpmTask_UserView.TaskBaseline7Work as [TaskBaseline7Work],                MSP_EpmTask_UserView.TaskBaseline7BudgetCost as [TaskBaseline7BudgetCost],                MSP_EpmTask_UserView.TaskBaseline7BudgetWork as [TaskBaseline7BudgetWork],                MSP_EpmTask_UserView.TaskBaseline7StartDate as [TaskBaseline7StartDate],                MSP_EpmTask_UserView.TaskBaseline7StartDateString as [TaskBaseline7StartDateString],                MSP_EpmTask_UserView.TaskBaseline7FinishDate as [TaskBaseline7FinishDate],                MSP_EpmTask_UserView.TaskBaseline7FinishDateString as [TaskBaseline7FinishDateString],                MSP_EpmTask_UserView.TaskBaseline7DeliverableStartDate as [TaskBaseline7DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline7DeliverableFinishDate as [TaskBaseline7DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline7Duration as [TaskBaseline7Duration],                MSP_EpmTask_UserView.TaskBaseline7DurationString as [TaskBaseline7DurationString],                MSP_EpmTask_UserView.TaskBaseline8Cost as [TaskBaseline8Cost],                MSP_EpmTask_UserView.TaskBaseline8FixedCost as [TaskBaseline8FixedCost],                MSP_EpmTask_UserView.TaskBaseline8Work as [TaskBaseline8Work],                MSP_EpmTask_UserView.TaskBaseline8BudgetCost as [TaskBaseline8BudgetCost],                MSP_EpmTask_UserView.TaskBaseline8BudgetWork as [TaskBaseline8BudgetWork],                MSP_EpmTask_UserView.TaskBaseline8StartDate as [TaskBaseline8StartDate],                MSP_EpmTask_UserView.TaskBaseline8StartDateString as [TaskBaseline8StartDateString],                MSP_EpmTask_UserView.TaskBaseline8FinishDate as [TaskBaseline8FinishDate],                MSP_EpmTask_UserView.TaskBaseline8FinishDateString as [TaskBaseline8FinishDateString],                MSP_EpmTask_UserView.TaskBaseline8DeliverableStartDate as [TaskBaseline8DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline8DeliverableFinishDate as [TaskBaseline8DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline8Duration as [TaskBaseline8Duration],                MSP_EpmTask_UserView.TaskBaseline8DurationString as [TaskBaseline8DurationString],                MSP_EpmTask_UserView.TaskBaseline9Cost as [TaskBaseline9Cost],                MSP_EpmTask_UserView.TaskBaseline9FixedCost as [TaskBaseline9FixedCost],                MSP_EpmTask_UserView.TaskBaseline9Work as [TaskBaseline9Work],                MSP_EpmTask_UserView.TaskBaseline9BudgetCost as [TaskBaseline9BudgetCost],                MSP_EpmTask_UserView.TaskBaseline9BudgetWork as [TaskBaseline9BudgetWork],                MSP_EpmTask_UserView.TaskBaseline9StartDate as [TaskBaseline9StartDate],                MSP_EpmTask_UserView.TaskBaseline9StartDateString as [TaskBaseline9StartDateString],                MSP_EpmTask_UserView.TaskBaseline9FinishDate as [TaskBaseline9FinishDate],                MSP_EpmTask_UserView.TaskBaseline9FinishDateString as [TaskBaseline9FinishDateString],                MSP_EpmTask_UserView.TaskBaseline9DeliverableStartDate as [TaskBaseline9DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline9DeliverableFinishDate as [TaskBaseline9DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline9Duration as [TaskBaseline9Duration],                MSP_EpmTask_UserView.TaskBaseline9DurationString as [TaskBaseline9DurationString],                MSP_EpmTask_UserView.TaskBaseline10Cost as [TaskBaseline10Cost],                MSP_EpmTask_UserView.TaskBaseline10FixedCost as [TaskBaseline10FixedCost],                MSP_EpmTask_UserView.TaskBaseline10Work as [TaskBaseline10Work],                MSP_EpmTask_UserView.TaskBaseline10BudgetCost as [TaskBaseline10BudgetCost],                MSP_EpmTask_UserView.TaskBaseline10BudgetWork as [TaskBaseline10BudgetWork],                MSP_EpmTask_UserView.TaskBaseline10StartDate as [TaskBaseline10StartDate],                MSP_EpmTask_UserView.TaskBaseline10StartDateString as [TaskBaseline10StartDateString],                MSP_EpmTask_UserView.TaskBaseline10FinishDate as [TaskBaseline10FinishDate],                MSP_EpmTask_UserView.TaskBaseline10FinishDateString as [TaskBaseline10FinishDateString],                MSP_EpmTask_UserView.TaskBaseline10DeliverableStartDate as [TaskBaseline10DeliverableStartDate],                MSP_EpmTask_UserView.TaskBaseline10DeliverableFinishDate as [TaskBaseline10DeliverableFinishDate],                MSP_EpmTask_UserView.TaskBaseline10Duration as [TaskBaseline10Duration],                MSP_EpmTask_UserView.TaskBaseline10DurationString as [TaskBaseline10DurationString],                MSP_EpmTask_UserView.[Health] as [Health],                MSP_EpmResource_UserView.ResourceName as [ResourceName],                MSP_EpmResource_UserView.ResourceType as [ResourceType],                MSP_EpmResource_UserView.ResourceIsTeam as [ResourceIsTeam],                MSP_EpmResource_UserView.[Resource Departments] as [Resource Departments],                  MSP_EpmResource_UserView.[RBS] as [RBS],                  MSP_EpmResource_UserView.ResourceCostCenter as [ResourceCostCenter],                MSP_EpmAssignment_UserView.AssignmentCost as [AssignmentCost],                MSP_EpmAssignment_UserView.AssignmentWork as [AssignmentWork],                MSP_EpmAssignment_UserView.AssignmentActualWork as [AssignmentActualWork],                MSP_EpmAssignment_UserView.AssignmentPercentWorkCompleted as [AssignmentPercentWorkCompleted],                MSP_EpmAssignment_UserView.AssignmentStartDate as [AssignmentStartDate],                MSP_EpmAssignment_UserView.AssignmentFinishDate as [AssignmentFinishDate],                MSP_EpmAssignment_UserView.AssignmentActualStartDate as [AssignmentActualStartDate],                MSP_EpmAssignment_UserView.AssignmentActualFinishDate as [AssignmentActualFinishDate],                MSP_EpmAssignment_UserView.AssignmentType as [AssignmentType],                MSP_EpmAssignment_UserView.AssignmentResourceType as [AssignmentResourceType],                MSP_EpmAssignment_UserView.AssignmentIsOverallocated as [AssignmentIsOverallocated],                MSP_EpmAssignment_UserView.AssignmentModifiedDate as [AssignmentModifiedDate],                MSP_EpmAssignment_UserView.AssignmentResourcePlanWork as [AssignmentResourcePlanWork],                MSP_EpmAssignment_UserView.AssignmentRemainingCost as [AssignmentRemainingCost],                MSP_EpmAssignment_UserView.AssignmentRemainingWork as [AssignmentRemainingWork]             FROM                dbo.MSP_EpmProject_UserView             INNER JOIN dbo.MSP_EpmTask_UserView ON                MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID             LEFT OUTER JOIN                dbo.MSP_EpmAssignment_UserView ON                   MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID                   AND MSP_EpmTask_UserView.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID             LEFT OUTER JOIN                dbo.MSP_EpmResource_UserView ON                   MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID             ORDER BY                MSP_EpmProject_UserView.ProjectName,                MSP_EpmTask_UserView.TaskIndex,                MSP_EpmTask_UserView.TaskName          

    Wednesday, April 22, 2015 4:19 PM
  • That's Ok we can live without that.

    Thanks for your help!

    Wednesday, April 22, 2015 4:54 PM
  • Ah! did not see your last comment. Anyway, if you need it, it is here http://1drv.ms/1bjfyIM


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, April 22, 2015 5:00 PM
    Moderator
  • Thanks you!

    I am at BI Template. Want to add fields at those templates how can I do that/ Can I do that, there is no SQL query and when I open those templates it gives me multiple fields options, I am asking as most of our reports are based on these.

    See screen shots

    Wednesday, April 22, 2015 6:06 PM
  • Go to Data >> Conenctions >> properties

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, April 22, 2015 6:09 PM
    Moderator
  • Wednesday, April 22, 2015 6:16 PM
  • No SQL query Option
    Wednesday, April 22, 2015 6:16 PM
  • You cannot change the template file that is based on a CUBE. The easiest option is to navigate to Bi Center >> Templates and use on the excel templates there (like Projects, Issues, Risks etc)

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, April 22, 2015 6:17 PM
    Moderator
  • but what if, If i need Resource and project information in one report as they are defined seprate here.

    Wednesday, April 22, 2015 6:19 PM
  • The answer to your question is to write your own SQL query. You can write a SQL query to query the data anywhich you want.

    Also, for the SQL query option to appear , You have to pick a template that is Query based. You cannot modify an OLAP based template. So, please use the template that is available directly under BI Center >> Templates, and not an OLAP cube based template.

    You could also start from Excel, connect to the SQL Server, and add your query and then publish the report to BI Center. here is an example: http://www.prasannaadavi.com/2011/08/create-report-of-users-and-security.html

    I think this document will be helpful to understand as to how BI was set up in 2010: http://download.microsoft.com/download/7/7/6/7769F459-8D80-4338-A4E0-E06ABC83C1FE/Microsoft%20Project%20Server%202010%20Reporting%20with%20Excel%20Services.pdf


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Wednesday, April 22, 2015 7:22 PM
    Wednesday, April 22, 2015 6:43 PM
    Moderator
  • Hi Prasanna,

    I want to see a field called % complete on task or task status from project pro in Business Intelligence report in PWA like (Status Manger) Can this be Done?

    Thursday, April 23, 2015 7:42 PM
  • Yes, you are looking for the field MSP_EpmTask_UserView.TaskPercentCompleted.

    It is already there in the query I provided above (or the default one)


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Thursday, April 23, 2015 7:46 PM
    Moderator
  • Hi Prasanna,

     I want to add 

    1)Timesheet Period

    2)Status Manager 

    3)Task Percent complete to below query

    SELECT                MSP_TimesheetLine_UserView.PeriodUID as [PeriodUID],                MSP_TimesheetLine_UserView.PeriodName as [PeriodName],                MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],                MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],                MSP_TimesheetLine_UserView.TimesheetUID as [TimesheetUID],                MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],                MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],                MSP_TimesheetLine_UserView.ResourceName as [ResourceName],                MSP_TimesheetLine_UserView.ResourceUID as [ResourceUID],                MSP_TimesheetLine_UserView.ProjectName as [ProjectName],                MSP_TimesheetLine_UserView.ProjectUID as [ProjectUID],                MSP_TimesheetLine_UserView.TaskName as [TaskName],                MSP_TimesheetLine_UserView.TaskUID as [TaskUID],                MSP_TimesheetLine_UserView.TimesheetLineUID as [TimesheetLineUID],                MSP_TimesheetLine_UserView.PlannedWork as [Planned Work],                MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],                MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],                MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [Billable Actual Overtime Work],                MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable Actual Overtime Work],                MSP_TimesheetLine_UserView.ActualWorkBillable + MSP_TimesheetLine_UserView.ActualWorkNonBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Timesheet Line Actual Work],                MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],                MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],                MSP_TimesheetLine_UserView.[RBS] as [RBS]              FROM                MSP_TimesheetLine_UserView  

    Friday, April 24, 2015 6:38 PM
  • try this: http://1drv.ms/1KdEhum

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, April 24, 2015 8:22 PM
    Friday, April 24, 2015 7:31 PM
    Moderator
  • hi Again

    I want to add

    1) Res Id

    2) Resource Location

    2) Project ID

    to below query, I have added those fields to simple reports where there is no join required but Unfortunately I don't have access to Database so I can see the unique Id to join and test.

    SELECT                
    MSP_TimesheetLine_UserView.PeriodUID as [PeriodUID],                
    MSP_TimesheetLine_UserView.PeriodName as [PeriodName],                
    MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],                
    MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],                
    MSP_TimesheetLine_UserView.TimesheetUID as [TimesheetUID],
    MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],                
    MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],                
    MSP_TimesheetLine_UserView.ResourceName as [ResourceName],                
    MSP_TimesheetLine_UserView.ResourceUID as [ResourceUID],                
    MSP_TimesheetLine_UserView.ProjectName as [ProjectName],                
    MSP_TimesheetLine_UserView.ProjectUID as [ProjectUID],                
    MSP_TimesheetLine_UserView.TaskName as [TaskName],                
    MSP_TimesheetLine_UserView.TaskUID as [TaskUID],                
    MSP_TimesheetLine_UserView.TimesheetLineUID as [TimesheetLineUID],                
    MSP_TimesheetLine_UserView.PlannedWork as [Planned Work], 
    MSP_TimesheetLine_UserView.TimesheetLineStatus as [Timesheet Line Status],                
    MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],                
    MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],                
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [Billable Actual Overtime Work],                
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable Actual Overtime Work],                
    MSP_TimesheetLine_UserView.ActualWorkBillable + MSP_TimesheetLine_UserView.ActualWorkNonBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Timesheet Line Actual Work],                
    MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],                
    MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],                
    MSP_TimesheetLine_UserView.[RBS] as [RBS],
    (Select b.ResourceName from MSP_EpmResource b where c.[ResourceTimesheetManagerUID]=b.resourceuid) AS TimesheetManager,
    (Select b.ResourceName from MSP_EpmResource b where d.[TaskStatusManagerUID]=b.resourceuid) AS StatusManager,
    d.TaskPercentworkCompleted,
    MSP_TimesheetLine_UserView.PeriodName           
    FROM                
    MSP_TimesheetLine_UserView
    INNER JOIN
    MSP_EPMResource_userview c
    on MSP_TimesheetLine_UserView.ResourceUID= c.ResourceUID
    Inner JOIN MSP_EPMTask_Userview d on d.taskuid=MSP_TimesheetLine_UserView.Taskuid

    Tuesday, April 28, 2015 8:37 PM
  • hi Again

    I want to add

    1) Res Id

    2) Resource Location

    2) Project ID

    to below query, I have added those fields to simple reports where there is no join required but Unfortunately I don't have access to Database so I can see the unique Id to join and test.

    SELECT                
    MSP_TimesheetLine_UserView.PeriodUID as [PeriodUID],                
    MSP_TimesheetLine_UserView.PeriodName as [PeriodName],                
    MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],                
    MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],                
    MSP_TimesheetLine_UserView.TimesheetUID as [TimesheetUID],
    MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],                
    MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],                
    MSP_TimesheetLine_UserView.ResourceName as [ResourceName],                
    MSP_TimesheetLine_UserView.ResourceUID as [ResourceUID],                
    MSP_TimesheetLine_UserView.ProjectName as [ProjectName],                
    MSP_TimesheetLine_UserView.ProjectUID as [ProjectUID],                
    MSP_TimesheetLine_UserView.TaskName as [TaskName],                
    MSP_TimesheetLine_UserView.TaskUID as [TaskUID],                
    MSP_TimesheetLine_UserView.TimesheetLineUID as [TimesheetLineUID],                
    MSP_TimesheetLine_UserView.PlannedWork as [Planned Work], 
    MSP_TimesheetLine_UserView.TimesheetLineStatus as [Timesheet Line Status],                
    MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],                
    MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],                
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [Billable Actual Overtime Work],                
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable Actual Overtime Work],                
    MSP_TimesheetLine_UserView.ActualWorkBillable + MSP_TimesheetLine_UserView.ActualWorkNonBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Timesheet Line Actual Work],                
    MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],                
    MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],                
    MSP_TimesheetLine_UserView.[RBS] as [RBS],
    (Select b.ResourceName from MSP_EpmResource b where c.[ResourceTimesheetManagerUID]=b.resourceuid) AS TimesheetManager,
    (Select b.ResourceName from MSP_EpmResource b where d.[TaskStatusManagerUID]=b.resourceuid) AS StatusManager,
    d.TaskPercentworkCompleted,
    MSP_TimesheetLine_UserView.PeriodName           
    FROM                
    MSP_TimesheetLine_UserView
    INNER JOIN
    MSP_EPMResource_userview c
    on MSP_TimesheetLine_UserView.ResourceUID= c.ResourceUID
    Inner JOIN MSP_EPMTask_Userview d on d.taskuid=MSP_TimesheetLine_UserView.Taskuid


    Wednesday, April 29, 2015 5:53 PM
  • Hi Prasanna,

    When I add this query to my report it only includes Timesheet  line Class= Standard not all,

    This is happening when we are joining MSP_EPMTask_Userview   and MSP_TimesheetLine_UserView.Taskuid

    Thursday, April 30, 2015 5:50 PM
  • try this: http://1drv.ms/1KdEhum

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Thursday, April 30, 2015 8:05 PM
    Moderator
  • Thank you for your reply

    I have used the above query, it still shows standard hours only and doesn't show up Res id and Project Id

    Friday, May 1, 2015 3:39 PM
  • Ah! for some reason my changes did not save.

    try this: http://1drv.ms/1E1Yyhg


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, May 1, 2015 6:12 PM
    Friday, May 1, 2015 4:04 PM
    Moderator
  • Thank you 

    I will try that, I was reading your blog the other day

    http://www.prasannaadavi.com/2014/03/create-department-vacation-calendar.html#more

    Is there a place we can ask questions?

    Friday, May 1, 2015 4:11 PM
  • I have run above query it Gave me all other timesheet Line class but not Res Id and project ID.Instead showing something else .Please see screenshot.

    Friday, May 1, 2015 4:20 PM
  • I am working on adding a "Ask me a question" feature to my blog, but for now, you can post a comment on the blog itself.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Friday, May 1, 2015 4:24 PM
    Moderator
  • What are Project ID and Resource ID? are they custom fields that you have created? if yes, then you will need to know the exact names of the fields, and tie in couple of other tables into the query.

    What you are seeing with my query are the Resource UID and project UID that are system generated.


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Friday, May 1, 2015 4:33 PM
    Moderator
  • OK.. I have update the query again. I have added two fields Project Departments, and Resource Departments. Replace that with the exact name of ResID and ProjectID custom fields in your environment. Keep the square brackets intact.

    http://1drv.ms/1E1Yyhg


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Friday, May 1, 2015 4:41 PM
    Moderator
  • Yes, they are Custom fields.

    If I use below Query it shows me Res Id and Resource Location fields. Same with Project ID

    SELECT *FROM  dbo.MSP_EpmResource_UserView

    but When I add above fields to below Query, it gives me an error saying Invalid coloumn.

                 SELECT                MSP_EpmResource_UserView.ResourceName as [ResourceName],                MSP_EpmResource_UserView.ResourceStandardRate as [ResourceStandardRate],                MSP_EpmResource_UserView.ResourceOvertimeRate as [ResourceOvertimeRate],                MSP_EpmResource_UserView.ResourceStatusUID as [ResourceStatusUID],                MSP_EpmResource_UserView.ResourceCode as [ResourceCode],                MSP_EpmResource_UserView.ResourceCostPerUse as [ResourceCostPerUse],                MSP_EpmResource_UserView.ResourceEmailAddress as [ResourceEmailAddress],                MSP_EpmResource_UserView.ResourceInitials as [ResourceInitials],                MSP_EpmResource_UserView.ResourceMaterialLabel as [ResourceMaterialLabel],                MSP_EpmResource_UserView.ResourceType as [ResourceType],                MSP_EpmResource_UserView.ResourceGroup as [ResourceGroup],                MSP_EpmResource_UserView.ResourceMaxUnits as [ResourceMaxUnits],                MSP_EpmResource_UserView.ResourceBookingType as [ResourceBookingType],                MSP_EpmResource_UserView.ResourceTimesheetManagerUID as [ResourceTimesheetManagerUID],                MSP_EpmResource_UserView.ResourceEarliestAvailableFrom as [ResourceEarliestAvailableFrom],                MSP_EpmResource_UserView.ResourceLatestAvailableTo as [ResourceLatestAvailableTo],                MSP_EpmResource_UserView.ResourceCanLevel as [ResourceCanLevel],                MSP_EpmResource_UserView.ResourceHyperlink as [ResourceHyperlink],                MSP_EpmResource_UserView.ResourceHyperlinkHref as [ResourceHyperlinkHref],                MSP_EpmResource_UserView.ResourceNTAccount as [ResourceNTAccount],                MSP_EpmResource_UserView.ResourceIsActive as [ResourceIsActive],                MSP_EpmResource_UserView.ResourceIsGeneric as [ResourceIsGeneric],                MSP_EpmResource_UserView.ResourceIsTeam as [ResourceIsTeam],                MSP_EpmResource_UserView.ResourceBaseCalendar as [ResourceBaseCalendar],                MSP_EpmResource_UserView.ResourceWorkgroup as [ResourceWorkgroup],                MSP_EpmResource_UserView.ResourceClientUniqueId as [ResourceClientUniqueId],                MSP_EpmResource_UserView.ResourceCostCenter as [ResourceCostCenter],                MSP_EpmResource_UserView.ResourceCreatedRevisionCounter as [ResourceCreatedRevisionCounter],                MSP_EpmResource_UserView.ResourceModifiedRevisionCounter as [ResourceModifiedRevisionCounter],                MSP_EpmResource_UserView.ResourceCreatedDate as [ResourceCreatedDate],                MSP_EpmResource_UserView.ResourceModifiedDate as [ResourceModifiedDate],                MSP_EpmResource_UserView.ResourceCount as [ResourceCount],                MSP_EpmResource_UserView.[RBS] as [RBS],                 MSP_EpmResource_UserView.[Cost Type] as [Cost Type]              FROM                dbo.MSP_EpmResource_UserView             ORDER BY                dbo.MSP_EpmResource_UserView.ResourceName           


    Friday, May 1, 2015 4:54 PM
  • Please see my last reply. Replace the Replace that with the exact name of ResID and ProjectID custom fields in your environment. Keep the square brackets intact. You can copy the res ID line, and add Resource Location.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Friday, May 1, 2015 5:07 PM
    Moderator
  • I have tried above as well still shows "Invalid Column name" whereas with Select All I get that  same Column name . I am using 2 column name from below Res ID and Resource Location.

    Same with ProjectID

    Friday, May 1, 2015 5:13 PM
  • Hmm... I am not sure at this point.. seems like the name of the fields is different in the database

    Anyway, try this query one last time, as is: http://1drv.ms/1E1Yyhg


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, May 1, 2015 6:07 PM
    Friday, May 1, 2015 5:43 PM
    Moderator
  • Great It gave me Res ID and Location

    Now how can I add Project id?

    Friday, May 1, 2015 6:08 PM
  • give me a screenshot showing the field name, and I will add it to the query.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Friday, May 1, 2015 6:09 PM
    Moderator
  • SELECT  * FROM                dbo.MSP_EpmProject_UserView                INNER JOIN dbo.MSP_EpmEnterpriseProjectType ON                   MSP_EpmProject_UserView.EnterpriseProjectTypeUID = MSP_EpmEnterpriseProjectType.EnterpriseProjectTypeUID

    Friday, May 1, 2015 6:12 PM
  • try now. http://1drv.ms/1E1Yyhg

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Friday, May 1, 2015 8:03 PM
    Friday, May 1, 2015 6:25 PM
    Moderator
  • Thanks For All your help!
    Friday, May 1, 2015 8:04 PM