locked
Risk List - Adding the project name RRS feed

  • Question

  • I want to create a consolidated list of risks from all projects in EPM 2010. 

    I tried to use Reporting Services.  My first handicap is that the list doesn't contain the project name.

    How can I add a column that will contains the project name in the consolidated list?

     


    Mermoz
    Wednesday, December 21, 2011 6:36 PM

Answers

  • OK Now that's better insight and this helps answer the query, you would need some custom solution for achieving this,typical solutions being used are as below

    1. one of the potential solution we have seen is using SSIS list adapter to read values from sharepoint list along with ProjUID and push it to custom table and use that for reporting :: Refer to >> http://sqlsrvintegrationsrv.codeplex.com/
    2. Develop custom code using list web service or object model which loops thorugh site & reads list data along with Project UID and push it to custom table followed by a join with project user view to get project name for reporting, to get projectUID, you can use this

      Guid ProjectUID = new Guid(SPContext.Current.Web.AllProperties["MSPWAPROJUID"].ToString());
    3. attach event handlers to sharepoint list & push data in custom table for reporting purpose
    4. Again Custom Code ::  http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx
    5. Try using Powerpivot for excel, using From Data Feeds Wizards, though not sure if it helps but think might work

    By now you would have understood there is no simple OOB way to do so :)

    Let us know if this helps

     


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    • Marked as answer by Mermoz Friday, December 23, 2011 7:52 AM
    Thursday, December 22, 2011 7:05 AM

All replies

  • i think you should check the SDK for documentation on Reporting DB schema, however here is a simple query which will give you all risks consolidated with project name

    Execute this query against project server reporting DB

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_WssRisk.Title, MSP_WssRisk.Status, MSP_WssRisk.AssignedToResource, MSP_WssRisk.Owner,
                          MSP_WssRisk.DueDate, MSP_WssRisk.Probability, MSP_WssRisk.Impact, MSP_WssRisk.Exposure, MSP_WssRisk.Cost, MSP_WssRisk.CostExposure,
                          MSP_WssRisk.Category, MSP_WssRisk.Description, MSP_WssRisk.MitigationPlan, MSP_WssRisk.ContingencyPlan, MSP_WssRisk.TriggerDescription
    FROM         MSP_EpmProject_UserView INNER JOIN
                          MSP_WssRisk ON MSP_EpmProject_UserView.ProjectUID = MSP_WssRisk.ProjectUID

     

    Let Us know if this helps


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Wednesday, December 21, 2011 7:43 PM
  • Thanks for the answer.  I was expecting a more general answer.  My apologies.

     

    I have 6 lists like this one and they are all personalised with custom fields.   With SRSS (Reporting Services) I can connect to  sharepoint list one project each time (or one connection for each project) .   I don't master yet how to create a consolidated list but looking at the fields in the risk list I don't see the project name or project ID. 

     

    That why I was asking for a way to add this information in the list.  I want to use it in a consolidated list from all projects.

     

     


    Mermoz
    Thursday, December 22, 2011 1:17 AM
  • OK Now that's better insight and this helps answer the query, you would need some custom solution for achieving this,typical solutions being used are as below

    1. one of the potential solution we have seen is using SSIS list adapter to read values from sharepoint list along with ProjUID and push it to custom table and use that for reporting :: Refer to >> http://sqlsrvintegrationsrv.codeplex.com/
    2. Develop custom code using list web service or object model which loops thorugh site & reads list data along with Project UID and push it to custom table followed by a join with project user view to get project name for reporting, to get projectUID, you can use this

      Guid ProjectUID = new Guid(SPContext.Current.Web.AllProperties["MSPWAPROJUID"].ToString());
    3. attach event handlers to sharepoint list & push data in custom table for reporting purpose
    4. Again Custom Code ::  http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spsitedataquery.aspx
    5. Try using Powerpivot for excel, using From Data Feeds Wizards, though not sure if it helps but think might work

    By now you would have understood there is no simple OOB way to do so :)

    Let us know if this helps

     


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    • Marked as answer by Mermoz Friday, December 23, 2011 7:52 AM
    Thursday, December 22, 2011 7:05 AM
  • Mermoz --

    I think there is an easier way to handle your reporting problem.  In the Business Intelligence Center, you could create a report on all Risks in all projects by using the OlapRisks cube.  When you build your report, add the Project List dimension to the Row Labels drop area and then add the additional information as needed.  Hope this helps.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, December 22, 2011 1:42 PM
  • Mermoz --

    To add to my previous reply, for even greater reporting capabilities on Risks, Issues, and Deliverables associated with every project, you might want to create a BI Center report using the OlapProjectSharePoint cube. This would allow you to show the names of every project, plus the names of every Risks, Issue, and/or Deliverable as needed.  Hope this additional thought helps.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, December 22, 2011 1:45 PM
  • Hello Dale

    As per mermoz's requirement there are custom columns also created in risk list which  i think might not be available in OLAP cube or in reporting DB as well


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com
    Thursday, December 22, 2011 3:07 PM
  • Sunil --

    Thanks for the correction.  I missed that one little minor detail in his questions.  :)


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Thursday, December 22, 2011 3:54 PM
  • Merci.  I appreciate your generosity to answer questions?
    Mermoz
    Friday, December 23, 2011 7:53 AM