当前位置: 动力学知识库 > 问答 > 编程问答 >

I can run the following query in SSRS 2008 Query Designer, but the report will not run

问题描述:

This is the query behind the report. Notice it includes the use of a common table expression (CTE).

This code will run in query designer and in Sql Server Management Studio, however will genorate an incorrect syntax error in the IDE or when deployed.

-- Project System Health Report V1.0

-- Extracts a series of data points from the Draft & Reporting databases to qualify the data in a Project system

--

-- Customization

-- 1. If running against Project Server 14 then search & fix "**P14**" code

--

-- Privacy/Information Protection Notes

-- 1. Report contains "Proj.PROJ_NAME" - please replace with N''*** INFORMATION REDACTED ***'' to remove this information

-- 2. Report contains data that can be used to quantify the amount of project work in the org, please review this for sensitivity prior to sending the data to Microsoft

--

-- Changelog

-- 01/05/2009 pmc Prepared for Customer Release, some perf tuning

-- 01/05/2009 pmc Changes made for Project Server 14 Alpha

--

Declare @sqlquery1 nvarchar(max)

Declare @DraftDB nvarchar(100)

Declare @ReportingDB nvarchar(100)

-- Set DB variable to name of EPM Draft DB.

-- Set DB variable to name of EPM Reporting DB.

Set @DraftDB = N'PWA_Draft_96D2C4A4_1D9F_4C80_B3A3_82098CC6C6E5'

Set @ReportingDB = N'PWA_Reporting_96D2C4A4_1D9F_4C80_B3A3_82098CC6C6E5'

set @sqlquery1 = N'use [' + @DraftDB + N'];

-- Common Table Expression to Recursively Parse Project Hierarchy

WITH ProjHierarchy

AS (SELECT Proj.PARENT_PROJ_UID, 1 AS DEPTH

FROM dbo.MSP_PROJ_HIERARCHIES AS Proj

UNION ALL

SELECT Proj.PARENT_PROJ_UID, Hier.DEPTH+1

FROM dbo.MSP_PROJ_HIERARCHIES AS Proj

INNER JOIN ProjHierarchy AS Hier

ON Proj.CHILD_PROJ_UID = Hier.PARENT_PROJ_UID)

SELECT Proj.PROJ_UID

,Proj.PROJ_NAME AS N''Project Name'' -- If sensitive uncomment below and comment this line

--, N''*** INFORMATION REDACTED ***'' AS N''Project Name''

,CONVERT(NVARCHAR(23), Proj.PROJ_INFO_START_DATE,20) AS N''Project Start Date''

,CONVERT(NVARCHAR(23), Proj.PROJ_INFO_FINISH_DATE,20) AS N''Project Finish Date''

,DATEDIFF(mm, Proj.PROJ_INFO_START_DATE,Proj.PROJ_INFO_FINISH_DATE) AS N''Project Length (Months)''

,CONVERT(NVARCHAR(23), Proj.CREATED_DATE,20) AS N''Project Created''

,CONVERT(NVARCHAR(23), Proj.WPROJ_LAST_PUB,20) AS N''Project Published''

,CONVERT(NVARCHAR(23), Proj.MOD_DATE,20) AS N''Project Modified''

