SQL Query to Get Project Resources by Role


I was developing some reports on Project Server 2003 recently, while working I got a Question.

Question : I need to get Projects, Its Resources, Resource Role in Project Server and Project Created by in a report.

The query to get this done is give below.

SELECT     MSP_PROJECTS.PROJ_NAME AS Project, MSP_WEB_RESOURCES.RES_NAME AS Resource,
                      MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME AS [Role In Project], MSP_PROJECTS.PROJ_PROP_AUTHOR AS [Project Created By]
                      –MSP_RESOURCES.PROJ_ID, MSP_RESOURCES.RES_EUID, MSP_RESOURCES.RES_UID
FROM         MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
                      MSP_WEB_SECURITY_GROUPS ON
                      MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID = MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
                      MSP_WEB_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID = MSP_WEB_RESOURCES.WRES_GUID INNER JOIN
                      MSP_PROJECTS INNER JOIN
                      MSP_RESOURCES ON MSP_PROJECTS.PROJ_ID = MSP_RESOURCES.PROJ_ID ON
                      MSP_WEB_RESOURCES.RES_EUID = MSP_RESOURCES.RES_UID
WHERE     (MSP_RESOURCES.RES_UID > 0) AND (MSP_RESOURCES.RES_EUID IS NOT NULL) AND (MSP_PROJECTS.PROJ_TYPE = 0)
ORDER BY Project, Resource, [Role In Project]

 

Note: Please do test this query in local server dev before using it on production environments.

Microsoft Project 2007 – Baselines and Updating Project Progress


Saving and using Baselines

You should save the baseline when:

  • You have developed the project plan as fully as possible. (However, this does not mean that you cannot add tasks, resources, or assignments to the project after work has started, for this is often unavoidable.)

  • You have not yet started entering actual values, such as a task’s percentage of completion.

Tip 

You can set up to 11 baselines in a single plan. (The first one is called Baseline, and the rest are Baseline 1 through Baseline 10.) Saving multiple baselines can be useful for projects with exceptionally long planning phases in which you might want to compare different sets of baseline values. For example, you might want to save and compare the baseline plans every month as the planning details change. To clear a previously set baseline, click Clear Baseline on the Tools menu, Tracking submenu. To learn more about baselines in Project’s online Help, type create baseline.

Three ways to update project progress

Overall Project Progress

The simplest approach to tracking progress is to report that the actual work is proceeding exactly as planned. For example, if the first month of a five-month project has elapsed and all of its tasks have started and finished as scheduled, you can quickly record this in the Update Project dialog box.

image

Task updates using % Complete and Actual Start

After work has begun on a task, you can quickly record its progress as a percentage. When you enter a completion percentage other than 0, Project changes the task’s actual start date to match its scheduled start date. It then calculates actual duration, remaining duration, actual costs, and other values based on the percentage you enter.

image

 

Issues Fixed by Project Server 2007 Service Pack 1 – 2


Project Server KB 937908 In the My Tasks page, items are grouped by project. By default, the maximum number of items to display on a page is 30. When the maximum number of items is reached, paging occurs. You have to switch between the pages to view all the tasks. If a project grouping is collapsed, the page does not refresh to bring tasks from other pages back. Therefore, the page does not display 30 items to the user. This causes tasks that straddle the paging to become orphaned from the project.
Project Server KB 937908 When a project manager receives a task update, there is no way for the project manager to know what has changed within the specific update. Also, the view in Approvals/Task Updates shows only the current period.
Project Server KB 937908 Using the local calendar may cause a variance between PWA and Project Professional.
Project Server KB 938189 Custom fields in a Project 2007 project appear in the order in which they are created. However, the custom fields are not sorted alphabetically by name.
Project Server KB 938889 When you migrate projects from an earlier version of Microsoft Office Project to Project 2007, any enterprise outline code that contains formulas may not behave as expected. Specifically, when you try to change the value of the outline code, the value may not change unless you delete and then re-create the outline code. 
Project Server KB 938889 When you copy and paste an indented task under a different parent task, the indented task does not become a child of the new parent task.
Project Server KB 938889 When you try to publish a Project 2007 project in Project Web Access, you may receive the following error message:
Error summary/areas:
Reporting message processor failed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
ReportingProjectChangeMessageFailed
Queue
GeneralQueueJobFailed
This issue occurs when you create one or more empty tasks in a project.
Project Server KB 938889 If an enterprise outline code has an incorrect code mask applied to it, incorrect values can be entered into the outline code.
Project Server KB 939161 Consider the following scenario. You add both standard and generic enterprise resources to a Microsoft Office Project Server 2007 project. You use a reporting database to track the time that the resources are available and their capacity. However, when you view the reporting database table, only the standard enterprise resources are present in the table. The generic enterprise resources are missing.
Project Server KB 939162 When you try to run a Microsoft Visual Basic for Applications (VBA) macro to access each Task object in the ActiveProject object in Microsoft Office Project 2007, you receive the following error message:
Run-time error ‘438’: Object doesn’t support this property or method. 
Project Server KB 939163 When you add multiple values to a lookup table that is used by an outline code, Microsoft Office Project 2007 takes longer than expected to add the new values. In some cases, it may take five to 10 times longer to add the new values.
Project Server KB 939182 When you try to set the baseline for a subtask and then roll up the baselines to a summary task, the summary task displays an incorrect value.
Project Server KB 939594 You create an OLAP cube in Project Server 2007. You try to use the "Resource is Active," "Resource is Generic," or "Resource is Team" dimension from the MSP_Portfolio_Analyzer cube. When you do this, no resources are displayed. 
Project Server KB 939595 You open the Resource Information dialog box in Project 2007, click either the Windows account or Details, and then perform any other action. When you do this, Project 2007 crashes. 
Project Server KB 939595 When you try to open a link in a task to a task in a different project that resides on Project Server, you may receive the following error message:
Cannot find specified project.
This issue occurs if the project that was linked to was renamed.
Project Server KB 939595 When you use the Leveling function on a collection of tasks, the end dates of some tasks are not calculated correctly. Additionally, the end date of these tasks and the of project may be much later than expected
Project Server KB 941426 After you apply the update KB 940133, the pjclient.cab file is updated. However, only the pjgrid12.ocx file inside the pjclient.cab file is updated. Additionally, the ASP pages that are required for pushing and updating controls on the Project Web Access (PWA) client are not updated.
Project Server KB 941426 When tasks are being updated in PWA, you cannot open the project in Project 2007 or Project 2007 may stop responding.
Project Server KB 941426 The server-side scheduling code does not check for assignment work contours values during the scheduling operation.
Project Server N/A N/A If a project calendar has default start and end times other than 8am and 5pm, certain calculations are incorrect. For example, if you have a task that starts on 9/26 and ends 9/27, and you work 7 hours per day, the total work is 14 hours. If you then enter a new end date as 9/28, the total work is recalculated as 24 hours because Project 2007 assumes that you work 8 hours per day.
Project Server N/A N/A When you try to reassign tasks to other team members, you may receive the following error message:

The selected task(s) could not be reassigned due to the following reason(s):
Access Denied
This issue may occur if you are the assignment owner and not the current resource.

Project Server N/A N/A You create an enterprise template that has enterprise resources. If you create a project from the template, the project does not recognize the resources as enterprise resources. Instead, the resources are listed as local resources.
Project Server N/A N/A Dataset constraints are too strict for resource custom field values. The same custom field value is returned for each resource per project. However, the key only specifies custom field and this causes duplicate key errors.
Project Server N/A N/A When you try to save a light-weight project that has custom task fields and you have a summary task in the project, you may receive the following error message:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints. 

Project Server N/A N/A Light-weight projects will not work correctly if the projects have milestone tasks.
Project Server N/A N/A When you try to close tasks by using the admin features, you may receive the following error message:

The selected Tasks could not be locked because of the following reasons: A required custom field value was not provided.

Project Server N/A N/A If you have a project level custom field that uses a formula, the custom field does not recalculate for activity and proposal projects.
Project Server N/A N/A When you try to save an activity plan after you set value in the second custom field, the activity plan cannot be saved and you may receive the following error message:

