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:

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):

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