,CASE ISNULL(Proj.WPROJ_STS_SUBWEB_NAME,N'''') WHEN N'''' THEN 0 ELSE 1 END AS N''Workspace''

,CASE ISNULL(Proj.PROJ_SESSION_UID,N''00000000-0000-0000-0000-000000000000'')

WHEN N''00000000-0000-0000-0000-000000000000''

THEN 0 ELSE DATEDIFF(d,Proj.PROJ_CHECKOUTDATE,getdate()) END AS N''Checked Out (Days)''

,CAST(Task.TASK_ACT_WORK / 60000 AS DECIMAL(10,2)) AS N''Project Total Actual Work (Hrs)''

,CAST(Task.TASK_REM_WORK / 60000 AS DECIMAL(10,2)) AS N''Project Total Remaining Work (Hrs)''

,ISNULL(ProjCF.[Number of Project Custom Field Values],0) AS N''Number of Project Custom Field Values''

,ISNULL(ProjLCF.[Number of Local Custom Field Definitions],0) AS N''Number of Local Custom Field Definitions''

,ISNULL(HierSummaryData.[Number of SubProjects],0) AS N''Number of SubProjects''

,ISNULL(HierSummaryData.[SubProject Depth],0) AS N''SubProject Depth''

,ISNULL(LeafTaskData.[Leaf Task Count],0) AS N''Leaf Task Count''

,ISNULL(LeafTaskData.[Max Leaf Calendar Duration (Days)], 0) AS N''Max Leaf Task Calendar Duration (Days)''

,ISNULL(LeafTaskData.[Average Leaf Calendar Duration (Days)], 0) AS N''Average Leaf Task Calendar Duration (Days)''

,ISNULL(LeafTaskData.[Total Leaf Task Ignoring ResCalendar], 0) AS N''Total Leaf Task Ignoring ResCalendar''

,ISNULL(AllTaskData.[Task Count],0) AS N''Total Task Count''

,ISNULL(RecentTasks.[Recent Changes],0) AS N''Total Recent Changed''

,ISNULL(LinkData.[Total Number of Links],0) AS N''Total Number of Links''

,ISNULL(LinkData.[Cross Project Links],0) AS N''Cross Project Links''

,ISNULL(TaskCFAggregated.[Max Task Custom Fields],0) AS N''Max Task Custom Fields''

,ISNULL(TaskCFAggregated.[Average Task Custom Fields],0) AS N''Average Task Custom Fields''

,ISNULL(TaskCFAggregated.[Total Task Custom Fields],0) AS N''Total Task Custom Fields''

,ISNULL(TaskCFAggregated.[Number of Tasks with Custom Fields],0) AS N''Number of Tasks with Custom Fields''

,ISNULL(AssnDataAggregated.[Average Resources on Assigned Tasks],0) AS N''Average Resources on Assigned Tasks''

,ISNULL(AssnDataAggregated.[Total Number of Assignments],0) AS N''Total Number of Assignments''

,ISNULL(AssnDataAggregated.[Number of Tasks with Assignments],0) AS N''Number of Tasks with Assignments''

,ISNULL(RealData.[Actual Count],0) AS N''Actual Count in AssnByDay Table''

,ISNULL(AssnCFAggregated.[Max Assn Custom Fields],0) AS N''Max Assn Custom Fields''

,ISNULL(AssnCFAggregated.[Average Assn Custom Fields],0) AS N''Average Assn Custom Fields''

,ISNULL(AssnCFAggregated.[Total Assn Custom Fields],0) AS N''Total Assn Custom Fields''

,ISNULL(AssnCFAggregated.[Number of Assn with Custom Fields],0) AS N''Number of Assn with Custom Fields''

,ISNULL(TaskBaseAggregated.[Number of Baselines],0) AS N''Number of Baselines''

,ISNULL(TaskBaseAggregated.[Total Task Baseline Rows],0) AS N''Total Task Baseline Rows''

,ISNULL(AssnBaseAggregated.[Total Assn Baseline Rows],0) AS N''Total Assn Baseline Rows''

,ISNULL(ResBaseAggregated.[Total Resource Baseline Rows],0) AS N''Total Resource Baseline Rows''

,ISNULL(Resources.[Total Resources],0) AS N''Project Team (All)''

,ISNULL(Resources.[Enterprise Resources],0) AS N''Project Team (Enterprise)''

,ISNULL(ActiveResAggregated.[Active Resources],0) AS N''Active Resources''

,getdate() as [Date/Time of Recording]

FROM dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)

-- Project Summary Task

INNER JOIN dbo.MSP_TASKS AS Task WITH(NOLOCK)

ON (Proj.PROJ_UID = Task.PROJ_UID AND Task.TASK_OPTINDX = 1) -- Use the Project Summary Task for rollup values

-- Get the rowcount from our (usually) largest table

LEFT OUTER JOIN (SELECT Assn.ProjectUID as N''PROJ_UID''

,COUNT(*) + 1 AS N''Actual Count''

FROM [' + @ReportingDB + ']..MSP_EpmAssignmentByDay AS Assn

GROUP BY Assn.ProjectUID) AS RealData

ON (Proj.PROJ_UID = RealData.PROJ_UID)

-- Project Custom Field Data

LEFT OUTER JOIN (SELECT pcf.PROJ_UID

,COUNT(*) AS N''Number of Project Custom Field Values''

FROM dbo.MSP_PROJ_CUSTOM_FIELD_VALUES AS pcf WITH(NOLOCK)

GROUP BY pcf.PROJ_UID) AS ProjCF

ON (Proj.PROJ_UID = ProjCF.PROJ_UID)

-- Project Local Custom Fields

LEFT OUTER JOIN (SELECT plcf.PROJ_UID

,COUNT(*) AS N''Number of Local Custom Field Definitions''

FROM dbo.MSP_PROJECT_CUSTOM_FIELDS AS plcf WITH(NOLOCK)

GROUP BY plcf.PROJ_UID) AS ProjLCF

ON (Proj.PROJ_UID = ProjLCF.PROJ_UID)'

set @sqlquery1 = @sqlquery1 + '

-- Project Hierarchy Below each Project

