How to create a Milestone Report


While delivering Project Server training in Beijing, a customer asked me to produce a report that displayed Milestones dates by Projects.

The assumption is that all projects are using the same project template and thus there is a specific naming convention for all Milestones.

The hard part was creating the T-SQL query from the Reporting database. You basically have to de-normalize the data and combine two UserViews (MSP_EpmTask_UserView and MSP_EpmProject_UserView)as shown below:

SELECT P.ProjectName AS Project, P.ProjectAuthorName AS Author,
P.ProjectStartDate AS Start, P.ProjectFinishDate AS Finish,
MAX(CASE WHEN TaskName = 'M1' 
THEN TaskFinishDate ELSE NULL END) AS M1, 
MAX(CASE WHEN TaskName = 'M2' 
THEN TaskFinishDate ELSE NULL END) AS M2,
MAX(CASE WHEN TaskName = 'M3' 
THEN TaskFinishDate ELSE NULL END) AS M3
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
ON P.ProjectUID=T.ProjectUID
WHERE T.TaskIsMilestone=1
GROUP BY P.ProjectName, P.ProjectAuthorName, P.ProjectStartDate, P.ProjectFinishDate

For instance for Proj4 it contains the following data:

153 2008-03-10, 22_54_36

I then created a new view in the Reporting database called MilestoneView, then went into Excel 2007 added a data connection my Reporting database and used the MilestoneView created earlier. Results looks like this (note I added some conditional formatting to display indicators):

154 2008-03-10, 22_55_29

Going further you could leverage Excel Services to render this report in your SharePoint Server farm.

Comments (2)
  1. nedjohn51 says:

    Thanks for this post!  Based on it, I created a similar report for our organization that  is proving very helpful!  Is there a method to also include the resource(s) assigned to the milestone task.

    Your site is a tremendous resource.  Keep up the good work!

Comments are closed.

Skip to main content