We needed a script to run twice a day, each night and at noon, against the SQL Server database to hide tasks for any project that was designated as "Closed" or "Cancelled." In order to accomplish this, we added a custom enterprise project outline code to store the project status. We changed the necessary database table switches to hide the closed or cancelled project tasks, so they wouldn't show up in the task view on Project Web Access. In this article I share the steps to accomplish this goal. These changes were done in Project Server 2003.

The change will require administrator security privileges.

1. Launch the Microsoft Project desktop client and connect to the project server URL.
2. Select Tools | Enterprise Options | Open Enterprise Global.
3. Select Tools | Customize Enterprise Fields.
4. Select the Custom Outline Codes tab.
5. Select the Project radio button.

6. Select the outline code you wish to use by clicking on it once.
7. Click Rename.
8. Type a descriptive name.
9. Select View Code Mask and enter it as shown in Figure 2.

10. Select Lookup Table and enter the values necessary.

This SQL code looks for the values "COMPLETE" or "CANCELLED". If found, it will flip the switch to hide the corresponding tasks.

UPDATE [ProjectServer].[dbo].[MSP_WEB_ASSIGNMENTS]
SET WASSN_REMOVED_BY_RESOURCE = 1
WHERE WRES_ID <> -1
  and WASSN_REMOVED_BY_RESOURCE <> 1
  and WPROJ_ID IN (SELECT A.WPROJ_ID
          FROM [ProjectServer].[dbo].[MSP_WEB_PROJECTS] A,
                  [ProjectServer].[dbo].[MSP_CUBE_PROJECTS] B,
                  [ProjectServer].[dbo].[MSP_CUBE_ENTERPRISE_PROJECT_OUTLINE_1] C
          WHERE (C.PROJ_OUTLINECODE_NAME = 'Completed' or
                 C.PROJ_OUTLINECODE_NAME = 'Cancelled')
             and C.PROJ_OUTLINECODE_ID = B.ENTERPRISE_PROJECT_CODE1
             and B.PROJ_UID = A.PROJ_ID)

MPA member Rick Theisen is an IT Senior Project Manager for media and marketing company Meredith Corp. Contact him at Rick.Theisen@Meredith.com.