LEFT OUTER JOIN (SELECT HierData.PROJ_UID

,COUNT(HierData.DEPTH) AS N''Number of SubProjects''

,MAX(HierData.DEPTH) AS N''SubProject Depth''

FROM (SELECT Proj.PROJ_UID

,Hier.DEPTH

FROM ProjHierarchy as Hier

INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)

ON Hier.PARENT_PROJ_UID = Proj.PROJ_UID) AS HierData

GROUP BY HierData.PROJ_UID) AS HierSummaryData

ON (Proj.PROJ_UID = HierSummaryData.PROJ_UID)

-- Project resource data

LEFT OUTER JOIN (SELECT Pres.PROJ_UID

,COUNT(*) AS N''Total Resources''

,SUM(CAST(Pres.RES_IS_ENTERPRISE_RESOURCE AS INT)) AS N''Enterprise Resources''

,SUM(CAST(Pres.RES_BOOKING_TYPE AS INT)/2) AS N''Proposed Resources''

FROM dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)

WHERE Pres.RES_ID > 0 -- Ignore internal resources

GROUP BY Pres.PROJ_UID) AS Resources

ON (Proj.PROJ_UID = Resources.PROJ_UID)

-- Resources with Assignments

LEFT OUTER JOIN (SELECT ActiveRes.PROJ_UID

,COUNT(*) AS N''Active Resources''

FROM (SELECT Assn.PROJ_UID

,Assn.RES_UID

FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)

INNER JOIN dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)

ON (Assn.PROJ_UID = Pres.PROJ_UID AND Assn.RES_UID = Pres.RES_UID)

WHERE Pres.RES_ID > 0 -- Ignore internal resources

GROUP BY Assn.PROJ_UID, Assn.RES_UID) AS ActiveRes

GROUP BY ActiveRes.PROJ_UID) AS ActiveResAggregated

ON (Proj.PROJ_UID = ActiveResAggregated.PROJ_UID)

-- Leaf task data

LEFT OUTER JOIN (SELECT Task.PROJ_UID

,COUNT(*) AS N''Leaf Task Count''

-- Implementation Note - Use _SCHED_ below for P14, Ignores User Scheduled dates

,MAX(DATEDIFF(d,Task.TASK_START_DATE, Task.TASK_FINISH_DATE)) AS N''Max Leaf Calendar Duration (Days)''

,AVG(DATEDIFF(d,Task.TASK_START_DATE, Task.TASK_FINISH_DATE)) AS N''Average Leaf Calendar Duration (Days)''

--**P14**: ,MAX(DATEDIFF(d,Task.TASK_SCHED_START, Task.TASK_SCHED_FINISH)) AS N''Max Calendar Duration (Days)''

--**P14**: ,AVG(DATEDIFF(d,Task.TASK_SCHED_START, Task.TASK_SCHED_FINISH)) AS N''Average Calendar Duration (Days)''

,SUM(CAST(TASK_IGNORES_RES_CAL AS INT)) AS N''Total Leaf Task Ignoring ResCalendar''

FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)

WHERE Task.TASK_IS_SUMMARY = 0x0 -- Ignore summary tasks

AND Task.TASK_IS_MILESTONE = 0x0 -- Ignore milestones

AND Task.TASK_IS_SUBPROJ = 0x0 -- Ignore subprojects

GROUP BY Task.PROJ_UID) AS LeafTaskData

ON (Proj.PROJ_UID = LeafTaskData.PROJ_UID)

-- All visible tasks in the project

LEFT OUTER JOIN (SELECT Task.PROJ_UID

,COUNT(*) AS N''Task Count''

FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)

WHERE Task.TASK_OPTINDX > 1

GROUP BY Task.PROJ_UID) AS AllTaskData

ON (Proj.PROJ_UID = AllTaskData.PROJ_UID)

-- Task data changed on Last Project saved

LEFT OUTER JOIN (SELECT Task.PROJ_UID

,COUNT(*) AS N''Recent Changes''

FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)

INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)

ON (Task.PROJ_UID = Proj.PROJ_UID AND Task.MOD_REV_COUNTER = Proj.MOD_REV_COUNTER)

WHERE Task.TASK_OPTINDX > 1

GROUP BY Task.PROJ_UID) AS RecentTasks

ON (Proj.PROJ_UID = RecentTasks.PROJ_UID)

-- Task Custom Field Data

LEFT OUTER JOIN (SELECT TaskCF.PROJ_UID

,MAX(TaskCF.[Task CF Count]) AS N''Max Task Custom Fields''

,AVG(TaskCF.[Task CF Count]) AS N''Average Task Custom Fields''

,SUM(TaskCF.[Task CF Count]) AS N''Total Task Custom Fields''

,COUNT(*) AS N''Number of Tasks with Custom Fields''

FROM (SELECT TCF.PROJ_UID

,TCF.TASK_UID

,COUNT(*) AS N''Task CF Count''

FROM dbo.MSP_TASK_CUSTOM_FIELD_VALUES AS TCF WITH(NOLOCK)

GROUP BY TCF.PROJ_UID, TCF.TASK_UID) AS TaskCF

GROUP BY TaskCF.PROJ_UID) AS TaskCFAggregated

ON (Proj.PROJ_UID = TaskCFAggregated.PROJ_UID)

-- Link Data

LEFT OUTER JOIN (SELECT Links.PROJ_UID

,COUNT(*) AS N''Total Number of Links''

,SUM(CAST(LINK_IS_CROSS_PROJ AS INT)) AS N''Cross Project Links''

FROM dbo.MSP_LINKS AS Links

GROUP BY Links.PROJ_UID) AS LinkData

ON (Proj.PROJ_UID = LinkData.PROJ_UID)

-- Task Baseline Data

LEFT OUTER JOIN (SELECT TaskBaseData.PROJ_UID

,COUNT(*) AS N''Number of Baselines''

,SUM(TaskbaseData.[Task Baseline Rows]) AS N''Total Task Baseline Rows''

FROM (SELECT TaskBase.PROJ_UID

,TaskBase.TB_BASE_NUM AS N''Baseline''

,COUNT(*) AS N''Task Baseline Rows''

FROM dbo.MSP_TASK_BASELINES AS TaskBase WITH(NOLOCK)

GROUP BY TaskBase.PROJ_UID, TaskBase.TB_BASE_NUM) AS TaskBaseData

GROUP BY TaskBaseData.PROJ_UID) AS TaskBaseAggregated

ON (Proj.PROJ_UID = TaskBaseAggregated.PROJ_UID)

-- Assignment Baseline Data

LEFT OUTER JOIN (SELECT AssnBaseData.PROJ_UID

,COUNT(*) AS N''Total Assn Baseline Rows''

FROM dbo.MSP_ASSIGNMENT_BASELINES AS AssnBaseData WITH(NOLOCK)

GROUP BY AssnBaseData.PROJ_UID) AS AssnBaseAggregated

ON (Proj.PROJ_UID = AssnBaseAggregated.PROJ_UID)

-- Resource Baseline Data

LEFT OUTER JOIN (SELECT ResBaseData.PROJ_UID

,COUNT(*) AS N''Total Resource Baseline Rows''

FROM dbo.MSP_PROJECT_RESOURCE_BASELINES AS ResBaseData WITH(NOLOCK)

GROUP BY ResBaseData.PROJ_UID) AS ResBaseAggregated

ON (Proj.PROJ_UID = ResBaseAggregated.PROJ_UID)

-- Assignment Data Summary

LEFT OUTER JOIN (SELECT AssnTaskData.PROJ_UID

,AVG(AssnTaskData.[Assignment Count]) AS N''Average Resources on Assigned Tasks''

,SUM(AssnTaskData.[Assignment Count]) AS N''Total Number of Assignments''

,COUNT(*) AS N''Number of Tasks with Assignments''

FROM (SELECT Assn.PROJ_UID

,Assn.TASK_UID

,COUNT(*) AS N''Assignment Count''

FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)

GROUP BY Assn.PROJ_UID, Assn.TASK_UID) AS AssnTaskData

GROUP BY AssnTaskData.PROJ_UID) AS AssnDataAggregated

ON (Proj.PROJ_UID = AssnDataAggregated.PROJ_UID)

-- Assignment Custom Field Data

LEFT OUTER JOIN (SELECT AssnCF.PROJ_UID

,MAX(AssnCF.[Assn CF Count]) AS N''Max Assn Custom Fields''

,AVG(AssnCF.[Assn CF Count]) AS N''Average Assn Custom Fields''

,SUM(AssnCF.[Assn CF Count]) AS N''Total Assn Custom Fields''

,COUNT(*) AS N''Number of Assn with Custom Fields''

FROM (SELECT ACF.PROJ_UID

,ACF.ASSN_UID

,COUNT(*) AS N''Assn CF Count''

FROM dbo.MSP_ASSN_CUSTOM_FIELD_VALUES AS ACF WITH(NOLOCK)

GROUP BY ACF.PROJ_UID, ACF.ASSN_UID) AS AssnCF

GROUP BY AssnCF.PROJ_UID) AS AssnCFAggregated

ON (Proj.PROJ_UID = AssnCFAggregated.PROJ_UID);'

exec (@sqlquery1)

网友答案:

All I can suggest you do is trim your query down to nothing, then add elements until you find the offending bit of code.

网友答案:

I was able to get the query to work in both query designer, and the report, by not using the EXEC() command. I also changed the ‘’ in the query.

