locked
Good Query Wont work in SSRS Dataset RRS feed

  • Question

  • Hi Folks,

    My query below executes OK in SSMS but wont work in a dataset - it gives the error: "Incorrect Syntax Near PROJ_UI". I wonder if anyone could give some advice?


    Thanks


    Paul


    -- Project System Health Report V1.0

     

    -- Extracts a series of data points from the Published & 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


     @PublishedDB nvarchar(100)

     

    Declare


     @ReportingDB nvarchar(100)

     

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

     

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

     

    Set


     @PublishedDB = N'ProjectServer2007_Litware_Published'

     

    Set


     @ReportingDB = N'ProjectServer2007_Litware_Reporting'

     

    set


     @sqlquery1 = N'use [' + @PublishedDB + 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)

     

     

     

    Tuesday, May 24, 2011 3:25 PM

Answers

  • Hello,

    Add your dynamic SQL Statement as a stored procedure to your database and in SSRS then just only execute the stored procedure.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Tuesday, May 24, 2011 4:43 PM