Thursday, March 12, 2009

Get project issues on EPM

This is the stored procedure I am using to get issues information on my EPM


CREATE PROCEDURE [dbo].[spLoadProjectIssues]
@ProjectName NVARCHAR(255) = NULL,
@FromDate DATETIME = NULL,
@ToDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT t1.Title, t1.AssignedToResource, t1.DueDate, t1.Status, t1.CreatedDate
FROM ProjectServer_Reporting..MSP_WssIssue t1
INNER JOIN MSP_PROJECTS t2 ON t1.ProjectUID = t2.PROJ_UID
WHERE t2.PROJ_NAME LIKE @ProjectName
AND (t1.CreatedDate BETWEEN @FromDate AND @ToDate)
END

Monday, March 2, 2009

Get project name and team members in EPM

SELECT t1.PROJ_UID
, t1.PROJ_NAME
, t3.RES_NAME
, CASE WHEN t1.WRES_UID = t3.RES_UID THEN 1 ELSE 0 END AS IS_PM
FROM MSP_PROJECTS t1
INNER JOIN MSP_PROJECT_RESOURCES t2 ON t1.PROJ_UID = t2.PROJ_UID
INNER JOIN MSP_RESOURCES t3 ON t2.RES_UID = t3.RES_UID
ORDER BY t1.PROJ_NAME