-- Project System Health Report V1.0
-- Extracts a series of data points from the Archive & Reporting databases to qualify the data in a Project system
--
-- Customization
--   1. If running against Project Server 14 then search & fix "**P14**" code
--
-- Privacy/Information Protection Notes
--   1. Report contains "Proj.PROJ_NAME" - please replace with N''*** INFORMATION REDACTED ***'' to remove this information
--   2. Report contains data that can be used to quantify the amount of project work in the org, please review this for sensitivity prior to sending the data to Microsoft
-- 
-- Changelog
-- 01/05/2009 pmc Prepared for Customer Release, some perf tuning
-- 01/05/2009 pmc Changes made for Project Server 14 Alpha 
--

-- Common Table Expression to Recursively Parse Project Hierarchy
-- Declare @ReportingDB nvarchar(100)
-- Set @ReportingDB = 'PWA_Reporting_96D2C4A4_1D9F_4C80_B3A3_82098CC6C6E5'

;WITH ProjHierarchy
AS (SELECT Proj.PARENT_PROJ_UID, 1 AS DEPTH
    FROM dbo.MSP_PROJ_HIERARCHIES AS Proj
    UNION ALL
    SELECT Proj.PARENT_PROJ_UID, Hier.DEPTH+1
    FROM dbo.MSP_PROJ_HIERARCHIES AS Proj
    INNER JOIN ProjHierarchy AS Hier
    ON Proj.CHILD_PROJ_UID = Hier.PARENT_PROJ_UID) 
SELECT Proj.PROJ_UID 
       ,Proj.PROJ_NAME AS 'Project Name' -- If sensitive uncomment below and comment this line
       --, N''*** INFORMATION REDACTED ***'' AS N''Project Name''
       ,CONVERT(NVARCHAR(23), Proj.PROJ_INFO_START_DATE,20) AS 'Project Start Date'
       ,CONVERT(NVARCHAR(23), Proj.PROJ_INFO_FINISH_DATE,20) AS 'Project Finish Date'
       ,DATEDIFF(mm, Proj.PROJ_INFO_START_DATE,Proj.PROJ_INFO_FINISH_DATE) AS 'Project Length (Months)'
       ,CONVERT(NVARCHAR(23), Proj.CREATED_DATE,20) AS 'Project Created'
       ,CONVERT(NVARCHAR(23), Proj.WPROJ_LAST_PUB,20) AS 'Project Published'
       ,CONVERT(NVARCHAR(23), Proj.MOD_DATE,20) AS 'Project Modified'
       ,CASE ISNULL(Proj.WPROJ_STS_SUBWEB_NAME,'') WHEN '' THEN 0 ELSE 1 END AS 'Workspace'
       ,CASE ISNULL(Proj.PROJ_SESSION_UID,'00000000-0000-0000-0000-000000000000') 
        WHEN '00000000-0000-0000-0000-000000000000' 
        THEN 0 ELSE DATEDIFF(d,Proj.PROJ_CHECKOUTDATE,getdate()) END AS 'Checked Out (Days)'
       ,CAST(Task.TASK_ACT_WORK / 60000 AS DECIMAL(10,2)) AS 'Project Total Actual Work (Hrs)'
       ,CAST(Task.TASK_REM_WORK / 60000 AS DECIMAL(10,2)) AS 'Project Total Remaining Work (Hrs)'
       ,ISNULL(ProjCF.[Number of Project Custom Field Values],0) AS 'Number of Project Custom Field Values'
       ,ISNULL(ProjLCF.[Number of Local Custom Field Definitions],0) AS 'Number of Local Custom Field Definitions'
       ,ISNULL(HierSummaryData.[Number of SubProjects],0) AS 'Number of SubProjects'
       ,ISNULL(HierSummaryData.[SubProject Depth],0) AS 'SubProject Depth'
       ,ISNULL(LeafTaskData.[Leaf Task Count],0) AS 'Leaf Task Count'
       ,ISNULL(LeafTaskData.[Max Leaf Calendar Duration (Days)], 0) AS 'Max Leaf Task Calendar Duration (Days)'
       ,ISNULL(LeafTaskData.[Average Leaf Calendar Duration (Days)], 0) AS 'Average Leaf Task Calendar Duration (Days)'
       ,ISNULL(LeafTaskData.[Total Leaf Task Ignoring ResCalendar], 0) AS 'Total Leaf Task Ignoring ResCalendar'
       ,ISNULL(AllTaskData.[Task Count],0) AS 'Total Task Count'
       ,ISNULL(RecentTasks.[Recent Changes],0) AS 'Total Recent Changed'
       ,ISNULL(LinkData.[Total Number of Links],0) AS 'Total Number of Links'
       ,ISNULL(LinkData.[Cross Project Links],0) AS 'Cross Project Links'
       ,ISNULL(TaskCFAggregated.[Max Task Custom Fields],0) AS 'Max Task Custom Fields'
       ,ISNULL(TaskCFAggregated.[Average Task Custom Fields],0) AS 'Average Task Custom Fields'
       ,ISNULL(TaskCFAggregated.[Total Task Custom Fields],0) AS 'Total Task Custom Fields'
       ,ISNULL(TaskCFAggregated.[Number of Tasks with Custom Fields],0) AS 'Number of Tasks with Custom Fields'
       ,ISNULL(AssnDataAggregated.[Average Resources on Assigned Tasks],0) AS 'Average Resources on Assigned Tasks'
       ,ISNULL(AssnDataAggregated.[Total Number of Assignments],0) AS 'Total Number of Assignments'
       ,ISNULL(AssnDataAggregated.[Number of Tasks with Assignments],0) AS 'Number of Tasks with Assignments'
       ,ISNULL(RealData.[Actual Count],0) AS 'Actual Count in AssnByDay Table'
       ,ISNULL(AssnCFAggregated.[Max Assn Custom Fields],0) AS 'Max Assn Custom Fields'
       ,ISNULL(AssnCFAggregated.[Average Assn Custom Fields],0) AS 'Average Assn Custom Fields'
       ,ISNULL(AssnCFAggregated.[Total Assn Custom Fields],0) AS 'Total Assn Custom Fields'
       ,ISNULL(AssnCFAggregated.[Number of Assn with Custom Fields],0) AS 'Number of Assn with Custom Fields' 
       ,ISNULL(TaskBaseAggregated.[Number of Baselines],0) AS 'Number of Baselines'
       ,ISNULL(TaskBaseAggregated.[Total Task Baseline Rows],0) AS 'Total Task Baseline Rows'
       ,ISNULL(AssnBaseAggregated.[Total Assn Baseline Rows],0) AS 'Total Assn Baseline Rows' 
       ,ISNULL(ResBaseAggregated.[Total Resource Baseline Rows],0) AS 'Total Resource Baseline Rows'
       ,ISNULL(Resources.[Total Resources],0) AS 'Project Team (All)'
       ,ISNULL(Resources.[Enterprise Resources],0) AS 'Project Team (Enterprise)'
       ,ISNULL(ActiveResAggregated.[Active Resources],0) AS 'Active Resources'
       ,getdate() as [Date/Time of Recording]
