Want to report on #ProjectOnline project site list data? Example using #PowerAutomate to push project site #SharePoint list data to #AzureSQLDatabase #PowerBI #Office365 #PPM #LessCodeMorePower

One of the challenges for reporting on Project Online data is building a report for cross project site lists. Lately I’ve had quite a few conversations about how to handle this so I thought it was worth sharing a blog post. This could be a Lessons Learned list or Change Requests list or even the Risks or Issues lists with custom columns. To get access to this data requires the use of the SharePoint List REST APIs. Whilst these aren’t complex to use, the complexity is that you have to query each list API on each project site separately. So if you have 2000 projects, that is 2000+ API calls. Over 4 years ago I published a blog post on how to do this using Power BI but this had a limitation that meant this would only refresh the data in the Power BI Desktop client. That post can be seen here: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/ 

In this blog post we take a look at an example Power Automate flow I built that pushes the Risk data into an Azure SQL Database, you would then get the data from the Azure SQL Database using Power BI without having to consume the SharePoint List REST APIs in Power BI. The Flow can be seen below:

Flow Summary

The Flow details can be seen below.

Flow1

Firstly, this Flow runs on schedule, as seen in this example it is running once a week. The first action is an HTTP call to the Project Online Reporting OData API to get a list of the Project Site URLs. Next some variables are initialised and set, this is to build up the various URLs need for the API calls need to get the data:

Flow2

The SetRiskUrl variable value is what needs to be updated for your specific risk list – update for the correct list columns you want to include. Similarly, if you want to sync a different list this would need to be updated with the correct list title and columns. This is just the standard SharePoint list REST API URL OData syntax. The next part of the Flow is a for each loop, the output from the first HTTP action is passed in, this is the data from the Project Online Reporting API call – Project Names and the Project Site URLs. Use an expression to pass this in such as: body(‘action’)[‘value’] – note the apostrophes in this text are not correct so copy and paste wont work. Then another variable is set, this time project site URL, this is set with the current item project site URL in the for each loop. Use an expression to reference items from the for each such as: items(‘action’)?[‘propertyname’] – the note re apostrophes applies here too. This Project Site Url is then used in the site address for the next HTTP action – this action is used to get the Project GUID (MSPWAPROJUID) from the Project Site using the SharePoint Web REST API:

Flow3

The Flow then uses another HTTP call, this time to query the SharePoint List REST API for the Risks list. Another for each loop is used to loop through all of the SharePoint items returned in the HTTP action, the output from the HTTP action is passed in to the for each action:

Flow4

The final action in this Flow is a SQL Insert Row action, item properties from the for each risk action are mapped to the correct SQL columns as seen below:

Flow6

When that runs it will push the data into an Azure SQL database like below:

SQL1

This can then be used as the data source for reports, in this case a Risk report. The ProjectGUID can be used to join to other Project Online project data in Power BI / your reporting tool. The SQL database table has to be designed and created in the SQL database outside of this sync process. This is a very basic Power Automate flow that would need a fair amount of work before this could be used in a production environment but it gives us an idea of what could be done. This solution starter flow can be downloaded from my GitHub repro here: https://github.com/pwmather/ProjectOnlineListSyncFlow/blob/master/GetAllRisksDataExample_20200406223030.zip and imported into your Flow environment. It will need to be updated to get it working when you import.

Things to keep in mind that would need to be handled before something like this example solution start flow could be used in Production are:

  • Requires updates to match your requirements
    • update to include the SharePoint lists and columns on those lists
  • Update the flow to handle missing columns on lists as different project sites could have different list configurations
  • Update the flow to handle missing data in certain columns where expands are required as the SQL insert action expects the data
  • Update the flow to handle the SharePoint and Project Online API paging
  • Update the flow to handle errors including possible unknown transient errors
  • Data retention in the target SQL tables
  • SQL database maintenance to keep the database performant

There are 3rd party products that do this very well, one of those being the edison365 DataStore solution: edison365 DataStore This is a product I know very well, others I don’t have experience of. edison365 DataStore is an enterprise ready SaaS application built in Microsoft Azure that synchronises all of the Project Online data into your Azure SQL Database in near real time – it is all event driven. This product has the following advantages over the example Power Automate Flow I have put together in this post:

  • Simple user interface to configure what data from Project Online is synchronised to your Azure SQL Database – no need to know the Project Online or SharePoint REST APIs or OData syntax
    • Projects, tasks, resources, timesheets, baseline data etc. plus Project multiline custom fields with HTML, any SharePoint lists from the project sites
  • Event driven and scheduled data syncs – get the data in the database as it changes in Project Online
  • edison365 DataStore manages the SQL database schema, as new custom fields / columns are added in Project Online or a new SharePoint list is synced to the database, the SQL schema is updated automatically to support the new configuration  – no SQL admin knowledge required
  • Robust sync process
  • Full history kept in the Azure SQL database enabling trend reports to easily be created
  • SQL database maintenance plan built in
  • Keep track of who updated the data in Project Online

Building a project trend report like this where you can see how the RAG Status changes over time and who updated the project is very simple:

Report1

Or simple cross project site list report like this test Change Request list:

Report2

That concludes this blog post, two options to improve Project Online report experience with either building and maintaining something in Power Automate or looking for a 3rd party product like edison365 DataStore. I have another example Power Automate Flow coming later this week or early next offering something for those users who are using Microsoft Project and SharePoint Online!

2 thoughts on “Want to report on #ProjectOnline project site list data? Example using #PowerAutomate to push project site #SharePoint list data to #AzureSQLDatabase #PowerBI #Office365 #PPM #LessCodeMorePower

  1. I recreated this flow with some success! One hitch is that my GetAllProjectSites HTTP request is returning only a subset of sites – 300 of about 500. I can’t seem to determine why the results are limited. Any idea?

    1. Hello, Good to hear you have it working. This is due to the paging limits in the API’s as mentioned on the list of items to be handled before putting into production: “Update the flow to handle the SharePoint and Project Online API paging”

Comments are closed.

Blog at WordPress.com.

Up ↑

%d bloggers like this: