How to create a Milestone Report
11 March 2008While 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.
Originally by chrisfie from Christophe Fiessinger’s Blog on March 11, 2008, 1:04am
Popularity: 2% [?]
If you enjoyed this post, make sure you subscribe to my RSS feed!
Other posts like this one:
No comments yet