FROM dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
-- Project Summary Task
INNER JOIN dbo.MSP_TASKS AS Task WITH(NOLOCK)
ON (Proj.PROJ_UID = Task.PROJ_UID AND Task.TASK_OPTINDX = 1) -- Use the Project Summary Task for rollup values
-- Get the rowcount from our (usually) largest table 
LEFT OUTER JOIN (SELECT Assn.ProjectUID as 'PROJ_UID'
                       ,COUNT(*) + 1 AS 'Actual Count'
                 FROM PWA_Reporting_96D2C4A4_1D9F_4C80_B3A3_82098CC6C6E5..MSP_EpmAssignmentByDay AS Assn
                 GROUP BY Assn.ProjectUID) AS RealData
ON (Proj.PROJ_UID = RealData.PROJ_UID)
-- Project Custom Field Data
LEFT OUTER JOIN (SELECT pcf.PROJ_UID
                       ,COUNT(*) AS 'Number of Project Custom Field Values'
                 FROM dbo.MSP_PROJ_CUSTOM_FIELD_VALUES AS pcf WITH(NOLOCK)
                 GROUP BY pcf.PROJ_UID) AS ProjCF
ON (Proj.PROJ_UID = ProjCF.PROJ_UID)
-- Project Local Custom Fields
LEFT OUTER JOIN (SELECT plcf.PROJ_UID
                       ,COUNT(*) AS 'Number of Local Custom Field Definitions'
                 FROM dbo.MSP_PROJECT_CUSTOM_FIELDS AS plcf WITH(NOLOCK)
                 GROUP BY plcf.PROJ_UID) AS ProjLCF
ON (Proj.PROJ_UID = ProjLCF.PROJ_UID)

-- Project Hierarchy Below each Project
LEFT OUTER JOIN (SELECT HierData.PROJ_UID
                       ,COUNT(HierData.DEPTH) AS 'Number of SubProjects'
                       ,MAX(HierData.DEPTH) AS 'SubProject Depth'
                 FROM (SELECT Proj.PROJ_UID
                             ,Hier.DEPTH 
                       FROM ProjHierarchy as Hier
                       INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
                       ON Hier.PARENT_PROJ_UID = Proj.PROJ_UID) AS HierData
                 GROUP BY HierData.PROJ_UID) AS HierSummaryData
