How to report Project Risks at a Program Level?
<Update June 2010> As a reminder as mentioned in this MSDN article: SharePoint Database Access “process 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
|
 |
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:
SELECT
ProjectStructure.Program,
ProjectStructure.Project,
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
INNER JOIN LISTS
ON UserData.tp_ListId = Lists.tp_ID
LEFT OUTER JOIN UserInfo
ON UserData.int1 = UserInfo.tp_ID
LEFT OUTER JOIN UserInfo AS UI
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))
RETURNS TABLE AS RETURN (
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 |  |
| Level AA |  |
| Level AB |  |
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
Reporting on Sharepoint lists from Microsoft SQL Reporting Services
Creating Recursive SQL Calls for Tables with Parent-Child Relationships