none
How to Programatically get Risks and Issues from a Project Server 2010 instance RRS feed

  • Question

  • Hello I have a sharepoint 2010 enterprise with a project server 2010 on it and I am trying to retrieve the list of risks and issues from a project using Visual Studio 2010 C#. I would like to be able to retrieve all of the data associated with a Risk and display it in my application.

    I have been researching it for a few hours now and cannot seem to get anywhere.. I read that you can link tasks with risks and find them that way, but I want to be able to find all risks and issues no matter if they are linked to a task or not.

    I see that A project dataset(SvcProject.ProjectDataSet) created using ReadProject  has  WPROJ_RISK_LIST_NAME and WPROJ_ISSUE_LIST_NAME columns with a GUID -> could I somehow use that to retrieve??

    Any help would be greatly appreciated!

    Tuesday, August 21, 2012 8:21 PM

Answers

  • For anyone who ever runs into a similar issue here is how I solved it.

    I used the WssInteropSvc to programatically get the list location and using SP.ClientContext get the lists and their items

    using SP = Microsoft.SharePoint.Client; using Microsoft.SharePoint; using Microsoft.SharePoint.Client; SvcWssInterop.WssInteropClient interop = new SvcWssInterop.WssInteropClient(ENDPOINT_PROJ_WSS); //App or Web config endpoint for projectserver.svc SvcWssInterop.ProjectWSSInfoDataSet dsProjectWssInfo = new SvcWssInterop.ProjectWSSInfoDataSet(); interop.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password", "domain"); //if you need it put your credentials here dsProjectWssInfo = interop.ReadWssData(g); //get information for the project in question -> g is the projuid String ListsUrl = dsProjectWssInfo.ProjWssInfo[0].PROJECT_WORKSPACE_URL; //get the url for where the lists are located SP.ClientContext SPContext = new SP.ClientContext(ListsUrl); //Create a context for the lists url you got above SP.Web myWeb = SPContext.Web; SP.Site site = SPContext.Site; SP.List myList = myWeb.Lists.GetByTitle("Risks"); //Find list with name Risks SP.List myList2 = myWeb.Lists.GetByTitle("Issues"); //Find list with name Issues SP.CamlQuery query = new SP.CamlQuery(); query.ViewXml = "<Where><Contains><FieldRef Name='Status' /><Value Type='Text'>1</value></contains></where> "; //All active risks SP.ListItemCollection risksCollection = myList.GetItems(query); SPContext.Load(risksCollection); SPContext.ExecuteQuery(); //Execute the query - this populates the risksCollection with all he risk lisk items foreach (ListItem item in risksCollection) { //Do something here.. items are gotten using "item["ColumnName"] }

    //Now do the same as above but for the issues list


    By the way I was using MVC4 for my application which uses .NET4 and that is the reason why I could not use SPSite... API because that required .net3.5 and the application to run in x64bit.. trying to open SPSite s = new SPSite(ListsUrl); gives the famous "FileNotFoundException".

    One problem with the Project Server SDK and the one that led me to hours of stress is that it does not provide an API for retrieving risks and issues (a HUGE big part of project management so no clue why they didnt add a simple project.getRiskList() and project.getIssueList()).. So the workaround as I provided here is to use the SharePoint api which allows you to retrieve a list on a sharepoint 2010 web app.

    Reply in this thread if this code saved your life and feel free to give me some points :)

    • Marked as answer by gkrilov Friday, August 24, 2012 6:13 PM
    • Edited by gkrilov Friday, August 24, 2012 6:18 PM more info
    Friday, August 24, 2012 6:08 PM

