Projects' Risks and Issues custom columns Querying in Project Server 2010



Working with Project Server 2010 Reporting database makes me realize how it can be painful to get information and properties that was customized on Risks or Issues sharepoint lists (or any sharepoint list).

Risks, Issues, and Deliverables are lists in the project site, yet they are project's properties. those properties have their own Tables in the project server 2010 reporting database as:

  • Risks : MSP_WssRisk
  • Issues : MSP_WssIssue
  • Deliverables: MSP_WssDeliverable

And since they are sharepoint lists then they can be customized to add columns or update column's values. and there the trick is ... the new custom columns are added to the sharepoint content database as database columns that will be named by the data type of the property and a postfix number, in the [AllUserData] table like: datetime2, sql_variant1, nvarchar9 ...etc.

After lots of googling the way to do it I have found a blog (unfortunately I can't find it again) that suggested a query to do so, it will be like this:

SELECT B.ProjectUID, B.IssueUniqueID,....., A.datetime2 as [Date Raised]
FROM PWAContentDatabase.dbo.AllLists lists
        INNER JOIN PWAContentDatabase.dbo.AllUserData as A ON lists.tp_ID = A.tp_ListId
        INNER JOIN dbo.MSO_WssIssue as B ON a.tp_DocId = B.IssueUniqueID
        INNER JOIN dbo.MSP_Epm_Project_UserView AS C ON B.ProjectUID = C.ProjectUID

I have tried it and it worked as charm except for one detail; consider having this daily used scenario:
  1. open the project site
  2. open the issues list (for example)
  3. add a new Issue
  4. close the Add Issue dialog
  5. open the Issue for edit and save again
in that query there will be redundant records for the same item (Issue in our example), and the reason of that is sharepoint lists keep track of each update for versioning issues (Version History, to view all changes took place on this list item). So to avoid such redundant records there is a column in the [AllUserData] table called [tp_IsCurrentVersion] that indicates which record is the working (current) one.

so our final SQL query will be like this:
SELECT B.ProjectUID, B.IssueUniqueID,....., A.datetime2 as [Date Raised]
FROM PWAContentDatabase.dbo.AllLists lists
        INNER JOIN PWAContentDatabase.dbo.AllUserData as A ON lists.tp_ID = A.tp_ListId
        INNER JOIN dbo.MSO_WssIssue as B ON a.tp_DocId = B.IssueUniqueID
        INNER JOIN dbo.MSP_Epm_Project_UserView AS C ON B.ProjectUID = C.ProjectUID
WHERE (A.tp_IsCurrentVersion = 1)


And finally,to avoid keep using the same query a lot I suggest to use this query as database view.


It worked for me I hope it will work for you as well :)

Comments

Post a Comment

Popular posts from this blog

Project Server 2010 : Server Event Handlers (Part 1)

Image
Server Event handlers are the expansion points for Project Server 2010, to register a custom event handlers with project server's events, Project Server raises pre-events and post-events when Project Server Interface (PSI) methods change business objects such as Project, Task, Resource, or Timesheet. When you associate an event handler with an event, Project Server runs the event handler when that particular event occurs. To start with event handlers development, you need to create new Class Library Project, from File > New > Project and select Class Library template (Figure 1) Figure 1 Delete the default class file class1.cs and right click on the project Add > New Item select class and rename it to ProjectEventHandler.cs , that will create new class file with a class called ProjectEventHandler . Add public access modifier to make this class accessible. Add these Project Server 2010 reference, right click on the project > Add Reference on the browse ta...
Read more

Project Server 2010 : Server Event Handlers (Part 2)

Image
Now we have wrote the business logic code, and registered the dll in GAC (in Part 1 ), we will register this event handler association in Project Server Application, so that when an event is raised, our function will be triggered. lets open server side event handler from Server Settings under Operational policies chose Server Side Event Handler , this will open the following page: Figure 1 Chose the event that we need to trigger our function on, in our example from the previous post it will be Project on Creating; locate the Project parent and Creating event, it will list all the registered associations (if there are any). to add new association click on New Event Handler which will open the following page: Figure 2 the Display Information section is for the name and description that appears in the list. the System Information section is to fill the dll information from GAC, as below: Assembly Name: which is the dll's name in this format ddlName, Version=1.1....
Read more

Retrieving Sharepoint Lists' Information In Project Sites - Project Server 2010

Image
In a previous blog post we have discussed how to retrieve Risks and Issues custom columns information (built in project properties) from Sharepoint through SQL query. But ... what If I have another custom list that is not a project property by default, lets say "Project Monthly Review" that contains these columns "Month" as text, "Year" as text, and "Review" as multiline text. for retrieving this information in Sharepoint 2010 through SQL below is the query that will just do the work: SELECT  P.ProjectUID, A.tp_ListId, A.tp_IsCurrent, A.nvarcahr3  as  [ Year ], A.nvarchar4  as  [ Month ], A.nText2  as  [Review] FROM  PWA_ContentDB.dbo.AllLists  as  L   INNER   JOIN  PWA_ContentDB.dbo.AllUserData  AS  A  ON  L.tp_ID = A.tp_ListId There the trick ... again ... the new custom Sharepoint list added to this certain site template (or project site) is not connec...
Read more