An error has occurred when saving your project to the server. Please contact your administrator. 

Project Server N/A N/A If you connect to a server and then edit enterprise formulas, you may destroy the formulas if the client settings use a semicolon.
Project Server N/A N/A Both actual and scheduled work time-phased assignment data will be inaccurate in the remote database (RDB) and cubes.
Project Server N/A N/A You enter values into a resource plan but make a mistake. When you save the resource plan, you receive an error. After you fix the incorrect cells and save again, other valid data is lost.
Project Server N/A N/A You cannot view all potential owners in the Owners list on the Edit Project Properties page. You can only see the users who have the permission to save projects.
Project Server N/A N/A If any fields are changed in the assignment details page before you submit the task assignment, the change log for Actual works that is imported from timesheet is lost.
Project Server N/A N/A When you add resources to the team for a proposal, the resource custom field values for the resource are cleared from the view.
Project Server N/A N/A If a save fails and blocks correlation, you may receive a correlation blocked error on a second save. This is because the Project save function cannot automatically handle CorrelationBlockedExcepitons .
Project Server N/A N/A When you add some resource custom field values in the Build Resource Plan Team page view, most values do not appear.
Project Server N/A N/A If you have a language pack installed on a Project server, those users who view PWA custom field data by using the language pack cannot see the data.
Project Server N/A N/A When you try to import a timesheet in PWA and there are null values in the assn_work and assn_act_work field in the msp_assignments_saved table, the import fails and you receive the following error message:

Error – unknown error – troubleshoot with Windows SharePoint Services.

