Creating Deliverable Reports

I have had many questions with regards to the Deliverables feature and reporting. If your not familiar with Deliverables, I suggest you take a look at my blog post on the Project blog:

http://blogs.msdn.com/project/archive/2007/02/24/deliverables.aspx

Just like all other project data, when a project plan is published, the data makes it way to the reporting database. This allows you to create some very useful reports on deliverables and dependencies. To get started with reporting, you may want to read through this post:

http://blogs.msdn.com/project_programmability/Default.aspx?p=2

In this post, I am only going to provide some background information on Deliverables and a couple of queries to get you started with creating your own reports. To begin with, these are the views and tables that are most commonly used for Deliverable reports:

There are a set of common queries that user tend to want when creating a report for deliverables. This first query is a simple list of all the deliverables and what project they are associated with:

SELECT 
	ProjectName As 'Project Name', 
	Title As 'Deliverable', 
	StartDate As 'Start Date', 
	FinishDate As ' Finish Date'
  FROM 
	MSP_WssDeliverableToProjectLinks_UserView 
	Inner Join MSP_EpmProject_UserView 
      	On 	MSP_WssDeliverableToProjectLinks_UserView.ProjectUID =
 		MSP_EpmProject_UserView.ProjectUID

The following query lists all the projects that have taken dependencies on a deliverable for given project. For the query to work, you need to set ProjectSelect.

SELECT     
	DeliverableProj.ProjectName AS SelectedProject,
 	DependancyProj.ProjectName AS DependentProject,
 	DeliverableLinks.Title, 
         DeliverableLinks.StartDate, 
	DeliverableLinks.FinishDate
FROM         
	MSP_EpmProject_UserView AS DeliverableProj 
	INNER JOIN	MSP_WssListItemAssociation 
	ON DeliverableProj.ProjectUID =
		MSP_WssListItemAssociation.ProjectUID 
	INNER JOIN MSP_EpmProject_UserView AS DependancyProj 
	ON MSP_WssListItemAssociation.RelatedProjectUID =
 		DependancyProj.ProjectUID 
	INNER JOIN MSP_WssDeliverable AS DeliverableLinks 
	ON MSP_WssListItemAssociation.ListItemUID = 
		DeliverableLinks.DeliverableUniqueID
WHERE     
	(MSP_WssListItemAssociation.ProjectUID 
                  <> MSP_WssListItemAssociation.RelatedProjectUID) 
AND (DeliverableProj.ProjectName = @ProjectSelect)

This last query lists all the projects that a given project is dependent on. Again, you need to set ProjectSelect for the query to work.

SELECT     
	DependancyProj.ProjectName AS SelectedProject,
 	DeliverableProj.ProjectName, 
	DeliverableLinks.Title, 
	DeliverableLinks.StartDate, 
	DeliverableLinks.FinishDate
FROM         
	MSP_WssListItemAssociation 
	INNER JOIN MSP_EpmProject_UserView AS DependancyProj 
	ON MSP_WssListItemAssociation.RelatedProjectUID =
		DependancyProj.ProjectUID 
	INNER JOIN MSP_EpmProject_UserView AS DeliverableProj 
	ON MSP_WssListItemAssociation.ProjectUID =
		DeliverableProj.ProjectUID 
	INNER JOIN MSP_WssDeliverable AS DeliverableLinks 
	ON MSP_WssListItemAssociation.ListItemUID =
		DeliverableLinks.DeliverableUniqueID
WHERE
	(MSP_WssListItemAssociation.RelatedProjectUID 
		<> MSP_WssListItemAssociation.ProjectUID) 
AND (DependancyProj.ProjectName = @ProjectSelect)

To take a look at the last two queries in real reports, check out the Project Give and Get Reports in the Report Pack:

http://blogs.msdn.com/project/archive/2007/01/30/sql-server-reporting-services-report-pack-for-project-server-2007.aspx

This should be a good start with creating Deliverable reports. If you come up with some interesting queries for creating Deliverable reports, please share them by posting them as comments!

Chris Boyd

Tags: Project Server Reporting Deliverables