none
Need help with modifying an odc file RRS feed

  • Question

  • I have an almost out of the box report using the timesheet data sample report.  I need to add a text based Project custom field and I do not know how to do the join statement. So what I need is to add a project number (free form text) to the timesheet report so it displays the project name / project number along with the hours.
    Tuesday, February 4, 2014 8:43 PM

Answers

  • Hello,

    See the example below for added a project level custom field to the example timesheet ODC SQL query:

    SELECT
                    TL.PeriodUID as [PeriodUID],
                    TL.PeriodName as [PeriodName],
                    TL.PeriodStatus as [Period Status],
                    TL.TimesheetName as [TimesheetName],
                    TL.TimesheetUID as [TimesheetUID],
                    TL.TimesheetStatus as [Timesheet Status],
                    TL.TimesheetLineClass as [Timesheet Line Class],
                    TL.ResourceName as [ResourceName],
                    TL.ResourceUID as [ResourceUID],
                    TL.ProjectName as [ProjectName],
    				P.[Plan Status] as [Plan Stauts], --Modify this with the name of you project level field
                    TL.ProjectUID as [ProjectUID],
                    TL.TaskName as [TaskName],
                    TL.TaskUID as [TaskUID],
                    TL.TimesheetLineUID as [TimesheetLineUID],
                    TL.PlannedWork as [Planned Work],
                    TL.ActualWorkBillable as [Billable Actual Work],
                    TL.ActualWorkNonBillable as [Non Billable Actual Work],
                    TL.ActualOvertimeWorkBillable as [Billable Actual Overtime Work],
                    TL.ActualOvertimeWorkNonBillable as [Non Billable Actual Overtime Work],
                    TL.ActualWorkBillable + TL.ActualWorkNonBillable + TL.ActualOvertimeWorkBillable + TL.ActualOvertimeWorkNonBillable as [Timesheet Line Actual Work],
                    TL.PeriodStartDate as [Period Start Date],
                    TL.PeriodEndDate as [Period End Date],
                    TL.[RBS] as [RBS] 
    FROM		    MSP_TimesheetLine_UserView TL
    INNER JOIN		MSP_EPMProject_UserView P
    ON				P.ProjectUID = TL.ProjectUID
    

    I have added an example custom field called P.[Plan Status], update this for your project level custom field name.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    • Marked as answer by john.klinger Wednesday, February 5, 2014 2:12 PM
    Wednesday, February 5, 2014 2:04 AM
    Moderator