Project Server N/A N/A After creating a deliverable and linking it to a task, you may receive the following error message: "An unspecified error has occurred. Error Code: -2146827187"  
Project Server N/A N/A You create a custom field and then set or change the "Calculation for Summary Rows" setting. When you do this, the setting is saved, but it is not read the next time that the Edit Custom Field page is read. Therefore, it is always set to "Use Formula."  
Project Server N/A N/A When you make changes to the Option or Time column on the Daily Backup Schedule page, your changes are not saved.  
Project Server N/A N/A Custom field multi-level value drop-down lists are not displayed correctly in Project Web Access if the values have long descriptions. 
Project Server N/A N/A After you save and publish a resource plan, you may receive the following error message: "The resource plan is no longer checked out to you and your changes will not be saved.  Contact the Project Server administrator." This issue occurs because the "Save and Publish" job was not completed before the Web page was refreshed.
Project Server N/A N/A You create a server-side plan, set a custom duration field value, and then save. When you convert the server-side plan to a project and then try to open it in Project Professional, the value for the custom duration field is not displayed.   
Project Server N/A N/A You create a duration lookup table via the PSI that does not specify the duration format. When users try to launch Project Professional and connect to the server, they may receive a "job has failed" error followed by an error message that states that the enterprise template did not load.  
Project Server N/A N/A If you try to reopen a project soon after closing it, you may receive an error message that states that the project cannot be found or that it may not be completely saved. This issue occurs because the last save job has not yet finished. This error is more likely to occur for projects that are large and have a lot of custom field data. 
Project Server N/A N/A If you create a resource center view and then apply a server-side Cost Rate filter, the value that you enter for the filter is multiplied by 100.
Project Server N/A N/A You track tasks on an "Hours of work done per period" basis. If the actual work that you enter occurs before the assignment’s start date, you cannot use the Assignment Details page to add remaining work.  You may also receive the following warning: "New finish date must occur after start date and between 1/1/1984 and 12/31/2049."  
Project Server N/A N/A If you try to create a server-side project with task durations that are longer than 6.6 years, you may receive an error that states that the duration is invalid. The limit has been corrected to 64 years (1/1/1984 – 12/31/2049) to cover all of Project time.
Project Server N/A N/A When working in the Project Web Access Timesheet view, columns that contain very long project names, task names, and other names expand to be very wide. This condition makes navigating the page very difficult. Now, the column widths stay more constant. As you select cells, the row height automatically expands so that you can see the full text string. 
Project Server N/A N/A If you delete a value from an existing lookup table, you may lose the entire value list.  
Project Server N/A N/A The Active Directory Resource Pool Synchronization feature does not populate the Assignment Owner field.  Therefore, resources do not see their task assignments until that field is populated and the assignments are published again. 
Project Server N/A N/A Extended characters may not be displayed correctly in Project Web Access.  For example, you have a resource whose name is Sørensen.  When the name is displayed in the Timesheet Header on the Timesheet page, the "ø" is replaced with "&#248."  
Project Server N/A N/A Creating a new resource through Project Professional does not set the default assignment owner or timesheet manager.  Therefore, the resource cannot assign a status to his or her own assignments in Project Web Access until an administrator sets these properties and republishes the project.  
Project Server N/A N/A When you save a project to the Project Server server, you may receive the following error message: "Project Server encountered a problem with this error code: 42." This issue may be caused by bad data in a field that causes a dependent field calculation to fail. This unexpected data causes an overflow exception that Project Server cannot handle.
Project Server N/A N/A A user does not see any plans in Project Center. However, the user could previously see plans. This issue occurs because of an incorrect SQL query statement that returns no valid values for the Project Center view. 
Project Server N/A N/A If a Project Server process loses its SQL connection, it may not be able to recover and reconnect. Therefore, the Microsoft Office Project Server Queue Service needs to be manually restarted. Now, the service will restart so that processing can continue.
Project Server N/A N/A When using the Resource Substitution Wizard, other plans for which resources are also assigned are not found and listed in the wizard.  This issue makes it difficult to manage over-allocations for those same resources in other plans.
Project Server N/A N/A You create a resource plan for the second time during a Project session. You change the booking type setting for a resource from Committed to Proposed. Then, you try to save and publish the resource plan. When you do this, the booking type is not retained.  
Project Server N/A N/A When creating and working with large server-side plans, the system may appear to hang or you may receive a time-out error.  
Project Server N/A N/A When updating tasks via PSI calls, you may see queue jobs blocked, and you may receive SQL database deadlock errors.   
Project Server N/A N/A An enterprise resource is checked out by a system process such as the Active Directory Resource Pool Synchronization, and the resource is not correctly checked back in. When this occurs, the resource does not appear on the Force Check-in Enterprise Objects page. This condition makes it impossible to check in the resource from Project Web Access.  
Project Server N/A N/A You import a WSS task list into a server-side plan in Project Web Access. If you are using a non-standard calendar, Project Web Access may display an error message that resembles the following: "System.Web.Services.Protocols.SoapException: ProjectServerError(s) LastError=ProjectSchedulingEngineException Instructions: Pass this into PSClientError constructor to access all error information." 
Project Server N/A N/A When editing a custom field that has a graphical indicator from the Project Properties page, the edit is not always updated. Additionally, the graphical indicator does not change.
Project Server N/A N/A When drilling into a Project view for a large or complex project, a long delay occurs, and the project is not displayed. Instead, you may receive the following message: "Project Center cannot access the project(s) you are trying to view. It is most likely that you either don’t have permissions to view the project, another user has deleted this project(s) before you were able to view it or that another user is in the process of publishing the project." 
Project Server N/A N/A When you try to use the Build Team feature in Project Web Access, you may receive the following error message: "Unknown Error."

 

Source: Microsoft Excel Sheet for Changes

List of Issues Fixed by Microsoft Office Project 2007 Service Pack 1


Application

Documentation Type

KB Article

Description

Project and Project Server

Project and Project Server

N/A

N/A

You may receive multiple false error messages when you try to save a Project 2003 XML file to Project Server 2007.

Project and Project Server

N/A

N/A

After entering the "percent complete" value for a task, the task finish date is unexpectedly rescheduled into the future. This issue occurs in a plan that is scheduled from finish with a cost and a work resource assigned.

Project and Project Server

N/A

N/A

You cannot edit an enterprise resource’s name by opening the enterprise resource pool in Project Professional because the field is disabled.

Project and Project Server

N/A

