none
Include Custom Column Values in Issues and Risks Report RRS feed

  • Question

  • This is a pretty simple question, though I have a feeling I won't like the answer.

    In Project Server/SharePoint 2010, is there any way to add custom site columns used for Issues and Risks into a report? (running a sql query in excel)

    I only see the default columns in the tables where all the issues and risks data is stored.  I've googled and searched and haven't gotten a difinitive answer.  We've always gotten this data before from a horrible macro that searched all the project sites issues and risks pages (via http) for the tables and values and it takes forever and isn't very acurate.  I love the Issues and Risks reports that come with 2010 but is there any way to alter them to include those custom colums, or is that data not stored anywhere in the ProjectServer_Reporting database?

    Thanks!

    Thursday, November 17, 2011 11:37 PM

Answers

  • Ellij,

    Hi again, I didnt read your question before very well, and I guess you are looking for:

     - Create a custom list in a Project Site

     - Create a custom column in that list

     - Retrieve that information

    The only approach I´ve found in DB architecture in PS2010 for that issue is the tables that are created in the Reporting database with information about Issues and Risks.

    So we have to turn back to PS2007 DB architechture to get what you are looking for and we have to query the WSS_Content database of Project Server, btw, when you identify that database you can make a query that look like this:


    Select [WSS_Content].[dbo].[AllWebs].Title as [Project Title],
             [WSS_Content].[dbo].[AllLists].tp_Title as [List Title],
             [WSS_Content].[dbo].[UserData].*
             From [WSS_Content].[dbo].[AllWebs] inner join [WSS_Content].[dbo].[AllLists] on
                  [WSS_Content].[dbo].[AllWebs].id =[WSS_Content].[dbo].[AllLists].tp_WebId
                  inner join  [WSS_Content].[dbo].[UserData] on
                  [WSS_Content].[dbo].[AllLists].[tp_ID] =[WSS_Content].[dbo].[UserData].tp_listid

    With that query you will retrieve all the data is contained in lists, and like that in your Sharepoint + PS environment, so you will need to custom the query to get what you need.

     

    Hope that helps!

     


    Miguel Soler
    • Proposed as answer by Miguelet_ Sunday, November 20, 2011 5:58 PM
    • Marked as answer by ElliJ Monday, November 21, 2011 8:26 PM
    Saturday, November 19, 2011 2:15 PM

All replies

  • You can modify the columns, but you'll have to report from SharePoint.
     
    A couple options there:
     
    1) PowerPivot
    2) Use Content Types and the Content Query Webpart Editor
    3) Write a Web Service to pull SharePoint data into a usable format
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Friday, November 18, 2011 12:42 PM
    Moderator
  • Is there a reason that I can't query the sharepoint content database? This is not a report we run very often, maybe every few weeks or so.
    Friday, November 18, 2011 9:47 PM
  • It's one of those questions that depends on whom you ask - hence you should keep asking until you get the answer you want....

    Ask a SharePoint developer, and it's required.

    Ask a SharePoint infrastructure type, and it's probably ok.

    I know clients who have queried the database directly and nothing bad has happened (yet).  Officially though, you shouldn't be querying the db directly.


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Saturday, November 19, 2011 3:34 AM
    Moderator
  • Hi Ellij,

    As you said, it´s a simple question!

    In the ProjectServer_Reporting database there is a table called MSP_WssIssue, and other one called MSP_WssRisk.

    This tables content all the information that you want, relationing with the ProjectUID, not as Project Server 2007 when you had to ask the WSS_Content database, this was really painfull...

    Hope that helps you!

     

    Kind regards


    Miguel Soler
    • Proposed as answer by Miguelet_ Saturday, November 19, 2011 12:27 PM
    • Unproposed as answer by Miguelet_ Saturday, November 19, 2011 12:30 PM
    Saturday, November 19, 2011 12:26 PM
  • Ellij,

    Hi again, I didnt read your question before very well, and I guess you are looking for:

     - Create a custom list in a Project Site

     - Create a custom column in that list

     - Retrieve that information

    The only approach I´ve found in DB architecture in PS2010 for that issue is the tables that are created in the Reporting database with information about Issues and Risks.

    So we have to turn back to PS2007 DB architechture to get what you are looking for and we have to query the WSS_Content database of Project Server, btw, when you identify that database you can make a query that look like this:


    Select [WSS_Content].[dbo].[AllWebs].Title as [Project Title],
             [WSS_Content].[dbo].[AllLists].tp_Title as [List Title],
             [WSS_Content].[dbo].[UserData].*
             From [WSS_Content].[dbo].[AllWebs] inner join [WSS_Content].[dbo].[AllLists] on
                  [WSS_Content].[dbo].[AllWebs].id =[WSS_Content].[dbo].[AllLists].tp_WebId
                  inner join  [WSS_Content].[dbo].[UserData] on
                  [WSS_Content].[dbo].[AllLists].[tp_ID] =[WSS_Content].[dbo].[UserData].tp_listid

    With that query you will retrieve all the data is contained in lists, and like that in your Sharepoint + PS environment, so you will need to custom the query to get what you need.

     

    Hope that helps!

     


    Miguel Soler
    • Proposed as answer by Miguelet_ Sunday, November 20, 2011 5:58 PM
    • Marked as answer by ElliJ Monday, November 21, 2011 8:26 PM
    Saturday, November 19, 2011 2:15 PM
  • Hi,

    Certainly querying the SharePoint content db as Miguel states above is an option, but not a supported one. I know of customers who have developed reports that run directly against the contentdb with no issue.

    With SQL Server 2008 R2 Reporting Services it is possible to use a SharePoint list as a data source for a report, which is ideal from one report , one set of risks or issues perspective. It does get a little harder when you want to do something at a portfolio level and need to take into account all risks and issues. In that case you can write a web service that will iterate through all projcets and perform CAML lookups, or you could use a third party tool such as iPMO's data miner (www.ipmo.com.au) which will copy all lists in the workspaces into a data mart in a supported manner for reporting.

    Hope this helps,


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Saturday, November 19, 2011 9:42 PM
    Moderator
  • Thanks to all of you for your help.  I think I'm on the right track now.  I also found this:

    http://blogs.catapultsystems.com/epm/archive/2010/11/02/adding-project-server-data-as-columns-to-sharepoint-sites.aspx

    Which seems like it would foot the bill. 

    Monday, November 21, 2011 7:34 PM
  • Well - that just pulls Project Server data into the Risks list as an available column.  There's plenty more where that came from:

    http://azlav.umtblog.com/category/external-content-types/

    ...note a couple blogs talking about using the saming mechanism to report out of the database as well.


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Monday, November 21, 2011 7:53 PM
    Moderator