ON (Proj.PROJ_UID = HierSummaryData.PROJ_UID)
-- Project resource data
LEFT OUTER JOIN (SELECT Pres.PROJ_UID
                       ,COUNT(*) AS 'Total Resources'
                       ,SUM(CAST(Pres.RES_IS_ENTERPRISE_RESOURCE AS INT)) AS 'Enterprise Resources'
                       ,SUM(CAST(Pres.RES_BOOKING_TYPE AS INT)/2) AS 'Proposed Resources'
                 FROM dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)
                 WHERE Pres.RES_ID > 0 -- Ignore internal resources
                 GROUP BY Pres.PROJ_UID) AS Resources
ON (Proj.PROJ_UID = Resources.PROJ_UID)
 -- Resources with Assignments
 LEFT OUTER JOIN (SELECT ActiveRes.PROJ_UID
                        ,COUNT(*) AS 'Active Resources'
                  FROM (SELECT Assn.PROJ_UID
                              ,Assn.RES_UID
                        FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)
                        INNER JOIN dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)
                        ON (Assn.PROJ_UID = Pres.PROJ_UID AND Assn.RES_UID = Pres.RES_UID)
                        WHERE Pres.RES_ID > 0 -- Ignore internal resources
                        GROUP BY Assn.PROJ_UID, Assn.RES_UID) AS ActiveRes
                  GROUP BY ActiveRes.PROJ_UID) AS ActiveResAggregated
 ON (Proj.PROJ_UID = ActiveResAggregated.PROJ_UID)  
-- Leaf task data            
LEFT OUTER JOIN (SELECT Task.PROJ_UID
                   ,COUNT(*) AS 'Leaf Task Count'
                   -- Implementation Note - Use _SCHED_ below for P14, Ignores User Scheduled dates 
                   ,MAX(DATEDIFF(d,Task.TASK_START_DATE, Task.TASK_FINISH_DATE)) AS 'Max Leaf Calendar Duration (Days)'
                   ,AVG(DATEDIFF(d,Task.TASK_START_DATE, Task.TASK_FINISH_DATE)) AS 'Average Leaf Calendar Duration (Days)'
                   --**P14**: ,MAX(DATEDIFF(d,Task.TASK_SCHED_START, Task.TASK_SCHED_FINISH)) AS N''Max Calendar Duration (Days)''
                   --**P14**: ,AVG(DATEDIFF(d,Task.TASK_SCHED_START, Task.TASK_SCHED_FINISH)) AS N''Average Calendar Duration (Days)''
                   ,SUM(CAST(TASK_IGNORES_RES_CAL AS INT)) AS 'Total Leaf Task Ignoring ResCalendar'
                 FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
                 WHERE Task.TASK_IS_SUMMARY = 0x0 -- Ignore summary tasks
                 AND Task.TASK_IS_MILESTONE = 0x0 -- Ignore milestones
                 AND Task.TASK_IS_SUBPROJ = 0x0 -- Ignore subprojects
                 GROUP BY Task.PROJ_UID) AS LeafTaskData
ON (Proj.PROJ_UID = LeafTaskData.PROJ_UID)
-- All visible tasks in the project
LEFT OUTER JOIN (SELECT Task.PROJ_UID
                   ,COUNT(*) AS 'Task Count'
                 FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
                 WHERE Task.TASK_OPTINDX > 1
                 GROUP BY Task.PROJ_UID) AS AllTaskData
ON (Proj.PROJ_UID = AllTaskData.PROJ_UID)
-- Task data changed on Last Project saved
LEFT OUTER JOIN (SELECT Task.PROJ_UID
                       ,COUNT(*) AS N'Recent Changes'
                 FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
                 INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
                 ON (Task.PROJ_UID = Proj.PROJ_UID AND Task.MOD_REV_COUNTER = Proj.MOD_REV_COUNTER)
                 WHERE Task.TASK_OPTINDX > 1
                 GROUP BY Task.PROJ_UID) AS RecentTasks
ON (Proj.PROJ_UID = RecentTasks.PROJ_UID)
-- Task Custom Field Data
LEFT OUTER JOIN (SELECT TaskCF.PROJ_UID
                       ,MAX(TaskCF.[Task CF Count]) AS 'Max Task Custom Fields'
                       ,AVG(TaskCF.[Task CF Count]) AS 'Average Task Custom Fields'
                       ,SUM(TaskCF.[Task CF Count]) AS 'Total Task Custom Fields'
                       ,COUNT(*) AS 'Number of Tasks with Custom Fields'
                 FROM (SELECT TCF.PROJ_UID
                             ,TCF.TASK_UID
                             ,COUNT(*) AS 'Task CF Count'
                       FROM dbo.MSP_TASK_CUSTOM_FIELD_VALUES AS TCF WITH(NOLOCK)
                       GROUP BY TCF.PROJ_UID, TCF.TASK_UID) AS TaskCF
                 GROUP BY TaskCF.PROJ_UID) AS TaskCFAggregated