N/A

You save an existing plan as an Excel file and then try to save the project plan back to the server. When you do this, you may receive the following false error message: "You do not have sufficient permissions to save this project to Project Server. Please contact your server administrator."

Project and Project Server

N/A

N/A

When using the Assign Resources dialog box, all resources in the dialog box are selected. This condition makes it easy to mistakenly assign all resources to a task or to a group of tasks.

Project and Project Server

N/A

N/A

If a subproject is open or checked out, and if the same plan is modified in a master plan, the master plan cannot be saved.

Project and Project Server

N/A

N/A

You cannot see assignments for other shared files when opening a project that shares a resource pool across multiple MPP files and when the other projects are not opened.

Project and Project Server

N/A

N/A

In Project 2007, you navigate to a Windows SharePoint Services 2.0 Web site list that has an MPP file and then click "Edit in Microsoft Project." When you do this, the file opens as read-only instead of as read-write. Now, a dialog box displays a choice for read-only or for read-write.

Project and Project Server

N/A

N/A

On the Tools menu, you click Options and then click Calculation. If you select the "Move end of" and "Move start of" options, and if you add actual work to assignments, the task’s remaining work value and finish date are changed incorrectly.

Project and Project Server

N/A

N/A

You create a project using the default calendar settings in the English version of Project and then open the same project in the French version (or in a version that by default has a different calendar). Then, you save the project in the Project 2003 format. When you reopen the project, it may not calculate correctly.

Project and Project Server

N/A

N/A

When the time-phased data for a resource assignment gets into a "bad" state, the server-side scheduling engine may consume a large amount of CPU processing time. Additionally, this issue prevents previewing or applying updates on projects.

Project and Project Server

N/A

N/A

If the URL for project workspaces that are created on publish is longer than 100 characters, you may receive the following error: "The Project workspace cannot be provisioned. The site where the project … "

Project and Project Server

N/A

N/A

If you save an MPP file with a write reservation password, you cannot open the project again in read-only mode.

Project and Project Server

N/A

N/A

When opening an XML file in Project 2007 that contains custom fields, you may receive the following error message: "An unexpected error occurred during command execution."

Project and Project Server

N/A

N/A

In Project Professional, you access the File/Open dialog box and then select the Group By list. When you do this, you may not see all the available fields, and there is no scroll bar to allow you to navigate through the list. A scroll bar has been added to the dialog box to allow you to see all the values.

Project and Project Server

N/A

N/A

When you use the Project Task Information dialog box to change the resource for an existing assignment, the assignment GUID changes. This causes a problem when trying to access the original assignment GUID, such as when another application updates or modifies assignments.

Project and Project Server

N/A

N/A

If you create a multiple-day task and then enter zero actual work for the first day, the task is rescheduled as expected. However, if you enter zero actual work on the second day, nothing is recorded, and the task is not rescheduled.

Project and Project Server

N/A

N/A

Baselines take a very long time to save or are not saved at all. This issue may occur for complex plans, such as plans that have a large number of custom calendars, long elapsed duration tasks, and so on.

Project and Project Server

N/A

N/A

Using a graphical indicator in a Project Center view, the field may not display the correct value for the Project Summary task.

Project and Project Server

N/A

N/A

If you are using a remote Local Cache folder, and if you lose network connectivity, a new local cache folder is created. There is no error message. The next time that you open a project from your local cache, the plan is not listed. Now, a message is displayed that tells you about the problem and states that your data will be lost.

Project and Project Server

N/A

N/A

When you copy data from Excel that includes blank cells and then paste the data into Project, you may see garbled text.

Project and Project Server

N/A

N/A

When you modify an enterprise resource custom field value for a local resource, save the plan, and then re-open the plan, the field value may revert to the original value.

Project and Project Server

N/A

N/A

You open a plan from the Project Web Access Project Center in Project Professional and then modify the plan. If Project is closed before the save process reaches 100%, the plan may not correctly be saved again when subsequent modifications are made.

Project and Project Server

N/A

N/A

If you have a custom field that has a formula, and if you group a view by that custom field, Project may crash.