none
how Can I add timesheet manager field to Business Intelligence center in sample report Resource in pwa 2010 RRS feed

Answers

All replies

  • It is not available out of the box. You will need to modify the SQL query in the ODC connection, to get the timesheet manager.

    Here is an example SQL query:

    select
    b.ResourceName,
    a.PeriodName,
    a.TimesheetStatus,
    b.[ResourceTimesheetManagerUID],
    (Select c.ResourceName from MSP_EpmResource_UserView c where b.[ResourceTimesheetManagerUID]=c.resourceuid) AS TimesheetManager
    from
    MSP_TimesheetLine_UserView a
    INNER JOIN
    MSP_EPMResource_Userview b on 
    a.ResourceUID=b.resourceuid

    Here is how to create new report by modifying the ODC connection: http://msprojectnow.com/blog/create-a-new-excel-report-from-existing-modifying-odc-excerpt-from-business-intelligence-course


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Tuesday, April 21, 2015 5:37 PM
    Moderator
  • Below is the query I see in my  Report . Should I replace this with your query? or Just add a line 

    MSP_TimesheetLine_UserView.ResourcetimesheetManager as [Timesheet Manager]

            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   

    Tuesday, April 21, 2015 6:33 PM
  • Please try this 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],

    (

    Selectc.ResourceName fromMSP_EpmResource b wherec.[ResourceTimesheetManagerUID]=b.resourceuid)ASTimesheetManager             


    FROM

                   

    MSP_TimesheetLine_UserView


    INNER

    JOIN


    MSP_EPMResource_userview c



    on

    MSP_TimesheetLine_UserView.ResourceUID=c.ResourceUID  


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Tuesday, April 21, 2015 7:07 PM
    Moderator
  • Thank you for your help!

    I am following the blog above and having some issues 

    I don't see step 18 when I hit save but file is saved at the location but file is saved on Business Intelligence Center à Data Connections à English (United States)  and  not getting Publish a Major Version option.
    I am stuck at Step 30 its not giving me option to move further . See screenshot below.

    Please advice 

    P.S I am replacing existing query with yours . 

    Tuesday, April 21, 2015 7:35 PM
  • Somehow I am now able to Add the created connection to my report but when I refresh it, its giving me below error.

     
    Tuesday, April 21, 2015 8:13 PM
  • Looks like the formatting of this forum messed up the query. I loaded a text file with the query here. Please copy/paste from the text file.

    http://1drv.ms/1E87aXY


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Tuesday, April 21, 2015 8:32 PM
    Moderator
  • The query works fine and it has added timesheet manager field but its giving me resource name in timesheet manager field.
    Wednesday, April 22, 2015 2:08 PM
  • Have you populated the Timesheet Manager for the resources in the Resource Pool? if you do not set any one to be the Timesheet Manager, the resource is by default their own timesheet manager.

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, April 22, 2015 2:27 PM
    Moderator
  • Yes, all resources have timesheet managers.
    Wednesday, April 22, 2015 2:34 PM
  • Sorry, there was a small mistake in my query. Please use the updated one from here: http://1drv.ms/1E87aXY

    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Project help Wednesday, April 22, 2015 3:13 PM
    Wednesday, April 22, 2015 2:50 PM
    Moderator
  • Thank You!
    Wednesday, April 22, 2015 3:13 PM