How to report Project Risks at a Program Level?

<Update June 2010> As a reminder as mentioned in this MSDN article: SharePoint Database Accessprocess for acquiring that data should be through the built-in SharePoint object model, and not by using any type of query to the database.” hence a much more elegant and supported solution to address this request is to use the object model (rewrite the T-SQL code below in managed C# using the SharePoint object model or if you are not a developer you could also leverage partner solutions such as this one to achieve the same result: How to report across multiple SharePoint lists? i-PMO SharePoint Data Miner to the rescue. Thanks to the SharePoint MVP community for the reminder on this un-supported scenario! </ Update>

A request I heard a few times from customers is how can you report Risks (or Issues) at a Program Level and since I’m snowed in it’s a perfect opportunity for a new EPM blog post! Since Risks and Issues are stored as lists within SharePoint another way of looking at this problem, is how can one create a list of lists from a SharePoint content database?

For instance lets assume I have the following project hierarchy in Project Web Access:


where Level A is a parent of Level AX and Level AX a parent of Level AXX. The levels could also represent Program/Project/Workpackage. At each level I have projects and for each projects I have a workspace associated with it (following the SharePoint site hierarchy shown above for ABA (add a ‘B’ and start singing!)):


Let’s assume I track Risks at each level and for each project shown above. How can you report all the risks at any level is the question? Remember that Issues and Risks are all tracked and stored in the SharePoint content database associated with the site collection. I hence wanted to build a report by querying the data directly in the SharePoint content database to ensure I have the latest and greatest data (remember that the SharePoint data (Issues/Risks/Deliverables) only flows in the reporting database once a project plan is opened and published). I could have leveraged SharePoint Designer to build a List of Lists by accessing the list webservice  (sample here), but instead of using ASMX I figured it would a lot more efficient to query the database directly.

There were two challenges in writing that query: figuring out the Sharepoint “Risk” T-SQL query and building a recursive function to find all the children “Risks” of each parent. Please find below how I solved these two challenges:

SharePoint “Risk” T-SQL query

I used the following post from Rob Fisch to get started: Reporting on Sharepoint lists from Microsoft SQL Reporting Services. I fired up the query editor in SQL 2008 to leverage Intellisense (I love it!) and started with this first query in the SharePoint content

 SELECT TOP 1 tp_FieldsFROM    ListsWHERE   Lists.tp_Title = 'Risks'

I then exported the XML result in notepad to figure out all the “Risk” field required.

 <FieldRef Name="ContentTypeId"/><FieldRef Name="Title" ColName="nvarchar1"/>
<FieldRef Name="_ModerationComments" ColName="ntext1"/><FieldRef Name="File_x0020_Type" ColName="nvarchar2"/>
<FieldRef Name="Owner" ColName="int1" StaticName="Owner" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="AssignedTo" ColName="int2" StaticName="AssignedTo" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Status" ColName="nvarchar3" StaticName="Status" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Category" ColName="nvarchar4" StaticName="Category" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="DueDate" ColName="datetime1" StaticName="DueDate" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Probability" ColName="float1" StaticName="Probability" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Impact" ColName="float2" StaticName="Impact" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Exposure" ColName="sql_variant1" StaticName="Exposure" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Cost" ColName="float3" StaticName="Cost" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Cost_x0020_Exposure" ColName="sql_variant2" StaticName="Cost_x0020_Exposure" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Description" ColName="ntext2" StaticName="Description" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Mitigation_x0020_plan" ColName="ntext3" StaticName="Mitigation_x0020_plan" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Contingency_x0020_plan" ColName="ntext4" StaticName="Contingency_x0020_plan" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Trigger_x0020_Description" ColName="ntext5" StaticName="Trigger_x0020_Description" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Trigger" ColName="nvarchar5" StaticName="Trigger" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>
<FieldRef Name="Links" ColName="ntext6" StaticName="Links" SourceID="{6E6DCD7D-D99B-4FE9-AAC6-283440B9076C}"/>


I then wrote the following query to retrieve all the Risk fields:


    UserData.nvarchar1       AS Title,
    UserInfo.tp_Title        AS 'Owner',
    UI.tp_Title              AS 'Assigned To',
    UserData.nvarchar3       AS 'Status',
    UserData.nvarchar4       AS Category,
    UserData.datetime1       As 'Due Date',
    UserData.float1          AS 'Probability',
    UserData.float2          AS 'Impact',
    UserData.sql_variant1    AS 'Exposure',
    UserData.float3          AS 'Cost',
    UserData.sql_variant2    AS 'Cost Exposure',
    UserData.ntext2          AS 'Description',
    UserData.ntext3          AS 'Mitigation Plan',
    UserData.ntext4          AS 'Contingency Plan',
    UserData.ntext5          AS 'Trigger Description',
    UserData.nvarchar5       AS 'Trigger',
    UserData.tp_Modified     AS 'Modified',        
    ProjectStructure.FullUrl AS 'URL',
    UserData.tp_DirName + '/DispForm.aspx?ID=' + CONVERT(varchar(5),UserData.tp_ID) AS [Risk URL]

FROM UserData
    ON UserData.tp_ListId = Lists.tp_ID
    ON UserData.int1 = UserInfo.tp_ID
    ON UserData.int2 = UI.tp_ID
INNER JOIN ProjectStructure(@ProgramName)
    ON ProjectStructure.Id = LISTS.tp_WebId 
WHERE tp_ContentType='Project Workspace Risk'
AND UserData.nvarchar3='(1) Active'
ORDER BY UserData.sql_variant1 DESC

Recursive Function to Find all Children that belong to a level

I used this blog post as a starting point: Creating Recursive SQL Calls for Tables with Parent-Child Relationships and created a function in the SharePoint content database. Notice how ProjectStructure does an inner join on itself hence the recursion:

 ALTER FUNCTION [dbo].[ProjectStructure](@ProgramTitle nvarchar(255))

WITH ProjectStructure (Id, [Program], [Project], FullUrl, [Level]) AS
        SELECT Id, Title AS [Program], Title AS [Project], FullUrl, 0
        FROM Webs
        WHERE Title = @ProgramTitle
        UNION ALL
        SELECT Project.Id, ProjectStructure.[Program], Project.Title, Project.FullUrl, 1
        FROM Webs AS Project
        INNER JOIN ProjectStructure
        ON ProjectStructure.Id = Project.ParentWebId

SELECT * FROM ProjectStructure

I then created a report in SQL Reporting Services 2008 (check these cool SSRS08 reports I did recently!) and voila!

Level A image
Level AA image
Level AB image

You can also run the query at the root (PWA level) and you’ll get all the Risks currently active in your Project Server instance. You could write a similar report for Issues. Last but not least use this reporting sample as a starting point for your reporting needs and don’t forget to test, test, test prior to any production deployment!

Happy reporting!

Related links

  SharePoint Database Access

  Reporting on Sharepoint lists from Microsoft SQL Reporting Services

  Creating Recursive SQL Calls for Tables with Parent-Child Relationships