August, 2007

We've had a number of good questions come in.  I thought it would be helpful to share these and the answers.  Enjoy!

--Treb

Timesheet

Question: Which is true? 

Case 1:  Does the assigned timesheet manager approve all tasks in the timesheet (for example, project tasks as well as administrative time tasks) OR

Case 2:  Does the resource manager (i.e. the assigned timesheet approver) approve administrative time AND the project manager of the project the tasks originally came from approve the project tasks? 

SUMMARY
The Resource manager approves all timesheet data.  If the Admin timesheet categories require approval outside of timesheets, those are also approved by the timesheet manager.

LOOPING THE PM INTO THE APPROVAL CHAIN
The current timesheet submitter/approver can change the value of the next person to approve the timesheet.  Using this setting, the PM can be sent the timesheet to approve.  As long as the PM doesn't have the category approval permission for that resource, the PM can send the timesheet back to the resource manager for final approval.  One manages actual work while the other manages compliance with company policies.

Looking at Tasks

Question:  I'm using Project Professional and I need to answer the following questions:

• What work items/tasks are due by a given date?
• What work items/tasks are slated for <x> milestone?
• What work items/tasks are all assigned to <resource>?

Items Due This Week/By this date

In Project Professional, you can define a filter to show you this.  The filter will prompt you for a date so you can use this to see finishing by any date.

• In Project Professionals, select Project, Filtered for, More Filters

• Select New

• Define the following Filter and click OK

• You will then see this filter in your list of filters to apply.  Project, Filtered For, More Filters, Select the filter and click apply. Once you use it, it will appear in the earlier list so the number of clicks will be reduced.
• When you apply this filter, it will prompt you to enter the date.  You can enter any date, not just the date for the end of the week.

What work items/tasks are slated for <x> milestone?

The way we do this internally is to define a task level custom field.  You would then assign a milestone value to each task. 

Add a new task custom field to the Project.

• From Project Professional, select Tools, Customize, Fields

• Select the Task radio button,
• Select a default task custom field that’s not used (in this case, Text 1)
• Click Rename.  Your screen should look like this: 
• Name the field Milestone Group and click OK.
• Under Custom Attributes, click the Lookup button.  This will enable you to add values to your dropdown.
• If you want to set a default value, you can select a Milestone value row, and click Set Default
• If you want to change the display order, expand the display plus and select your option
• If you want to be able to add new values on the fly, expand data entry options and select Allow additional items to be entered into the fields.
• Click Close.
• Your screen should look like this:
• If you need to see this for every assignment, then also select the Roll down unless manually entered radio button under Calculation for assignment rows.
• Click OK.

Insert the custom field into your Gantt view

• You need to add the field to the view to enter the data.
• You can either select a column header and right click, select Insert Column or you can press the Insert key.
• You will get this dialog:
• Use the field dropdown to find your new custom field. You can start typing the name to find it.
• Click OK.
• Your screen will appear as this:
• You can click on the field, click the dropdown and select the value. 
• If all of your tasks are in the correct order, you can drag down the value a la Excel style, to fill the cells below.

Turning on Autofilter

• To easily filter by Milestone, then select Project, Filtered For, Autofilter. 
• Now, you can easily filter for a given milestone

What work items/tasks are all assigned to <alias>?

• If you have set up your resource names using the alias, you can use the default filter “Using Resource”. 
• Select Project, Filtered For, Using Resource
• Select the resource from the dropdown
• If you want to filter on another aspect of the resource record, a custom filter can be easily developed.

Added Bonus – Cumulative Filters

By the way, all of these filters can be used together.  So, by applying all three filters, I can see for a given milestone, within Milestone 1, which tasks will be completed by X date.

From Jim Corbin.  The technical article Importing Project 2007 Tasks from Excel Using a Managed Code Add-In is now published on MSDN. There is an associated download that includes the complete C# and VB.NET code, using Visual Studio 2008 (Beta 2) with Visual Studio Tools for Office.

It appears part of the where clause is missing on the published Timesheet Audit report.  The result is that the weekly totals are all the same for each resource.

To correct this, replace the SQL with the code below.  The addition is in bold below.

SELECT     MSP_EpmResource.ResourceName, MSP_TimesheetPeriod.PeriodName, 
                      MSP_TimesheetPeriodStatus.Description AS PeriodStatus, 
                      MSP_TimesheetStatus.Description AS TimesheetStatus, 
                      SUM(MSP_TimesheetActual.ActualWorkBillable) 
                      + SUM(MSP_TimesheetActual.ActualWorkNonBillable) 
                      + SUM(MSP_TimesheetActual.ActualOvertimeWorkBillable) 
                      + SUM(MSP_TimesheetActual.ActualOvertimeWorkNonBillable) 
                      AS TotalWork, MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate, 
                      MSP_Timesheet.TimesheetStatusID, MSP_TimesheetPeriod.PeriodStatusID
FROM         MSP_EpmResource LEFT OUTER JOIN
                      MSP_TimesheetResource INNER JOIN
                      MSP_TimesheetActual ON MSP_TimesheetResource.ResourceNameUID = 
                         MSP_TimesheetActual.LastChangedResourceNameUID ON 
                      MSP_EpmResource.ResourceUID = MSP_TimesheetResource.ResourceUID 
                         LEFT OUTER JOIN
                      MSP_TimesheetPeriod INNER JOIN
                      MSP_Timesheet ON MSP_TimesheetPeriod.PeriodUID = MSP_Timesheet.PeriodUID INNER JOIN
                      MSP_TimesheetPeriodStatus ON MSP_TimesheetPeriod.PeriodStatusID = 
                        MSP_TimesheetPeriodStatus.PeriodStatusID INNER JOIN
                      MSP_TimesheetStatus ON MSP_Timesheet.TimesheetStatusID = 
                        MSP_TimesheetStatus.TimesheetStatusID ON 
                      MSP_TimesheetResource.ResourceNameUID = MSP_Timesheet.OwnerResourceNameUID
WHERE     (MSP_EpmResource.ResourceTimesheetManagerUID = @TimeSheetManager) 

AND (MSP_TimesheetActual.TimeByDay BETWEEN MSP_TimesheetPeriod.StartDate AND MSP_TimesheetPeriod.EndDate) 

GROUP BY MSP_TimesheetPeriod.PeriodName, MSP_TimesheetPeriodStatus.Description, 
                      MSP_TimesheetStatus.Description, MSP_EpmResource.ResourceName, 
                      MSP_TimesheetPeriod.StartDate, MSP_TimesheetPeriod.EndDate, 
                      MSP_Timesheet.TimesheetStatusID, MSP_TimesheetPeriod.PeriodStatusID
HAVING      (MSP_TimesheetPeriod.PeriodStatusID = 0) OR
                      (MSP_TimesheetPeriod.PeriodStatusID IS NULL)

For your information, this report includes time logged in any open time periods.  The report was designed assuming you are closing timesheet periods on a regular basis.  Closing prevents time from being applied to an incorrect period.   If you are billing for your time, this is real important.  Anyway, if you aren't closing periods, this report will continue to get grow wider as columns are added for each open period.

We apologize for any inconvenience.