Saturday, February 16, 2013 8:22 AM
i have created a query in sql,it is displaying correct results,but it is not showing result according to the format which i need
as you can see for project name test_final 2,phase5 completed is in actual milestone as well as in planned milestone,but result is coming in different rows,i want the result to appear in same row,if anybody has any idea,plz let me know,any help will be greatly appreciated
i am also mentioning the query below which i have createdSELECT MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName AS Plannedmilestone, NUll AS Actualmilestone, NUll AS futuremilestone
FROM MSP_EpmTask_UserView INNER JOIN
MSP_EpmProject_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
WHERE (MONTH(MSP_EpmTask_UserView.TaskBaseline0StartDate) = '2') AND (YEAR(MSP_EpmTask_UserView.TaskBaseline0StartDate) = 2013) AND
(MSP_EpmTask_UserView.TaskIsMilestone = '1') AND (MSP_EpmProject_UserView.Area = 'Mumbai')
SELECT MSP_EpmProject_UserView_2.ProjectName, NUll AS Plannedmilestone, MSP_EpmTask_UserView_2.TaskName AS Actualmilestone, NUll AS futuremilestone
FROM MSP_EpmTask_UserView AS MSP_EpmTask_UserView_2 INNER JOIN
MSP_EpmProject_UserView AS MSP_EpmProject_UserView_2 ON MSP_EpmProject_UserView_2.ProjectUID = MSP_EpmTask_UserView_2.ProjectUID
WHERE (MONTH(MSP_EpmTask_UserView_2.TaskActualFinishDate) = '2') AND (YEAR(MSP_EpmTask_UserView_2.TaskActualFinishDate) = 2013) AND
(MSP_EpmTask_UserView_2.TaskIsMilestone = '1') AND (MSP_EpmProject_UserView_2.Area = 'Mumbai')
SELECT MSP_EpmProject_UserView_1.ProjectName, NUll AS Plannedmilestone, NUll AS Actualmilestone, MSP_EpmTask_UserView_1.TaskName AS FutureMilestone
FROM MSP_EpmTask_UserView AS MSP_EpmTask_UserView_1 INNER JOIN
MSP_EpmProject_UserView AS MSP_EpmProject_UserView_1 ON MSP_EpmProject_UserView_1.ProjectUID = MSP_EpmTask_UserView_1.ProjectUID
WHERE (MONTH(MSP_EpmTask_UserView_1.TaskStartDate) = '2' + 1) AND (YEAR(MSP_EpmTask_UserView_1.TaskStartDate) = 2013) AND
(MSP_EpmTask_UserView_1.TaskIsMilestone = '1') AND (MSP_EpmProject_UserView_1.Area = 'Mumbai') order by ProjectName
Saturday, February 16, 2013 2:50 PM
Well, You can create a stored procedure to get this query.
Then take the result into TempTable
Rephrase the result inorder to get the result as below.
test_final1 phase 3 completed NULL NULL test_final2 phase 5 completed phase 5 completed d
This could be acheived easily. Either with a while loop or a cursor.
Then have as the dataset to the report inorder to get the result straight.
This is one way of solving this issue.
Please Mark posts as answers or helpful so that others can more easily find the answers they seek.
- Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Monday, February 25, 2013 1:45 AM