none
What Database Table are Resource Enterprise Custom Fields Stored RRS feed

  • Question

  • Hello,

    I created an Enterprise Custom Field at the Resource level that uses a formula. Each resource in my project has this field populated in the resource sheet. Does anyone know what database table (I think in the Published database) the values associated with this ECF are stored?

    Thanks,

    Jessica


    Jessica Hancock

    Monday, July 22, 2013 5:31 PM

All replies

  • Jessica,

    Assuming 2010 version, You should see these values in the Reporting database, the view MSP_EPMRESOURCE_USERVIEW..


    Prasanna Adavi,PMP,MCTS,MCITP,MCT http://thinkepm.blogspot.com

    Monday, July 22, 2013 8:03 PM
    Moderator
  • Hi Jessica,

    As Prasanna said for 2010 you will find these fields in the the MSP_EpmResource_UserView, the only custom fields you wont find there are multi valued lookup table fields. Example for access the multi valued fields can be seen here if needed: http://pwmather.wordpress.com/2011/09/07/accessing-custom-fields-that-allow-multiple-values-from-look-up-tables-in-projectserver-via-t-sql-msproject-ps2010-ps2007/. The same applies to 2007 also.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Monday, July 22, 2013 9:40 PM
    Moderator
  • Yes, I am using 2010 and the enterprise custom field does not use a lookup table. I found this field in my database in the view MSP_EpmResource_UserView but a lot of values seemed to be missing. I did a little more digging and found a column called TEXT_VALUE in the Published table MSP_Proj_RES_CUSTOM_FIELD_VALUES. This column seems to have a lot more of my data for this custom field. Do you know why these two columns might be different and why the Published table seems to have all of my data while the Reporting one only has some?

    Jessica


    Jessica Hancock

    Wednesday, July 24, 2013 2:39 PM
  • Hi Jessica,

    For an example resource that has resource data missing in the PWA Reporting database, edit the resource and save to force a synch to the reporting DB for that resource. Check the Project Server queue to ensure all jobs complete successfully then check the Reporting DB again. On a side note, it is not supported to query the Published, Draft or Archive DBs directly with T-SQL, you need to use the PSI.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Wednesday, July 24, 2013 2:55 PM
    Moderator
  • Hi Paul,

    I just tried to update the resources in a project like you suggested. All the queue jobs completed successfully but I am still not seeing all of the resources in this view. Is it possible that I am only seeing Enterprise Resources here?

    Jessica


    Jessica Hancock

    Wednesday, July 24, 2013 3:13 PM
  • Hi Jessica,

    When I mentioned editing a resource, do this from the Resource Center.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Wednesday, July 24, 2013 3:14 PM
    Moderator
  • Paul,

    If that's the case then I think I know what the issue is. The resources that are "missing" from this view are local project resources. All of the Enterprise Resources are displayed. My Enterprise custom field displays in the project's resource sheet and is populated for both Enterprise and Local resources. That information must be stored elsewhere in the database.

    Jessica


    Jessica Hancock

    Wednesday, July 24, 2013 3:17 PM
  • This information is not available in the PWA reporting DB as you have found. Any reason for using local resources?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Wednesday, July 24, 2013 3:24 PM
    Moderator
  • Each project has the freedom to name material and travel resources any way they like. We felt that importing all of these resources into the Enterprise Resource Pool would be unnecessary and clutter the pool.

    Jessica


    Jessica Hancock

    Wednesday, July 24, 2013 3:26 PM
  • If you want to look at reporting on these in the Reporting database I would look at standardising the resources and using enterprise resources.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

    Wednesday, July 24, 2013 4:27 PM
    Moderator
  • MSP_EpmResource_UserView

    Hope this will help you. 

    -------------------------------------------------------------------------

    Raushan Kumar  | LinkedIn
    Please click Mark As Answer if this post solves your problem or Vote As Helpful if a post is useful to you. This will be useful to other community members following the thread.

    Thursday, July 25, 2013 6:27 PM