#ProjectServer Active Issues and Risks on PWA Reminders Web Part #PS2010 #SP2010 #SharePointJuly 13, 2012 at 11:05 am | Posted in Administration, Configuration, Customisation, Functionality, Issue, T-SQL | 1 Comment
Tags: Project 2007, Project 2010, Project Server 2007, Project Server 2010, PS2007, PS2010
The Project Server reminders web part contains key information relating to all projects that the logged on user is involved in. This includes new task assignments, timesheet information, pending approvals and active issues and risks assigned to the logged on user. This post focuses on the issues and risks count as I have seen a number of queries relating to the issues and risks not showing on the reminders web part.
Firstly I will detail where this web part gets the data from and then show working / non working examples. This web part reads data from the Project Server Reporting database, two stored procedures create the dataset for the web part then the code on the web part counts how many of those issues and risks are active. The status value is key to the issues and risks count on the reminders web part, this will be shown in the examples later on. In the image below you can see that I have 3 active risks and 2 active issues assigned to me on my test system:
Lets now take a look what the two stored procedures return from the Reporting database. Firstly I need to get my ResourceUID to pass into the stored procedures using the simple query below against the Reporting database:
The two stored procedures are MSP_WSS_ReadResourceIssues and MSP_WSS_ReadResourceRisks. These two stored procedures require a RES_UID value, in this example I will use my ResourceUID C5733575-2C0A-4B40-A367-E33B82A1CB33 as shown below:
As you can see I have 3 risks and 4 issues assigned to me. Notice on the PWA reminders web part it shows 3 active risks which matches the SQL dataset and 2 active issues which doesn’t match the SQL dataset. The issues SQL dataset returns four issues, 3 of which are active in the lists and one which is postponed.
For active issues and risks to appear on the reminders web part it is key that the Status value is (1) Active, notice that the 4th issue has a value of Active without the (1).
If I change the issue list settings for the PM Testing Assn project site so that the Status choice value includes (1) Active then edit the item, the reminders web part will correctly show 3 active issues.
The current Status choice values on the PM Testing Assn project site are shown below:
Once this choice list has been updated to include the correct Active value and the issues edited the SQL dataset will contain the correct values and therefore so will the reminders web part. The choice values updated to the correct values shown below:
Now after editing the issue item the correct active Status value will appear:
The SQL stored procedures return the correct values after the Reporting (Project Sync) job has successfully completed in the Project Server queue:
Refreshing the PWA homepage now also shows the correct count on active issues and risks:
So the keys things to remember to make sure the reminders web part shows the correct counts for the active issues and risks are:
- ensure the Issues and Risks list default status values are not updated
- ensure the Reporting (Project Sync) jobs completed successfully
When updating the project site template it might be worth testing all default functionality, a useful project site smoke test can be found here: