none
Querying POL sharepoint lists power query RRS feed

  • Question

  • Hi, I am trying to query the SharePoint lists directly to investigate what information is available that way as compared to using the project api.

    There are some significant differences in the data returned - the query that points to the api (http://<pwa_site>/_api/ProjectData/Projects) has 561 risks and 30 fields returned, whereas the query that points directly to the SharePoint list returns 47 risks and 78 fields. Annoyingly, not all of the fields from the project api  are included in this 78. E.g. there is a field called ‘AssignedToResource’ included in the 30 from the project api that is not returned in the SharePoint query.

    I have created the query to sharepoint by going 

    new source > other sources > SharePoint lists > https://<pwa_site> > all risks
    • Why are only some risks returned from this sharepoint query?
    • Is it possible to expose custom fields via this method?
    • Where does the project API get the AssignedToResource if it is not returned by the sharepoint query?
    Friday, March 22, 2019 8:50 AM

Answers

  • Hello,

    When using the Project Reporting API (_api/ProjectData) this will return all of the Risks for all projects in Project Online where as when you use the SharePoint API (_api/web/lists/GetByTitle('Risk') this will be limited to the current project site. The Project Reporting API reads the Risk / Issue data from the Project Online Reporting schema tables, this data in synchronised from the SharePoint data into the Project Online schema tables.

    The _api/ProjectData API is limited to only default fields on the Project Site that Microsoft include where as the SharePoint list API will include all of the SharePoint fields.

    There SharePoint API requires additional lookups (expands) to get some data such as the user display names as by default these return the user ID, where as the Project Online sync process includes the users display name when in syncs to the Project Online schema tables.

    This post might help: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/

    Paul 


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

    Saturday, March 23, 2019 10:39 AM
    Moderator
  • Hello,

    Post updated.

    With the steps you are following there, I assume you have created a custom SharePoint list at the root PWA site collection level called "All Risks"? This list is different to the "Risks" list that you will see on all of the project sites - https://<tenant>.sharepoint.com/sites/ppm/ProjectSite1/Lists/Risks for example. The risks found on all the project sites ({PWAURL}/{projectsite}/Lists/Risks) will be the risks you find in the Project Online Reporting API ({PWAURL}/_api/ProjectData/Risks).

    Does that help?

    Paul


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


    Friday, March 29, 2019 8:16 PM
    Moderator
  • Hello,

    I don't think this is documented anywhere but the best feature to use for reporting is the metadata document for the reporting API: {PWA Site URL}/_api/ProjectData/$metadata. Here you will see all of the fields you have and the navigational properties for each endpoint.

    To actually see / use the default lists, create new  "Project Site" from the Project Site default template in PWA and activate the "Project Web App Connectivity" site feature in this new site / sub web. This will then create the default Issues / Risks lists.

    Hope that helps.

    Paul


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

    Monday, April 1, 2019 10:37 AM
    Moderator

All replies

  • Hello,

    When using the Project Reporting API (_api/ProjectData) this will return all of the Risks for all projects in Project Online where as when you use the SharePoint API (_api/web/lists/GetByTitle('Risk') this will be limited to the current project site. The Project Reporting API reads the Risk / Issue data from the Project Online Reporting schema tables, this data in synchronised from the SharePoint data into the Project Online schema tables.

    The _api/ProjectData API is limited to only default fields on the Project Site that Microsoft include where as the SharePoint list API will include all of the SharePoint fields.

    There SharePoint API requires additional lookups (expands) to get some data such as the user display names as by default these return the user ID, where as the Project Online sync process includes the users display name when in syncs to the Project Online schema tables.

    This post might help: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/

    Paul 


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

    Saturday, March 23, 2019 10:39 AM
    Moderator
  • Hi Paul, you mention

    "when you use the SharePoint API (_api/web/lists/GetByTitle('Risk') this will be limited to the current project site."

    We only have one project site (https://xxx.sharepoint.com/sites/abc/projects.aspx)

    Why am I only seeing ~10% of the risks across the projects within that site?


    • Edited by rapscalli Friday, March 29, 2019 12:07 PM
    Wednesday, March 27, 2019 8:54 AM
  • Hello,

    You are pointing to the PWA site collection, not a project site there. A project site where the risks and issues can be found are sub webs of the PWA site collection, for example: https://<tenant>.sharepoint.com/sites/ppm/ProjectSite1

    Paul


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


    Wednesday, March 27, 2019 9:11 AM
    Moderator
  • Hi Paul, apologies for my poor understanding  - when i create a new power bi query by doing the following:-

    • new source
    • sharepoint lists
    • "https://XXX.sharepoint.com/sites/ABC" as URL
    • Select 'all risks' table
    • OK

    What risks am i seeing? It appears to be a random subest of risks from across the projects.

    PS, I have mistakenly included my company details in the URL above - are you able to remove from your quote?  

    Friday, March 29, 2019 12:06 PM
  • Hello,

    Post updated.

    With the steps you are following there, I assume you have created a custom SharePoint list at the root PWA site collection level called "All Risks"? This list is different to the "Risks" list that you will see on all of the project sites - https://<tenant>.sharepoint.com/sites/ppm/ProjectSite1/Lists/Risks for example. The risks found on all the project sites ({PWAURL}/{projectsite}/Lists/Risks) will be the risks you find in the Project Online Reporting API ({PWAURL}/_api/ProjectData/Risks).

    Does that help?

    Paul


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


    Friday, March 29, 2019 8:16 PM
    Moderator
  • Hi Paul, we do indeed have a list 'all risks' that is not being used and was not aware of. When i saw this list when accessing from PowerBI I thought it was a self generated list of all risks from project sites (if only!)

    PS, is there any documentation as to the schema / standard fields of project online for the 'out-of-the-box' lists?


    • Edited by rapscalli Monday, April 1, 2019 9:17 AM
    Monday, April 1, 2019 9:17 AM
  • Hello,

    I don't think this is documented anywhere but the best feature to use for reporting is the metadata document for the reporting API: {PWA Site URL}/_api/ProjectData/$metadata. Here you will see all of the fields you have and the navigational properties for each endpoint.

    To actually see / use the default lists, create new  "Project Site" from the Project Site default template in PWA and activate the "Project Web App Connectivity" site feature in this new site / sub web. This will then create the default Issues / Risks lists.

    Hope that helps.

    Paul


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

    Monday, April 1, 2019 10:37 AM
    Moderator
  • Hi Paul - many thanks for your help on this.
    Monday, April 1, 2019 11:22 AM