none
multi line enterprise custom field in pdp page for capturing weekly overall project status RRS feed

  • Question

  • we want to produce a weekly status report where we show project name, issues, risks and general status.  We know we can pick name, issues and risks up from the project server database.  However, what about a project status that is at the project level?  Can we create an enterprise custom field at the project level and put that on a pdp page and enter text into it and it be stored in the server database?  If so, will it store history from week to week so we could look at status history?  Frankly we thought about doing a list in the project site and pick up the information from just the sharepoint site but no status list appears in the site.  We also thought about the status report within project server but that seems to be almost a separate feature.  Any recommendations would be greatly appreciated.  ??

    Cletus51

    Tuesday, May 14, 2013 2:25 AM

All replies

  • You can do most of what you want to do with enterprise custom fields.  However, one thing you will not get is a log or history of the status.  It's not maintain in project server, once you update the field, the previous status and its history is lost.

    I don't recommend this, but you could put a trigger on the MSP_EPMProject table and create a trigger to store the value.   Probably a better solution is write some custom code and capture the history using the project update and save event. 

    Cheers!


    Michael Wharton, MVP, MBA, PMP, MCT, MCTS, MCSD, MCSE+I, MCDBA
    Website http://www.WhartonComputer.com
    Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Tuesday, May 14, 2013 3:05 AM
    Moderator
  • Thanks for the quick reply.  I also just noticed that you cannot update the multiline field IF you have a project that has to be opened in project pro to update it due to features in the project that require pro.  I must confess, I am getting a bit frustrated with a simple task.  I want to produce a weekly report that has project name, issues, risks, start date and a few lines of project status.  No big deal I thought.  Do you have any ideas?  I know I can get everything I want from the project reporting database except for the status.  Do you of a way?  I thought about a sharepoint list that just has status in it becasue the sharepoint site has risks and issues as well but how can I read a sharepoint list and project server sql at the same time?  You would think this would not be a big deal.  Any suggestions will most certainly be appreciated.  help!

    Cletus51

    Tuesday, May 14, 2013 3:27 AM
  • Typcially I store the status as a list in the project site.  Using SSRS the latest item in the list can be brought into a report.

    Ben Howard [MVP] blog | web

    Tuesday, May 14, 2013 7:51 AM
    Moderator
  • Ben, quick question on your reply.  With SSRS can I pull from the Project Server tables while at the same time I pull from a sharepoint list?  I need to be able to pick up all fields from project server except of course the status which would be in a list.  That is what I really want to do.  If I cannot do that, is it possible to use SSRS to pull from multiple sharepoint lists at the same time?  ( I am certainly open to any other suggestion you might have.)   

    Cletus51

    Tuesday, May 14, 2013 1:41 PM
  • With SSRS, you can have multiple connections to deferent data source. so, in your case, you will have a connection to the reporting database for project server (assuming this is 2010) and another connection to the project site. now, I have to tell you that this will work for a report that will pull the status one project at a time because you can only connect to one SharePoint Site per connection string.

    other solution is, have the status notes in a custom filed. have a stored procedure that will copy the desired project information on a daily schedule to another table in SQL. now you have a historical data for each project. so you may design you report to filter by status date (the date the SP captured the data).

    let me know if that works for you, I can give you an example of how to do this.

    Best regards,


    Ali Al . Consultant & Development Manager.
    epma
    FB TW IN

    Friday, May 17, 2013 4:44 PM
  • Thanks.  Yes; would love to see example of how to do this.  Really hoping I can get this to work in some way.

    Cletus51

    Friday, May 17, 2013 5:39 PM
  • here is an example, I select random columns but the Idea is the same:

    SELECT 
    GETDATE() as [Status Date]
    ,P.ProjectUID
    ,P.ProjectName AS [Project Name]
    ,P.ProjectOwnerName AS [Project Manager]
    ,p.[Project Cost]
    ,p.[Project Health]
    ,p.projectStatusNote AS [Status Notes]
    Into YourTableName --> the table wher you will store the data, this will be inside the reporting database
      FROM [PWA_Reporting].[dbo].[MSP_EpmProject_UserView]P

    then after the initial creation, schedule the next query to run daily using SQL agent, this will update the table and then you use it in the report and filter by status date.

    insert into YourTableName ([Status Date]
    ,ProjectUID
    ,[Project Name]
    ,[Project Manager]
    ,[Project Cost]
    ,[Project Health]
    ,[Status Notes])
    
    SELECT 
    GETDATE() as [Status Date]
    ,P.ProjectUID
    ,P.ProjectName AS [Project Name]
    ,P.ProjectOwnerName AS [Project Manager]
    ,p.[Project Cost]
    ,p.[Project Health]
    ,p.projectStatusNote AS [Status Notes]
    
      FROM [PWA_Reporting].[dbo].[MSP_EpmProject_UserView]P

    I hope this helps,


    Ali Al . Consultant & Development Manager.
    epma
    FB TW IN

    Friday, May 17, 2013 6:20 PM