none
When do Enterprise Custom Fields get (re-)calculated - Or How to extract Status Manager from Reporting Database RRS feed

  • Question

  • We need to report on approvers of Tasks from the Reporting database.

    There is no standard field in the Reporting database but we found that you could add an Enterprise custom field with a formula of [Status Manager] - where Status Manager is a standard product field (but sadly not in database)

    We thought this was working but it now seems that plenty of projects have no value for this field in the Reporting Database, even though they have just been published.

    My theory is that the field is not recalculated and updated in the database unless the published project has the field displayed in the current view. Is this true? And is there a way of forcing recalculation every time the project is published?

    We are on Project Server 2007 - but interested to know if later versions would help us.

    Mike


    MikeCro

    Friday, June 13, 2014 12:52 PM

All replies

  • Hi Mike,

    I don't have right now the 2007 SDK so I can't check but retrieve the status manager from the PS2010 reporting DB is absolutely doable. So maybe just check if the scheme is not the same in 2007.

    The [dbo.MSP_EpmTask] view contains the [TaskStatusManagerUID] field that you can correlate to the resource UID to have the status manager name.


    Hope this helps,


    Guillaume Rouyre, MBA, MCP, MCTS |

    Friday, June 13, 2014 1:00 PM
    Moderator
  • We set this up 6 months ago and I didn't find [TaskStatusManagerUID] then in [dbo.MSP_EpmTask]. I just checked again; both there are in the 2 related views.

    Good to hear 2010 has it as standard - no need to use this semi-functional custom field.

    Any ideas on how to ensure the custom field is kept updated? ie that the formula is recalculated every time the plan is saved/published?

    Mike


    MikeCro

    Friday, June 13, 2014 2:41 PM
  • We set this up 6 months ago and I didn't find [TaskStatusManagerUID] then in [dbo.MSP_EpmTask]. I just checked again; both there are in the 2 related views.

    Do I understand well if I say that you finally found the taskstatutmanagerID field in the 2007 reporting DB?

    In this case you don't need anymore the custom field with the formula.

    That being said, you could do some VBA code catching the save/publish event and sending the status manager to the custom field. Note that is hsa to be a task custom field rolled down at assignment level since the status manager is an assignment level attribute.


    Hope this helps,


    Guillaume Rouyre, MBA, MCP, MCTS |

    Friday, June 13, 2014 3:07 PM
    Moderator
  • No. Sadly it is not there in 2007. So I am stuck with the custom field and formula.

    Do you/anyone have any pointers as how to do this in VBA - have done plenty in Excel/Word but never in Project.

    Is there a way of centrally establishing the VBA so that everyone runs the code without having to distribute it. Or do I have to send an exported module?

    We have some Powershell scripts based on PSI - anyway of triggering the update that way? But I really don't want to create/maintain a new table the way I had to with the Cost Table!

    Mike


    MikeCro

    Friday, June 13, 2014 3:23 PM
  • Rod will definitely be your man. He wrote THE vba book for Project. Hope he'll jump soon in this post.

    Basically you'll insert the code in a module in the enterprise global thus the deployment will be transparent for the users.

    For the powershell, it is indeed another path, but I think vba is simpler. But at this stage I'll let other experts from this forum jump in.


    Hope this helps,


    Guillaume Rouyre, MBA, MCP, MCTS |

    Friday, June 13, 2014 3:42 PM
    Moderator
  • Thanks. Have got a simple VBA event handler in Enterprise Global now. Just need help knowing what to do in it.

    Have reposted in the Programming/Customisation forum

    Mike


    MikeCro

    Monday, June 16, 2014 11:08 AM
  • Hi Mike,

    Good that you posted your new concern in the programming forum.

    If any of the replies above answered the initial concern, please mark them as helpful so other users could refer to them.


    Hope this helps,


    Guillaume Rouyre, MBA, MCP, MCTS |

    Monday, June 16, 2014 2:32 PM
    Moderator
  • heres the query from the reporting database in 2010

    There is a status manager field also in the published database if you want that field

    from my other post here on the same topic (http://social.technet.microsoft.com/Forums/projectserver/en-US/a333d263-aa62-4aa8-8770-bbb64d6fc826/project-server-2010-change-status-manager?forum=projectserver2010general)

    it will set the project owner to the status manager

    #################################################

    USE ProjectServer_Reporting

    UPDATE MSP_EpmTask

    SET TaskStatusManagerUID = MSP_EpmProject.ProjectOwnerResourceUID

    FROM MSP_EpmProject

    WHERE MSP_EpmTask.ProjectUID = MSP_EpmProject.ProjectUID

    #################################################

    • Edited by kbwrecker Wednesday, June 18, 2014 8:12 PM
    Wednesday, June 18, 2014 8:07 PM