none
Report to List of all Proejcts that a user is member RRS feed

  • Question

  • Hi,

    I am working on a Report requirement to list all projects that a specific user is a member. There will be multipe users in the report. I have to query the Reporitng Database to get all projects that a user is member of.

    I would like to know the tables in Reporting database that can help me extract this information. Any help in this regard will be appreciated.

    Wednesday, February 18, 2015 7:13 PM

Answers

  • Hello,

    Does this user have assignments of the projects or just in the project team? If they have assignments you will be able to check the assignment tables / views in the Reporting database. If they are just in the project team without assignment this information is not in the Reporting database, you would need to get this information via the PSI. For an example PSI code snippet (via PowerShell) to get the project team see below:

    $pwaUrl = "http://vm353/pwatest" 
    $svcProjectUrl = $pwaUrl + "/_vti_bin/PSI/Project.asmx?wsdl" 
    $c = Get-Credential 
    $svcProjectProxy = New-WebServiceProxy -uri $svcProjectUrl -credential $c 
    $svcProjectProxy.ReadProjectTeam("3DC95F6F-60C4-48CB-8DA0-AA28DA6E31FD").ProjectTeam #will return all fields in the ProjectTeam dataset for the given project  GUID

    Taken from the post below: https://pwmather.wordpress.com/2012/01/12/projectserver-2010-psi-powershell-examples-ps2010-msproject-sp2010/

    Paul


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

    Wednesday, February 18, 2015 8:16 PM
    Moderator
  • Hello,

    If that user/resource selected has assignments in those project plans then it will all work via SQL from the Reporting database / schema. If the user / resource is only on the project team and no assignments then this information is not in the reporting database / schema.

    Paul


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

    Friday, February 20, 2015 12:17 PM
    Moderator

All replies

  • Hello,

    Does this user have assignments of the projects or just in the project team? If they have assignments you will be able to check the assignment tables / views in the Reporting database. If they are just in the project team without assignment this information is not in the Reporting database, you would need to get this information via the PSI. For an example PSI code snippet (via PowerShell) to get the project team see below:

    $pwaUrl = "http://vm353/pwatest" 
    $svcProjectUrl = $pwaUrl + "/_vti_bin/PSI/Project.asmx?wsdl" 
    $c = Get-Credential 
    $svcProjectProxy = New-WebServiceProxy -uri $svcProjectUrl -credential $c 
    $svcProjectProxy.ReadProjectTeam("3DC95F6F-60C4-48CB-8DA0-AA28DA6E31FD").ProjectTeam #will return all fields in the ProjectTeam dataset for the given project  GUID

    Taken from the post below: https://pwmather.wordpress.com/2012/01/12/projectserver-2010-psi-powershell-examples-ps2010-msproject-sp2010/

    Paul


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

    Wednesday, February 18, 2015 8:16 PM
    Moderator
  • Thanks for the Post. This is very useful. However, the report is expected to be interactive. The user will specify a user and get the list of projects for the user. How can Powershell script be used for this type of report?
    Friday, February 20, 2015 12:12 PM
  • Hello,

    If that user/resource selected has assignments in those project plans then it will all work via SQL from the Reporting database / schema. If the user / resource is only on the project team and no assignments then this information is not in the reporting database / schema.

    Paul


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

    Friday, February 20, 2015 12:17 PM
    Moderator