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.