none
Added new field to SharePoint Issues Intake and want to report on it but can't find it in the Reporting DB RRS feed

  • Question

  • Hi,

    I have a need to add some additional fields to the Issue and Risk Intake Lists.  I can do this and then update my Issue for the Project including updating that new field.  The only problem is now where do I find these new fields.  They are not loaded into the MSP_WssIssue table.  Does anyone know what table the new field would be located in?  Is it in the Reporting DB or in the Content DB?

    Thanks for your help.

     


    Donald R. Landry
    Thursday, May 26, 2011 5:00 PM

Answers

  • You can get at it in the content db, it's just not officially supported.  I used the following blog of Christophe's to help me figure out how to query the content database directly for Issues, since the issues don't get updated to the reporting database until the project is republished (*sigh*). 

    http://blogs.msdn.com/b/chrisfie/archive/2008/12/19/how-to-report-project-risks-at-a-program-level.aspx

    Here's the query I wrote:

    (ETA:  This is for Project Server 2007 with WSS)

    SELECT

     Webs.Title AS ProjectName
    ,R.ProjectOwnerName AS ProjectManager
    ,R.Client
    ,R.[Primary Project Code] /*enterprise custom fields*/
    ,R.[Project Stage]
    ,Issue.tp_ID AS IssueID
    ,Issue.nvarchar1 AS Title
    ,Assigned.tp_Title AS AssignedToResource
    ,Issue.datetime1 AS DueDate
    ,Issue.nvarchar4 AS Category
    ,Issue.nvarchar3 AS Status
    ,Issue.nvarchar5 AS Priority
    ,Issue.ntext3 AS Resolution
    ,Issue.ntext2 AS Discussion
    ,Issue.tp_Created AS CreatedDate
    ,Issue.tp_Modified AS ModifiedDate

    FROM   WSS_Content.dbo.UserData Issue, WSS_Content.dbo.Lists,
    WSS_Content.dbo.Webs, WSS_Content.dbo.Webs ParentWeb, WSS_Content.dbo.UserInfo Assigned,
    Published.dbo.MSP_Projects P, Reporting.dbo.MSP_EpmProject_UserView R


    WHERE Issue.tp_ContentType = 'Project Workspace Issue' AND
    Issue.tp_ListId = Lists.tp_ID AND
    Webs.ID = Lists.tp_WebID AND
    Webs.ParentWebID = ParentWeb.Id AND
    ParentWeb.FullURL = 'PWA' AND   /* Ensures that we get everything and only those things under the PWA root. */
    /* Really need a left join to the user assigned info.  However, we can rely on the fact that the assigned user is a required field for us. */
    Issue.int2 = Assigned.tp_ID AND  /*Assigned to field*/
    P.WPROJ_ISSUE_LIST_NAME = Issue.tp_ListId AND
    R.ProjectUID = P.PROJ_UID AND
    Assigned.tp_SiteID = Webs.SiteID AND
    (R.ProjectOwnerName IN (@ProjectManager) OR Assigned.tp_Title IN (@ProjectManager)) AND /*filter by PM*/
    ((Issue.nvarchar3 NOT LIKE '%Closed%') OR (Issue.nvarchar3 LIKE '%Closed%' AND Issue.tp_Modified >=@ClosedDate))


    Terrie T - MCTS - PMP - MBA****PMO & Project Server Admin
    Friday, May 27, 2011 1:27 PM

All replies

  • Hi Donald,

    Unfortunately, any new risk or issue columns you add will not be pulled into the reporting db and will reside in the ContentDB housing your project workspaces.

    As direct contentdb sql queries are unsupported, your options are:

    • Write a web service that queries the ContentDB via the SharePoint API and hook this up to your reports;
    • Look at using SLAM (slam.codeplex.com) that allows you to create a pseudo data mart and report over it; or
    • Look at iPMO's Data Miner (http://www.ipmo.com.au/Products/iPMODataMiner2009forSharePoint.aspx) which does a similar data mart approach

    Hope this helps,


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Thursday, May 26, 2011 7:25 PM
    Moderator
  • Thanks Alex,

    I've been doing excel services queries against the content DB trying to find the data.  Are you saying that the data is somehow encrypted in the content db?  Does the SharePoint SDK outline how to create and API to access the content db?  I'm pretty proficient with SQL so if I can find an easy way to pull it out of the content db that would be easier but if that isn't an option then I will head in the direction you outlined.

    Also are there any references on msdn that I could use?

     


    Donald R. Landry
    Thursday, May 26, 2011 10:50 PM
  • You can get at it in the content db, it's just not officially supported.  I used the following blog of Christophe's to help me figure out how to query the content database directly for Issues, since the issues don't get updated to the reporting database until the project is republished (*sigh*). 

    http://blogs.msdn.com/b/chrisfie/archive/2008/12/19/how-to-report-project-risks-at-a-program-level.aspx

    Here's the query I wrote:

    (ETA:  This is for Project Server 2007 with WSS)

    SELECT

     Webs.Title AS ProjectName
    ,R.ProjectOwnerName AS ProjectManager
    ,R.Client
    ,R.[Primary Project Code] /*enterprise custom fields*/
    ,R.[Project Stage]
    ,Issue.tp_ID AS IssueID
    ,Issue.nvarchar1 AS Title
    ,Assigned.tp_Title AS AssignedToResource
    ,Issue.datetime1 AS DueDate
    ,Issue.nvarchar4 AS Category
    ,Issue.nvarchar3 AS Status
    ,Issue.nvarchar5 AS Priority
    ,Issue.ntext3 AS Resolution
    ,Issue.ntext2 AS Discussion
    ,Issue.tp_Created AS CreatedDate
    ,Issue.tp_Modified AS ModifiedDate

    FROM   WSS_Content.dbo.UserData Issue, WSS_Content.dbo.Lists,
    WSS_Content.dbo.Webs, WSS_Content.dbo.Webs ParentWeb, WSS_Content.dbo.UserInfo Assigned,
    Published.dbo.MSP_Projects P, Reporting.dbo.MSP_EpmProject_UserView R


    WHERE Issue.tp_ContentType = 'Project Workspace Issue' AND
    Issue.tp_ListId = Lists.tp_ID AND
    Webs.ID = Lists.tp_WebID AND
    Webs.ParentWebID = ParentWeb.Id AND
    ParentWeb.FullURL = 'PWA' AND   /* Ensures that we get everything and only those things under the PWA root. */
    /* Really need a left join to the user assigned info.  However, we can rely on the fact that the assigned user is a required field for us. */
    Issue.int2 = Assigned.tp_ID AND  /*Assigned to field*/
    P.WPROJ_ISSUE_LIST_NAME = Issue.tp_ListId AND
    R.ProjectUID = P.PROJ_UID AND
    Assigned.tp_SiteID = Webs.SiteID AND
    (R.ProjectOwnerName IN (@ProjectManager) OR Assigned.tp_Title IN (@ProjectManager)) AND /*filter by PM*/
    ((Issue.nvarchar3 NOT LIKE '%Closed%') OR (Issue.nvarchar3 LIKE '%Closed%' AND Issue.tp_Modified >=@ClosedDate))


    Terrie T - MCTS - PMP - MBA****PMO & Project Server Admin
    Friday, May 27, 2011 1:27 PM
  • Terrie,

    Thank You!!  You Rock!!

    One question did you run this query in excel services and what was your connection string like to support accessing the three different DB's


    Donald R. Landry

    Forget my connection string question got it.
    Friday, May 27, 2011 1:41 PM