ON (Proj.PROJ_UID = TaskCFAggregated.PROJ_UID)
-- Link Data
LEFT OUTER JOIN (SELECT Links.PROJ_UID
                       ,COUNT(*) AS 'Total Number of Links'
                       ,SUM(CAST(LINK_IS_CROSS_PROJ AS INT)) AS 'Cross Project Links'
                 FROM dbo.MSP_LINKS AS Links
                 GROUP BY Links.PROJ_UID) AS LinkData
ON (Proj.PROJ_UID = LinkData.PROJ_UID)
-- Task Baseline Data
LEFT OUTER JOIN (SELECT TaskBaseData.PROJ_UID
                       ,COUNT(*) AS 'Number of Baselines'
                       ,SUM(TaskbaseData.[Task Baseline Rows]) AS 'Total Task Baseline Rows'
                 FROM (SELECT TaskBase.PROJ_UID
                             ,TaskBase.TB_BASE_NUM AS 'Baseline'
                             ,COUNT(*) AS 'Task Baseline Rows'
                       FROM dbo.MSP_TASK_BASELINES AS TaskBase WITH(NOLOCK)
                       GROUP BY TaskBase.PROJ_UID, TaskBase.TB_BASE_NUM) AS TaskBaseData
                 GROUP BY TaskBaseData.PROJ_UID) AS TaskBaseAggregated
ON (Proj.PROJ_UID = TaskBaseAggregated.PROJ_UID)
-- Assignment Baseline Data
LEFT OUTER JOIN (SELECT AssnBaseData.PROJ_UID
                       ,COUNT(*) AS 'Total Assn Baseline Rows'
                 FROM dbo.MSP_ASSIGNMENT_BASELINES AS AssnBaseData WITH(NOLOCK)
                 GROUP BY AssnBaseData.PROJ_UID) AS AssnBaseAggregated
ON (Proj.PROJ_UID = AssnBaseAggregated.PROJ_UID)
-- Resource Baseline Data
LEFT OUTER JOIN (SELECT ResBaseData.PROJ_UID
                       ,COUNT(*) AS 'Total Resource Baseline Rows'
                 FROM dbo.MSP_PROJECT_RESOURCE_BASELINES AS ResBaseData WITH(NOLOCK)
                 GROUP BY ResBaseData.PROJ_UID) AS ResBaseAggregated
ON (Proj.PROJ_UID = ResBaseAggregated.PROJ_UID) 
-- Assignment Data Summary
LEFT OUTER JOIN (SELECT AssnTaskData.PROJ_UID
                       ,AVG(AssnTaskData.[Assignment Count]) AS 'Average Resources on Assigned Tasks'
                       ,SUM(AssnTaskData.[Assignment Count]) AS 'Total Number of Assignments'
                       ,COUNT(*) AS 'Number of Tasks with Assignments'
                 FROM (SELECT Assn.PROJ_UID
                             ,Assn.TASK_UID
                             ,COUNT(*) AS 'Assignment Count'
                       FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)
                       GROUP BY Assn.PROJ_UID, Assn.TASK_UID) AS AssnTaskData
                 GROUP BY AssnTaskData.PROJ_UID) AS AssnDataAggregated    
 ON (Proj.PROJ_UID = AssnDataAggregated.PROJ_UID)
-- Assignment Custom Field Data
LEFT OUTER JOIN (SELECT AssnCF.PROJ_UID
                       ,MAX(AssnCF.[Assn CF Count]) AS 'Max Assn Custom Fields'
                       ,AVG(AssnCF.[Assn CF Count]) AS 'Average Assn Custom Fields'
                       ,SUM(AssnCF.[Assn CF Count]) AS 'Total Assn Custom Fields'
                       ,COUNT(*) AS 'Number of Assn with Custom Fields'
                 FROM (SELECT ACF.PROJ_UID
                             ,ACF.ASSN_UID
                             ,COUNT(*) AS 'Assn CF Count'
                       FROM dbo.MSP_ASSN_CUSTOM_FIELD_VALUES AS ACF WITH(NOLOCK)
                       GROUP BY ACF.PROJ_UID, ACF.ASSN_UID) AS AssnCF
                 GROUP BY AssnCF.PROJ_UID) AS AssnCFAggregated
ON (Proj.PROJ_UID = AssnCFAggregated.PROJ_UID);
分享给朋友:
您可能感兴趣的文章:
随机阅读: