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:
https://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:
https://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:
|
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:
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