All replies

  • Hi there--

    To read the project's Risks & issues or other custom list, you can use the [MSP_WssIssue] & [MSP_WssRisk] table or MSP_WssIssue_OlapView, MSP_WssRisk_OlapView views from Reporting database. Please note that it will have only out of box fields for Issues/Risks list.
    If you want to read the fields which are custom as defined by administrator, you will need to Read project site url from [ProjectWorkspaceInternalHRef] column of [MSP_EpmProject] table in Reporting database. Once you have the URL, you can use the SPList from sharepoint object model to read all the fields in Issues/risk form.

    SELECT  [ProjectUID]
          ,[ProjectName]
          ,ProjectWorkspaceInternalHRef
      FROM [MSP_EpmProject]

    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.aspx
    http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.getitems.aspx

    Hope that helps.

     


    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”. Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

    Wednesday, August 22, 2012 6:38 AM
  • Sharepoint also supports a ODATA service for all lists. It may be helpful to use here. I don't have the document in front of me but searching for SharePoint 2010 odata list service should help you find it.

    Treb Gatte @tgatte

    Wednesday, August 22, 2012 12:55 PM
    Moderator
  • Hi thank you for the answer :)

    Just to make sure.. when you say RDB you mean the "Database Name" field in "Central Administration -> manage content databases" right? Just want to make sure because so far I have not touched any databases directly as I was simply able to get all of the information using the PSI... so it would be Database Server\WSS_Content

    Now while I wait to get access to this database I am just wondering is there some simple PSI call to get risks and issues for a project? So far I was able to get all the information I need such a project information and custom fields for it using the PSI..  so what is the reason to make direct sql access a requirement for risks and issues?

    Thanks again,

    Greg

    Wednesday, August 22, 2012 2:12 PM
  • To add to my previous reply... this application will be used by many people in our organization to connect to our project server and view project information. Is it even a good idea to hard code a connection string to be able to retrieve risks and issue information? It's hard to believe there isn't an API somewhere that does this.. however I have been looking for almost an entire work day for the answer but have been unable to find it..

    Why is it that I am able to get a bunch of project information using the PSI but cannot find a way to get risks and issues this way without explicit database calls?

    Wednesday, August 22, 2012 6:07 PM
  • For anyone who ever runs into a similar issue here is how I solved it.

    I used the WssInteropSvc to programatically get the list location and using SP.ClientContext get the lists and their items

    using SP = Microsoft.SharePoint.Client; using Microsoft.SharePoint; using Microsoft.SharePoint.Client; SvcWssInterop.WssInteropClient interop = new SvcWssInterop.WssInteropClient(ENDPOINT_PROJ_WSS); //App or Web config endpoint for projectserver.svc SvcWssInterop.ProjectWSSInfoDataSet dsProjectWssInfo = new SvcWssInterop.ProjectWSSInfoDataSet(); interop.ClientCredentials.Windows.ClientCredential = new NetworkCredential("username", "password", "domain"); //if you need it put your credentials here dsProjectWssInfo = interop.ReadWssData(g); //get information for the project in question -> g is the projuid String ListsUrl = dsProjectWssInfo.ProjWssInfo[0].PROJECT_WORKSPACE_URL; //get the url for where the lists are located SP.ClientContext SPContext = new SP.ClientContext(ListsUrl); //Create a context for the lists url you got above SP.Web myWeb = SPContext.Web; SP.Site site = SPContext.Site; SP.List myList = myWeb.Lists.GetByTitle("Risks"); //Find list with name Risks SP.List myList2 = myWeb.Lists.GetByTitle("Issues"); //Find list with name Issues SP.CamlQuery query = new SP.CamlQuery(); query.ViewXml = "<Where><Contains><FieldRef Name='Status' /><Value Type='Text'>1</value></contains></where> "; //All active risks SP.ListItemCollection risksCollection = myList.GetItems(query); SPContext.Load(risksCollection); SPContext.ExecuteQuery(); //Execute the query - this populates the risksCollection with all he risk lisk items foreach (ListItem item in risksCollection) { //Do something here.. items are gotten using "item["ColumnName"] }

    //Now do the same as above but for the issues list


    By the way I was using MVC4 for my application which uses .NET4 and that is the reason why I could not use SPSite... API because that required .net3.5 and the application to run in x64bit.. trying to open SPSite s = new SPSite(ListsUrl); gives the famous "FileNotFoundException".

    One problem with the Project Server SDK and the one that led me to hours of stress is that it does not provide an API for retrieving risks and issues (a HUGE big part of project management so no clue why they didnt add a simple project.getRiskList() and project.getIssueList()).. So the workaround as I provided here is to use the SharePoint api which allows you to retrieve a list on a sharepoint 2010 web app.

    Reply in this thread if this code saved your life and feel free to give me some points :)

    • Marked as answer by gkrilov Friday, August 24, 2012 6:13 PM
    • Edited by gkrilov Friday, August 24, 2012 6:18 PM more info
    Friday, August 24, 2012 6:08 PM
  • Hey there, i wanna know if is possible use SP.List on VBA macros to get list of risks and Issues? tnakyou
    Tuesday, November 6, 2012 1:36 PM