Creating Deliverable Reports

Creating Deliverable Reports

  • Comments 0

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:

MSP_EpmProject_UserView

This view shows all the projects. Commonly you will join the Project UID in this view with the Project UID or the Relationship UID from the other views. By doing this you can get information about the project the deliverable or dependency is associated with, such as the name of the project.
MSP_WssDeliverableToProjectLinks_UserView

This view lists all the published deliverables, not the dependencies. In this view you can get information such the UID for the project a deliverable is associated with and the start and finish date of a deliverable.
MSP_WssDeliverableToTaskLinks_UserView

This is the same at the MSP_WssDeliverableToProjectLinks_UserView except that is has additional fields for deliverables that are linked to tasks. This allows you to report on task details for the associated deliverable. For example, you could use the task information to write a report that shows all deliverables where the deliverable finish date is before the task finish date.
MSP_WssListItemAssociation

This view shows all the different associates with risks, issues and deliverables. Here you are going to want to look at the relationship type ID. The relationship type ID tells you if it is a deliverable or a dependency and if it is linked to a task or not. It is also where you can find if a dependency exists.
MSP_WssRelationshipType

This table lists the different types. These types refer to risks, issues and deliverables. For deliverables and dependencies, the following types are important:

Relationship Type ID

Description

11 This is a deliverable that is linked to a task.
12

This is a dependency on a deliverable that is linked to a task. 

13

This is a deliverable for a project. It is not linked to any task within the project.

14

This is a dependency on a deliverable for a project. It is not linked to any task within the project.

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:

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post