Projects' Risks and Issues custom columns query for reporting in 2010 / 2013

Working with Project Server 2010 / 2013, 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 and those properties have their own tables in the project server 2010 / 2013. These are the following name of each:

• 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 and etc 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:

• Open the project site
• Open the issues list (for example)
• Add a new Issue
• Close the Add Issue dialog
• 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)


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