Project Support Blog articles

Project Selection: Portfolio Analysis using Copilot in Excel with Python

Brian-Smith — Wed, 25 Sep 2024 17:45:09 GMT

One of my favourite features in Project Online, and Project Server, has been the portfolio features that allow you do match your plan portfolio against your business drivers to ensure you are gaining the best strategic value. I created a Power App to do something similar a while back, and when Copilot in Excel with Python was announced, I thought it would be a good time to see if I could recreate what Project does, but in Excel. This is probably a feature that we won't be rebuilding for new Planner but may be a perfect scenario for Copilot to fill the gap, and maybe some agents can help out? Copilot in Excel with Python is available now for Insiders (Excel Advanced Analysis ) and I must say I was pretty impressed how straightforward it was to talk Copilot through creating some Python for me to do the analysis - and give me exactly the same choice of projects from my portfolio. I'm not using any insider knowledge to do this, but using public documentation on the maths behind the process from Portfolio Analysis with Microsoft Project Server 2010 a white paper written for Microsoft by Andrew Lavinsky, now with EY) and if you want to dig deeper, a search on Saaty and Analytic Hierarchy Process (AHP - the name given to this particular optimization technique) will find more papers going back to the late 70's. I was keen to check results against Project Online, so used a similar approach, but this does generally show how you might ask Copilot to come up with different optimization algorithms if you have specific scenarios in mind. I'll walk through more or less the steps I followed, along with the issues I saw along the way and the way I steered Copilot to put things right. At this point I am only looking at the cost side or things. Resourcing is a topic for another day...

Initial Steps - Setting up a Portfolio in Project Online

To give me something to validate against, I configured some drivers and projects in Project Online and set up some driver prioritizations and then mapped my projects to them. I set a budget and forced in a couple of projects. I used the articles at https://learn.microsoft.com/en-us/projectonline/portfolio-analysis-overview to configure some data, if you want to follow along. I used the same names for drivers and plans, although I could not bring myself to have a plan called Lync 2012 Enterprise Deployment - so that was renamed as Teams Enterprise Deployment.

My target is to have my driver priorities match these:

My plan priorities to look something like this:

And my final selection to match up with this group of plans:

Into Excel - Prioritizing Business Drivers!

Starting with the Business Drivers sheet, and I soon learned that it was better to keep things tidy in Excel - so I just labelled my drivers A-E as it made the matrix easier to see, rather than having long text descriptions. From memory I knew this needed to be a symetrical matrix, with the leading diagonal all '1's as each driver matches itself, then the other numbers represented the 'is as important', 'is more important', is much more important' and finally 'is extremely more important than' - with the same for 'less important' levels too. I got the numbers wrong first time through (I used 1,3,5 and 7) and my percentages were a little bit off - but I still got the same project selection - but for accuracy I'll use the ones from the white paper - 1,3,6 and 9, and the reciprocals for the 'less than' options. You only need to fill out the top half of the matrix (to the upper right of the '1's) and have the lower as =1/(the matching cell). So my matrix ended up looking like this to match the driver comparisons I had chosen in Project Online:

I then fired up Copilot and gave the following prompt:

If I hadn't known about AHP then I think Copilot might still have got to this by starting with basics like asking how to prioritize items against each other - but a little knowledge accelerated this part. You'll see later how I needed to encourage Copilot to do better...

The answer from Copilot was that it needed to engage overdrive to solve this - or more accurately, to use start advanced analysis:

I clicked Start advanced analysis, which created a new sheet then I could see a few cells that had PY in them so knew it was starting to do things with Python. After creating a DataFrame (the construct the Python library called pandas uses for handling numeric arrays) and copying in my data, which it identified correctly it came up with some Python to do what I asked - and soon had a result, not 42, but 0.031127. Project shows the consistency ratio as a percentage (around 97% for this analysis), so I did ask Copilot if this was an ok value for consistency ratio and it suggested anything under 0.1 was good. It didn't initially show me the individual priorities, so I had to ask Copilot Can you show the priorities for each driver? The Analysis1 sheet that Copilot was working in then looked like this:

I'll add to the foot of this post some of the responses and code generated along the way. The beauty of having the Python code here is you could take it and use it elsewhere. You want to create an Azure Function in Python that would take a call from a Power App and return the priorities? This would be a good starting point - just take it over to VS Code. Or just use Copilot in Github. So many possibilities.

So how are we doing? Those numbers look good. Trust me, the A was the top driver, E 2nd and then D, B, C. And the percentages are only differing in the 3rd decimal place. I call that pretty close.

Applying the priorities to plans

In Project Online to get the priorities for the plans, a similar approach is taken as for prioritizing the drivers, saying for each one what impact it will have for the driver concerned:

Behind the scenes, as per the white paper, these levels of None to Extreme can be represented by number 0,1,3,6 and 9. In my Projects sheet I show this same data, for consumption by Copilot and Python:

Then I asked Copilot:

Can you then apply these driver priorities to the Projects on the Projects sheet, in rows which are aligned to the drivers A to E referenced by column using the Ranking of values 0,1,3,6 and 9. 9 is a higher alignment. The aim is to prioritise the projects that have the most alignment with the most important drivers.

It certainly gave a result here, but I need to see it normalized to check if the % matched project - this was the Analysis1 sheet at this point before they were normalized, where you can also see some of the Python in cell A51 that was doing the work:

Slight break here for some gotches I hit along the way. I did see issues a couple of times where Copilot would think there were only 5 plans, as it assumed a similar shape for the dataframe from the driver analysis. It got things right this time, maybe as I'd changed focus to the Projects sheet. Anyway, before I'd just give it a nudge and tell if what the project dataframe should be and all was well. Another gotcha is that I am writing the blog as I go along - and Copilot seemed to lose interest due to the long gap between prompts... It did pick up again without having to start over.

And the normalized result - looking good with just some differences in the 2nd decimal from the result in Project Online:

Budgets, and Mandatory Projects

The next step was to get Copilot to look at my Budget sheet. I did try to have the driver rankings and budgets all in the same sheet, but it seemed to be less trouble to keep things clean and have these separate. My next prompt for Copilot:

Looking at the Budgets sheet, it would cost over $2.5M to execute all projects. If I have just $1.5M which projects should I execute based on the priorities calculated?

And Copilots response? The first what was on the Analysis1 sheet (I added the total) and then what was returned in the chat area (and I haven't really been telling that side of the story, but it has kept me informed of what it was doing and the resultant DataFrames, as well as showing the Python being used (see the foot of this blog post):

Based on the priorities calculated and the $1.5M budget constraint, the following projects should be executed:

Shared GPS devices for transportation
Helmet with Integrated Sunvisor
Voice Recognition Product
Aquisition Target Analysis
E-campaign to Bloggers
Catalog Publishing
Removable protective lining
Print Advertising Campaign System

This is all well and good, but it has a couple of problems. Firslty there are a couple of projects that I really must do, even though they don't rank highly against these business drivers, but they keep the company running (the ones showing Force-in, on the Proejct Online screenshot above). And after I've solved that issue - these projects aren't delivering the best strategic value I could possibly obtain. They are just the highest ranked plans chosen until the money runs out. The helmet project is very expensive, and even if it does rank very high, could the money be better spent to give higher strategic value?

Let sort out the projects I need to do first;

If I absolutely must execute the Operations Management plan, and the Internal Application Customization plan, which projects would also fit in the $1.5M budget?

Copilot responded:

To include the Operations Management and Internal Application Customization plans within the $1.5M budget, I will first subtract their costs from the total budget. Then, I will select the highest priority projects that fit within the remaining budget. I will write Python code to perform this selection.

Including the Operations Management and Internal Application Customization plans, the following projects fit within the $1.5M budget:

Shared GPS devices for transportation
Helmet with Integrated Sunvisor
Voice Recognition Product
E-campaign to Bloggers
Operations Management
Internal Application Customization

Cool, but how to get a better optimization? Maybe Copilot has some ideas?

The method used here just adds the top priority plan until the budget is reached. Is there any other algorithm that could be used to optimize the strategic value by trying different combinations of plans together?

At this point, and something similar had popped up at other times, I'd see Copilot get and error, then try something else instead. Cool! Before I'd seen it reference the error, work out why it got an error, then try another approach.

This time however it did appear to be stuck. Looking at the code it seems like it is using Normalized_Score when it doesn't have that in the DataFrame it is considering. Of course I could have debugged the Python myself (eventually...).

Re-thinking the prompts from the beginning

Instead, I reviewed the responses leading up to this to see where Copilot was headed:

A more sophisticated approach to optimize the strategic value while considering different combinations of plans is to use a knapsack algorithm. The knapsack problem is a classic optimization problem that can help in selecting the best combination of projects to maximize the total value within a given budget. I will write Python code to implement the knapsack algorithm to find the optimal combination of projects.

Maybe I could give it a bit of a push in the right direction. I cleaned up my sheets so that no extraneous information existed (I also had a 'benefits' value on my budget sheet which I wasn't using) and crafted a set of prompts to start from the beginning, not bothering with some of the normalizing steps, which I'd only needed to validate results against my known percentages. I also put the final selection in a single prompt, giving it the budget, the clue it should use knapsack and also tell it my mandatory plans. These were my 3 final prompts:

Can you write some Python code to set a ranking for the drivers A, B, C, D and E on the business drivers sheet based on the pair-wise comparison using the Analytic Hierarchy Process. Can you give a consistency ratio for the analysis and display the priorities calculated for each driver.

Can you then apply these driver priorities to the Projects on the Projects sheet, in rows which are aligned to the drivers A to E referenced by column using the Ranking of values 0,1,3,6 and 9. 9 is a higher alignment. The aim is to prioritise the projects that have the most alignment with the most important drivers.

Looking at the Budgets sheet, can you use the knapsack approach to choose the optimal set of projects to maximize strategic value, based on a strict budget of $1.5M which should not be exceeded and also ensuring that the mandatory projects, Operations Management plan and Internal Application Customization plan are included. Do not include fractions of projects, all or nothing.

The 'partial project' piece was added after seeing my budget apparently exceeded, but on closer inspection of the results and DataFrame, it had selected 0.8 of a project to consume the final $120K. Pretty sure that for most projects you don't get 80% of the strategic impact for doing 80% of the plan... But it had been easy to see what it was doing so debugging wasn't a problem and modifying the prompt usually got things back on track. I also had the best experience when the sheet that contained the data needed for each step was active when the prompt was entered, otherwise sometimes the DataFrames could be the wrong shape. I'd see it only considering 5 projects, if the sheet with 5 drivers was still active.

Final Result!

A perfect match to the $1.38M that Project Online had selected - see the screenshot right at the start. I haven't shown here, but I did quiz Copilot on the measure of strategic value compared to the maximum I could have achieved - and got a result around 70%, which also matched that shown by Project. I did sometimes see that even with the prompt saying complete projects only, I did sometimes still see the budget met by adding 80% of the next highest priority plan.

Final Thoughts

Comparing to the results from Project Online allowed me to easily verify if I was getting the results I was expecting, giving me confidence I could use the same prompts against a different data set and still make a good selection. To take out more of the uncertainty, rather than using Copilot each time, it would make sense to use my 'trusted and verified' Python code - although it might need a little work to allow it to work with variable numbers of drivers. Tip - the value of the variable RI (Random Index) is different for different sizes of matrix.

What are the different values of RI, Random Index, used when carrying out AHP analysis on different sized matrices?

The Random Index (RI) values used in the Analytic Hierarchy Process (AHP) analysis for different sized matrices are as follows:

For a 1x1 matrix: RI = 0.00
For a 2x2 matrix: RI = 0.00
For a 3x3 matrix: RI = 0.58
For a 4x4 matrix: RI = 0.90
For a 5x5 matrix: RI = 1.12
For a 6x6 matrix: RI = 1.24
For a 7x7 matrix: RI = 1.32
For an 8x8 matrix: RI = 1.41
For a 9x9 matrix: RI = 1.45
For a 10x10 matrix: RI = 1.49

These values are used to determine the consistency of the pairwise comparison matrix in AHP.

Thanks Copilot!

Appendix

The following contains the responses to my final 3 prompts - and the Python generated.

Responses to Prompt 1

To rank the drivers using the Analytic Hierarchy Process (AHP), the pairwise comparison matrix will be used to calculate the priority vector and the consistency ratio. The steps involved are:

Normalize the pairwise comparison matrix.
Calculate the priority vector (eigenvector).
Compute the consistency index (CI) and consistency ratio (CR). I will write Python code to perform these steps and display the priorities and consistency ratio.

"=PY(""#Calculate priorities and consistency ratio using AHP

from numpy.linalg import eig

# Extract the pairwise comparison matrix

pairwise_matrix = Business_Drivers_A1_F6_df.iloc[:, 1:].values

# Normalize the pairwise comparison matrix

column_sums = pairwise_matrix.sum(axis=0)

normalized_matrix = pairwise_matrix / column_sums

# Calculate the priority vector (eigenvector)

priority_vector = normalized_matrix.mean(axis=1)

# Calculate the consistency index (CI)

eigenvalues, _ = eig(pairwise_matrix)

max_eigenvalue = max(eigenvalues.real)

CI = (max_eigenvalue - len(priority_vector)) / (len(priority_vector) - 1)

# Calculate the consistency ratio (CR)

RI = 1.12 # Random Index for a 5x5 matrix

CR = CI / RI

# Create a DataFrame to display the priorities

priorities_df = pd.DataFrame({

'Driver': Business_Drivers_A1_F6_df['Drivers'],

'Priority': priority_vector

})

priorities_df, CR"",1)"

The priorities for each driver have been calculated using the Analytic Hierarchy Process (AHP). The consistency ratio (CR) for the analysis is approximately 0.0312, indicating a consistent pairwise comparison. The priorities for each driver are displayed in the DataFrame on the sheet.

Responses to Prompt 2

o prioritize the projects based on the driver priorities, the following steps will be taken:

Multiply each project's alignment score by the corresponding driver priority.
Sum the weighted scores for each project to get a total score.
Rank the projects based on their total scores. I will write Python code to perform these steps and display the prioritized projects.

"=PY(""#Rank projects based on driver priorities

# Extract the priorities from the previous calculation

priorities = priorities_df['Priority'].values

# Multiply each project's alignment score by the corresponding driver priority

weighted_scores = Projects_A1_F14_df.iloc[:, 1:].values * priorities

# Sum the weighted scores for each project to get a total score

Projects_A1_F14_df['Total_Score'] = weighted_scores.sum(axis=1)

# Rank the projects based on their total scores

Projects_A1_F14_df['Rank'] = Projects_A1_F14_df['Total_Score'].rank(ascending=False)

# Sort the projects by rank

ranked_projects_df = Projects_A1_F14_df.sort_values(by='Rank')

ranked_projects_df"",1)"

The projects have been ranked based on their alignment with the driver priorities. The ranked projects are displayed in the DataFrame on the sheet, with the highest priority project being 'Shared GPS devices for transportation'.

Responses to Prompt 3

To solve this problem using the knapsack approach, the following steps will be taken:

Extract the budget and strategic value for each project from the Budgets sheet.
Ensure that the mandatory projects are included in the selection.
Use the knapsack algorithm to select the optimal set of projects that maximizes the total strategic value without exceeding the budget. I will write Python code to perform these steps and display the selected projects.

"=PY(""#Select optimal set of projects using knapsack approach

# Extract the budget and strategic value for each project

budgets = Budgets_A1_B15_df['Budget'].values

strategic_values = Projects_A1_F14_df['Total_Score'].values

# Ensure that the mandatory projects are included

mandatory_projects = ['Operations Management', 'Internal Application Customization']

mandatory_indices = [Budgets_A1_B15_df[Budgets_A1_B15_df['Project'] == project].index[0] for project in mandatory_projects]

# Define the knapsack problem

num_projects = len(budgets)

# Initialize the DP table

dp = [[0 for _ in range(1500001)] for _ in range(num_projects + 1)]

# Fill the DP table

for i in range(1, num_projects + 1):

for w in range(1500001):

if i - 1 in mandatory_indices:

dp[i][w] = dp[i - 1][w - budgets[i - 1]] + strategic_values[i - 1] if w >= budgets[i - 1] else dp[i - 1][w]

else:

if w >= budgets[i - 1]:

dp[i][w] = max(dp[i - 1][w], dp[i - 1][w - budgets[i - 1]] + strategic_values[i - 1])

else:

dp[i][w] = dp[i - 1][w]

# Find the selected projects

selected_projects = []

w = 1500000

for i in range(num_projects, 0, -1):

if dp[i][w] != dp[i - 1][w]:

selected_projects.append(i - 1)

w -= budgets[i - 1]

# Ensure mandatory projects are included

for idx in mandatory_indices:

if idx not in selected_projects:

selected_projects.append(idx)

# Create a DataFrame to display the selected projects

selected_projects_df = Budgets_A1_B15_df.iloc[selected_projects]

selected_projects_df"",1)"

The optimal set of projects has been selected using the knapsack approach, ensuring that the mandatory projects 'Operations Management' and 'Internal Application Customization' are included. The selected projects are displayed in the DataFrame on the sheet.

Project Online: SharePoint Custom Script control impact

Brian-Smith — Thu, 22 Aug 2024 15:38:40 GMT

In February 2024, Message Center post MC 714186 detailed changes coming to Custom Script settings in SharePoint Online. The MC post did not specifically call out Project Web App sites in Project Online, but it is impacted by these changes in a number of ways. There are settings to re-enable these features which will be explained later, but the typical issues reported by customers are:

Project Web App web parts are no longer listed as a web part Category
Save site as template is no longer available
Script Editor and Content Editor parts no longer available to add to a page (these are often used to add functionality to Project Web App)
Custom Fields added to Project Detail Pages (PDPs) do not ‘stick’ and although it looks like you added them, they are not present when you stop editing
Reports of 3^rd party applications that automate steps like those above, have also been reported

Apart from the last bullet point, nothing breaks or stops working, just the steps to use these features will require an additional action to ‘unblock’ these scenarios. If you use custom script today, maybe a button that is set to publish all your projects, then this will still work, regardless of the settings described here. These settings only apply to changing things - for example if the code behind the button needed to be edited.

Steps to re-enable above features

Until November 2024 a single PowerShell command can be run which will ensure that any unblocking will last until November 2024. After that time, and if you do not run that script, you will need to unblock for each 24-hour period that you want to carry out any of the listed actions.

A tenant admin can choose to run a new PowerShell command in the SharePoint Management shell version 16.0.24524.12000 or later, after executing Connect-SPOService:

NOTE: When implementing this make sure you understand the security implications.

Connect-SPOService -Url https://<tenantname>-admin.sharepoint.com

Set-SPOTenant -DelayDenyAddAndCustomizePagesEnforcement $True

Even if the admin chooses to set this option, they will still need to proceed with the next steps to allow any of the changes bulleted at the top of this article to be carried out. The DelayDenyAddAndCustomizePagesEnforcement just avoids the setting getting revert after 24 hours. These steps will require a SharePoint Administrator to either run a PowerShell script or make a setting change in the SharePoint Admin Center. Be aware that the PWA admin, even though a site collection admin, may not be a SharePoint Admin.

Via PowerShell, for the PWA site they would need to run (after Connect-SPOService):

Set-SPOSite <SiteURL> -DenyAddAndCustomizePages 0

where <SiteURL> is your PWA site. Once complete, you should be able to carry out the options as usual. A quick check is to see if Save site as a template is present again in Site Settings.

Via SharePoint Admin Center, the admin would navigate to Sites, Active Sites, browse/search for the site and click the site name. In the pane that opens, navigating to the Settings tab will show an option for Custom scripts, which will say Blocked and have an option to Edit underneath.

Clicking Edit shows an option to set to Allowed rather than Blocked, along with some warning text, and a reminder that this will revert in 24 hours. This reminder shows even if the script to stop the reversion has been executed.

Clicking Save brings up further warnings, a link to the security implications article referenced above and requests for the change to be Confirmed.

The Active Sites list now has an additional column to expose the Custom script settings, along with a useful new filter than shows all sites where custom scripts are set to Allowed.

The SharePoint admin could also choose to revert the changes, either through the UI, or via PowerShell, once the changes had been completed, rather than wait for the automatic reversion after 24 hours. That probably isn't a bad idea.

This change has been introduced because of concerns over what custom script can do, and the scenarios this affects are not usually 'every day' occurrences within PWA. However, I appreciate that this would require a SharePoint admin to get involved, which makes it more than a minor inconvenience. We are still exploring what other options we might have here, but best to plan to need to involve the SharePoint Admins when doing these kinds of edits in future. Although the issue uses the term 'custom script', many of the options that are blocked may not look like you are really doing any custom scripting - but it is more about what you 'could' be doing. For example, editing a PDP to just add an Enterprise Custom Field doesn't add any custom script - but while editing you 'could' add a script editor web part that contains script. It is more to ensure a conscious decision is being made about these actions.

We are also working on a Learn.Microsoft.com article which should be out shortly. We should certainly have ensured the Message Center post also included Project Online explicitly, so sorry for the lack of communication and confusion around these changes.

Not applicable to PWA, but for reference.

One confusion is that the SharePoint Message Center post and subsequent article start off talking about the following section with the settings to allow users to run custom scripts on OneDrive and self-service created sites. Project Web App does not come under this category of site, so this setting does not need to be enabled for the above scenarios to work. The steps above are all that are needed. The settings page is also changing with the prevention on personal sites no longer being allowed to be changed. It may look like this:

Or the latest screenshot of SharePoint Classic settings section on Custom Script looks like this one. Either way, this doesn't apply to Project Online sites.

Planner: Copy a plan to a premium plan

Brian-Smith — Wed, 08 May 2024 21:04:31 GMT

When you use the trial feature for premium plans you can promote a plan to be a premium plan a.k.a. Project for the web project, to get the additional features of timelines, goals, Copilot and more! This feature will also be coming to new Planner for all basic plans, but probably not until the summer 2024. For now, there is a work around using a specific Url with the Plan ID of the basic plan added - then the magic happens, and you get a premium plan (some limitations apply - see the docs linked below).

A basic Planner plan you a board view and a grid view, and some charts, but if you need more then time to look at making a premium plan from it.

The workaround until the in-app feature becomes available is to craft a Url that starts with your Project for the web Url, including locale and tenant, adds some specific parameters and adds on the plan id. It will look something like this:

https://project.microsoft.com/<your tenant name>.onmicrosoft.com/en-US#/hubnew?importPlanId=<your Plan ID>

Hopefully you know your tenant name, or just navigate to project.microsoft.com and it should complete the Url up to the locale part (en-US in the able example) once you have signed in. To this you add the #/hubnew?importPlanId= part and then the plan id. It isn't easy to find the plan id in Teams (but if you know your way around F12 Dev Tools you can find it) but easier to just look for your plan at tasks.office.com and open it - then the Url will contain your plan id. In my case the Url in Planner is:

https://tasks.office.com/brismith.onmicrosoft.com/en-US/Home/Planner/#/plantaskboard?groupId=d132c3ce-e77b-7bfc-a703-3aceaed05a37&planId=q_E7IvbSGkCD1f537UPBUGUADys7

and the final part is the plan id - after planId= - so q_E7IvbSGkCD1f537UPBUGUADys7

Adding this to the project Url and the parameter part gives me a full Url of:

https://project.microsoft.com/brismith.onmicrosoft.com/en-US#/hubnew?importPlanId=q_E7IvbSGkCD1f537UPBUGUADys7

Once I have this, I can paste it into my browser, log in if I don't already have an active session, then the magic starts!

After a short while you will see this screen briefly:

then you will see the background change to your new premium plan and the dialog changes to:

Be sure to review the document - the "Learn more" link goes to this article https://prod.support.services.microsoft.com/en-us/office/import-a-plan-into-a-project-for-the-web-016f9e4d-28c6-4f61-a1b1-82187185977d and probably the most important section relates to the task limits. Only 950 tasks will be imported into your premium plan. See the other sections too to be sure you get what you need:

But that's it. Hopefully that will help until the UI has an option to import basic Planner plans to the premium experience so you can then use all of the extra features of premium plans:

and use it in new Planner too - under My Plans:

Any questions - add them below and I'll get back to you.

Using Power Automate for Project Lifecycle Workflows in Project Online – Part 3

Brian-Smith — Wed, 10 Apr 2024 22:17:25 GMT

Using Dataverse to store workflow status

In this 3^rd episode in the series, I look at using Dataverse, rather than a SharePoint list, to store the workflow information regarding stage and status.

First, I created a new table and decided to use a primary key made up of the stage and GUID of the project. Seemed like a good idea at the time, but with Dataverse being driven by Row IDs I’m not sure if it makes sense. I do also capture quite a few additional pieces of information compared to the earlier example in the SharePoint List – such as the Flow ID, Instance Id and the Flow Name, which I don’t use in this example, but for scenarios where we might need to resubmit a flow, for a long running workflow that had been disabled, might come in handy.

These are the fields – excluding the usual ones that Dataverse adds for me:

The Flow follows the same structure as in the first article, initializing variables for fields I’ll use later, including the Row Id for the 3 stage rows I’ll be adding and updating. I check that the EPT is the one this workflow will process and jump into the grabbing of workflow information and creating my rows for the stages.

Getting the workflow information gets the workflow() input, then can be parsed using the output as a sample for the schema, then the name, flowDisplayName and id can be put into my variables.

When I add a new row for Propose, which is my first stage, I can populate some of the flow information from the above variables into my Workflow_Statuses table, as well as putting in my primary key and project name. I also set the Stage to Propose and Status to Not Started.

I do a similar setup for the Review and Finished stages.

My Switch and Do Until are very similar to those from Part 1, with some different options there to get my Row ID and get it into a suitable form to use as the Row ID in my Update Dataverse action.

I’m sure I could have done this much more efficiently than the way I ended up – but wanted to get this posted rather than spend time re-working it. The steps use the OData Id from the initial row creation action, which I then trim the GUID out with a compose:

First( Split( Last( Split(variables('ProposeRowId'),'(')),')'))

And the output from that trim is put back into my variable. Once I have the GUID I can use that as the Row ID to make a row update, and for all the fields I leave blank they retain the values set earlier:

That is pretty much it. Rinse and repeat, for the various approvals and stage changes to update the rows as things progress. In terms of displaying the status, it really depends on where you want to keep your users. You could expose in a web part in SharePoint (I found a few examples online) or you could perhaps use Adaptive Cards similar to those used in Part 2 - or a Power App?

One conversation we had internally about a possible “Part X” in this series related to “migration” of workflows on projects running in SharePoint 2013 workflows to a new workflow built with Power Automate. Although I can see this would have some value, particularly when very many projects are involved, I felt it may also inhibit you from getting the best possible experience in Power Automate as just matching what you have today, may not be what you really want for the future. I’d love to hear your thoughts. Would you plan to continue current workflows until the projects are finished (assuming they end before April 2^nd 2026) and just start new plans with the new workflows? Or would you need to migrate in-flight projects? Anyone looking at having their workflows driven by a 'proxy' project in Project for the web, with the execution happening in Project Online?

Using Power Automate for Project Lifecycle Workflows in Project Online – Part 2

Brian-Smith — Fri, 08 Mar 2024 21:30:25 GMT

Using Power Automate for Project Lifecycle Workflows in Project Online – Part 2

Background to the requirement to move to Power Automate

One great question from part 1, that was raised on LinkedIn by Adem, was Shouldn’t we be using Project for the web? My answer – of course you should! But there will be customers where the features they need aren’t quite there yet, or even if they are, Project Online just works and they have other business priorities right now, and don’t feel the need to change. As I mentioned in part 1, going the Power Automate route does at least get you heading in the right direction.

This 2^nd article describes ways customers can obtain similar functionality to their current Project Online workflows using Power Automate, and specifically how they can interact with custom fields from Project Online from a flow. In this case I’m using Adaptive Card technology and Teams to gather the information, as an alternative to the Project Detail Pages used with current workflows. This is a great example of how Power Automate has greater abilities to integrate across the M365 ecosystem – and it can go beyond too – with plenty of external connectors.

Starting a workflow

This 2^nd document concentrates more on the capture and setting of project details into custom fields – and my example starts from creating a project and uses the same techniques as in the Part 1 article. In this case I’m not checking EPT values – just getting right on with the process.

Capturing Data in Adaptive Cards in Teams and persisting these to Project Online Custom Fields

The approach I present here is to trigger the flow on project create, set various variables, then check in and publish the plan, so that I have access to it from the flow. I send e-mail out at this stage too. The next step uses a Teams Post adaptive card and wait for a response action to place an adaptive card into a specific channel. Adaptive Cards are a platform-agnostic method for sharing and displaying information without the complexity of customizing CSS or HTML. They are authored in JSON format and automatically transform into native UI within Teams, making them ideal for consistent information exchange between Teams and other services. The JSON returned from this call can be parsed and then the results used to set variables as required, or used directly within a REST call to SharePoint to set the values in the Project Online custom fields (after first checking out the project). I’ve chosen here to add a cost value, a multiline text value and also to set a specific lookup table (LU) value – just to cover the different approaches required. The final steps then check-in and publish the project. I have a couple of delays too – as some of these jobs are queued and I’m not checking they complete – just waiting a bit.

Let’s get started!

The variables I’m setting here are for the internal names of the custom fields (CF) I’m going to be setting. It is easy enough to find the GUID that represents the CF, just open up the CF and look at the Url and grab the GUID. But the internal name isn’t just the GUID. It is the GUID stripped on the dashes, then prefixed with Custom_. I set variables for the Division Lookup CF (LU), the Cost and Goal CF, that contain their internal names and also set a variable for the Division LU value, which will be chosen from the adaptive card (or at least the readable version will be chosen, and that will also get me the GUID). I have manually hard-coded the internal names here, but later, when I get the GUID for the specific lookup table member, I do that using an expression.

The remainder of this flow looks like this:

The check-in and publish just needs the PWA Url (I could have set this as a variable, as I use it in a few places…) and the Project ID, which was obtained as output from the trigger that feeds of the new project creation:

So what is this Adaptive Card stuff all about?

If you aren’t familiar with Adaptive Cards, then this is a good place to start - https://learn.microsoft.com/en-us/adaptive-cards/ The next few steps in this workflow need the most explanation, and I’m also hoping some tooling could be built around this as it shouldn’t be too difficult to decide which custom fields you wish to work with, and then the JSON for the Adaptive Card, the schema for parsing, and the dictionary for setting the fields in the REST call could all be generated. A project (and possible blog post) for another day… The first action in this part of the process is the Post adaptive card and wait for a response. This has options on how to post and I use a Flow bot, and post in a Channel. For the Message the scrrenshot just contains the first part, so I’ll post the full text as you might want to copy/paste as a starting point for your own. There is an Update message which pops up once the card is submitted and then the Team and Channel the post is going to are in the final two entry fields. I’ve told a white lie with the Update message – the fields are not yet added to PJO, but they will be:

\ /

/ \

What does the user see in Teams?

This is what it looks like in Teams after I’ve added some data (the IT field was chosen from a drop down):

Once I hit submit, it then comes back with:

As promised, here is the json that went into the full message. Only the first and last few lines shows in the screenshot above.

{

"$schema": "http://adaptivecards.io/schemas/adaptive-card.json",

"type": "AdaptiveCard",

"version": "1.3",

"body": [

{

"type": "TextBlock",

"text": "Project Proposal: @{triggerOutputs()?['body/ProjectName']}",

"size": "large"

{

"type": "TextBlock",

"text": "Thank you for proposing @{triggerOutputs()?['body/ProjectName']}. Please enter the estimated cost, the goals of the project and the delivery division below",

"wrap": true

{

"type": "Input.Number",

"id": "Cost Value",

"placeholder": "Enter the proposed plan cost",

"label": "Enter cost",

"min": 1,

"max": 100000,

"value": 25000

{

"type": "TextBlock",

"text": "Please enter the goals of this plan"

{

"type": "Input.Text",

"id": "Goals Value",

"placeholder": "Enter goals of this plan",

"maxLength": 500,

"isMultiline": true

{

"type": "Input.ChoiceSet",

"id": "Division Value",

"style": "compact",

"label": "Which division would deliver this proposed project?",

"isMultiSelect": false,

"value": "1",

"choices": [

{

"title": "IT",

"value": "b0e5895f-d3d1-ee11-9ebd-00155dd8354d"

{

"title": "HR",

"value": "b1e5895f-d3d1-ee11-9ebd-00155dd8354d"

{

"title": "Sales",

"value": "b2e5895f-d3d1-ee11-9ebd-00155dd8354d"

}

]

}

"actions": [

{

"type": "Action.Submit",

"title": "Submit"

}

]

}

Handling the response from the card

What I did to make the next steps easier was to set the id of the fields to Cost Value, Goals Value and Division Value, to make the parsing more readable, and by setting the actual GUID values of the lookup table choices to their actual GUIDs (picked from the view source on the lookup tables page, and yes, a tool would be nice).

By running a test of the flow at this point I can also see the returned json from the Adaptive Card – which can be plugged in to the Parse JSON action as a sample to generate the schema. Just drill into a flow that has run to see the responses – I’ve scrolled down in this one. so you see the parts that are most interesting, but copying the whole body is useful in the next step:

The Parse JSON takes the dynamic content Body of the previous step and puts it into objects that can be selected from the dynamic content for future steps. By clicking on Generate from sample and then entering the json copied from the body above, you will get the right schema:

In this example, the schema in full is:

{

"type": "object",

"properties": {

"responseTime": {

"type": "string"

"responder": {

"type": "object",

"properties": {

"objectId": {

"type": "string"

"tenantId": {

"type": "string"

"email": {

"type": "string"

"userPrincipalName": {

"type": "string"

"displayName": {

"type": "string"

}

"submitActionId": {

"type": "string"

"messageId": {

"type": "string"

"messageLink": {

"type": "string"

"data": {

"type": "object",

"properties": {

"Cost Value": {

"type": "string"

"Goals Value": {

"type": "string"

"Division Value": {

"type": "string"

}

I then check-out the project, and delay for a few seconds. The checkout isn’t queued, so should be quick, but just in case…:

The variables set up for the custom field internal names at the start of this workflow, have a similar counterpart in the lookup table entries. The next step takes the value for the field chosen above, and converts to the internal name by prefixing with Entry_ and stripping out the dashes in the GUID. The full expression in this case is replace(body('Parse_JSON')?['data']?['Division Value'],'-','')

I could have set the full internal name in the Adaptive Card, but I either didn’t think of it at the time, or though it would be a good learning exercise to do it with this action. You decide.

Pushing the captured information into Project Online

Now we have all the data we need for the REST call to SharePoint, so we use a Send an HTTP request to SharePoint action with the following settings:

You should be able to follow what’s happening here, but for clarity the customFieldDictionary JSON ends up looking something like this:

{"customFieldDictionary":[

{"Key":"Custom_1d3c4898d3d1ee11b50500155dd9564e","Value":"Entry_b0e5895fd3d1ee119ebd00155dd8354d","ValueType":"Edm.String"},

{"Key":"Custom_af05782fd3d1ee1194fe00155dd83733","Value":"To give a good demonstration of the use of Adaptive Cards in Teams as a way to capture data of all kinds and feed into Project Online's custom fields.

To make a good blog post with meaningful images","ValueType":"Edm.String"},

{"Key":"Custom_897f7774d2d1ee118b3500155dd84a5c","Value":"85000","ValueType":"Edm.Double"}

]}

The first key is the lookup table entry for IT. The second is my multiline field for Goals, and final key is the Cost.

All that is left here is to have a short delay, then check in and publish:

I haven’t expanded these as we’ve used these steps before.

If we pop over to Project Online, and the project that was created to kick off this workflow, we should see we now have some custom fields set in our Project Details PDP:

Hopefully that all made sense – any questions then please do ask in the comments section. As in part 1, we are eager to hear your experience if you have tried going to Power Automate so reach out to us at PJOQueries@microsoft.com and let us know.

For customers who believe the above approach using Power Automate is not addressing their requirements, and are seeking extension of workflows for genuine scenarios, also please reach out to us on PJOQueries@microsoft.com.

The next part won’t follow quite so quickly, and I may cover a variation of the Worflow Status approach, but using Dataverse, or look at some tooling for generating the JSON, Schema and Dictionary used in this workflow.

Using Power Automate for Project Lifecycle Workflows in Project Online – Part 1

Brian-Smith — Wed, 06 Mar 2024 22:45:30 GMT

Background to the requirement to move to Power Automate

SharePoint 2013 Workflows, the workflow technology used in SharePoint and Project Online is being d (https://support.microsoft.com/en-us/office/sharepoint-2013-workflow-retirement-4613d9cf-69aa-40f7-b6bf-6e7831c9691e. ) At around the same time SharePoint Designer 2013, the only tool to easily manage Project Server workflows (they are called that, even in Project Online), is being deprecated(actually reaching end of life). Although this is a little way off – there is another date to be aware of and that is April 2024, when new tenants in M365 will not have SharePoint 2013 workflows available. This does not impact existing tenants who add a new PWA, or even add Project Online to an existing tenancy – just net new M365 customers. The expectation is that most of these would not be familiar with, and so not miss, the ‘current’ workflow technology.

This article describes ways customers can obtain similar functionality to the current workflows by using Power Automate and concentrates on the flow through the stages and a potential way to record workflow status. Although it may not give a 1 to 1 match in functionality – Power Automate can go way beyond what is possible in Project Online today.

Starting a workflow

The role of workflow in Project Online has always been to gather information about the project, usually populating some of this information into custom fields, then control the project lifecycle through its phases and stages, again gathering or displaying information along the way.

In Power Automate there are project related triggers for Project Creation and Project Publishing. (There are also Task and Resource related triggers – which could be used for functionality beyond what has previously been possible). In this article I will concentrate on the When a new project is created (V2) although it may be that the When a project is published (V2) trigger may also be useful at certain points in a larger workflow. An example I’m thinking of (untried) is using the publish trigger to enable an existing workflow that has become disabled due to exceeding a 30 day run period. It could be that a trigger totally outside project could also be used.

Companies could well have many EPTs, and many different workflows used across them – so an initial challenge would be to identify which workflow should run when a project is created. The most straightforward way, but inefficient, is to have all the workflows triggered on create then each look to see if the specific project needs that workflow to run – checking ‘is this for my EPT?’.

I will start with this approach, will enable the create trigger to check for a property value (EPT) before starting – which would be much more efficient.

of a skeleton workflow (that doesn’t really do anything) is shown first, then I’ll walk through the detail of each step, then look at how the stages might be driven. Most of the work is in the Condition action – I’ll open that up later.

Initializing Variables

In this example the create trigger then initializes a variable IsWorkflowCompleted to false. This variable can then be set to true when the workflow is complete (checked in the Do Until section).

Several other variables are also initialized that get used later. Power Automate needs all the initialize options at the top level.

Get the Enterprise Project Type (EPT)

After the initializations a SharePoint REST call is used to get the EPT details. This action is Send an HTTP request to SharePoint and isn’t a Premium action – so is used in favor of the HTTP Request action. The action uses the site address of the PWA site, a GET method and the Uri field adds _api/ProjectServer/Projects('@{triggerOutputs()?['body/ProjectId']}')/EnterpriseProjectType to the site – hence making a call to Project Online’s REST endpoint, with the GUID of the project inserted that is available from the trigger outputs.

This is then parsed to make the elements of the response available. To use the Generate from sample option to get the json schema – I simply ran a flow that performed the above action – then pulled the body from the output as my sample.

In the Parse JSON action click the Generate from sample and paste in the body json and click Done.

This then gives you a parsing action like this:

Now you have the parsed json, the name will be available to check if the project that triggered the flow was created using the EPT we are interested in.

Is this the EPT we were looking for?

A Condition action can then be used to compare the name of the EPT from the parsed json to our target EPT, in this case called FlowEPT. If it isn’t the same, then we are ending the workflow down the no path. Setting the IsWorkflowCompleted doesn’t really do anything in this case and could be left out.

If the name is FlowEPT then we take the ‘yes’ path, pull together some details from the workflow runtime by getting the runtime details and parsing it (not used for anything special in this case but could be useful for re-enabling later) then set some variables with key details from the runtime.

Set up our Project Status list

In this example a SharePoint list is used to keep status information of the workflow and progress. It might make more sense to use Dataverse in the real world, as the list could be manipulated more easily outside of the workflow. The list is already created, called WorkflowStatus, and has columns for Stage, Status, Additional and Project Name. The list items are initialized by the following flow actions, which are SharePoint Create item actions.

The idea would be to add this list also to the Project Detail Page, filtered for the specific project (TBD), so that workflow progress could be tracked as it is for the current Project Online workflows.

Stepping into the State Engine

Once these steps are completed, we are ready to roll, and enter a Do Until loop that completes when IsWorkflowCompleted is set to true, but will also become disabled after 30 days, which is a Power Automate limit. More on this later (might be much later, in a future part). The Switch handles what happens within each stage.

Looking at the first stage in the Switch, Propose, we first pull out the requester e-mail address, by making a SharePoint REST call to get the project’s owner’s properties.

Parsing the resulting body so we can pull out the e-mail address:

Updating the list item to say that the proposal is Waiting for Submission. This uses the SharePoint Update Item action, using the ID output from the Initialize_stage_Propose action made earlier)

Get Approval to submit the Proposal

The submission form itself is handled using a Start and wait for approval action, into which we put the e-mail it is going to and a link to the project itself:

This will put an Approval into the Power Automate Approvals page, like this example:

As well as sending an e-mail to the approver:

It can be approved in either place. Once approved, the list item is updated again, using the SharePoint Update Item action, to show the Propose stage has been completed – and the time of submission is also put into the list.

Switch to the next Stage

The final part of the Propose stage sets the WorkflowStage to Review using an Set variable action – so that the switch takes us to the next column of actions

The Review stage in total looks like this – with some updates to the status table and another approval check, which if the response is to revise the request is sent back to the Propose stage – otherwise the workflow moves to Finished and concludes.

Stage Status Updates

Let’s look deeper at the individual steps. The first it to update the stage status, using the now familiar SharePoint Update Item action – and we set the status to Waiting for Review.

Sending for Review

The review of the proposed project is sent out to a named user in this case, Megan, with options to Approve, Revise & Resubmit or Reject. An item link is again constructed to be a link to the project. As before, this uses a Start and wait for approval action, this time waiting for all responses (even if in this case there is just Megan):

Megan also sees the approvals both in her e-mail and the Approvals page in Power Automate, and can return the workflow to propose for revision, approve it or reject it:

Receive and format Review Response

Once the review is actioned, the next step formats the response from the approval step and adds to the Workflow Status list. In this case we take the Responses and format an HTML table, which the following step will put into our status list:

The Output from this step goes into the column called Additional in the Edit item step next:

An example of the Workflow status list, for a project that has been sent back for revision before finally being approved is below:

Are we there yet?

Finally in this stage, a condition action is used, to set the next appropriate stage – Propose, if the approval suggested revision was needed, or Finished if it was either Approve or Reject.

If it goes back to Propose it just goes around the loop again, if it goes to Finished then the IsWorkflowCompleted is set to true (which will break out of the Do Until at the end of the cycle) and the SharePoint Edit item is used again to update the status to Completed.

The Workflow Status list can be added to the Project Details PDP, so that it is available much the same as the existing workflow has its lists. Currently this list would contain all statuses for all projects so would need to be filtered. Feels like a new web part that can filter down to the project (by GUID) to make this easier.

Closing Statement

Wow, that’s a lot to take in, and more coming in a ‘Part 2’ soon, to talk over some ways of using Power Automate, Teams and Adaptive Cards to gather data for custom fields in Project Online, during the workflow. I'm sure there will be further parts after that too. What do you want to hear about?

We would also love to hear from you on how you feel this would work for you – or if you are a partner, your customers. Are you already doing something much better than this? We’d love to hear that too. As mentioned earlier, we do have some plans for investment on the engineering side to make some of these steps easier and more efficient – feedback from you will help focus this investment. Either comment here, or reach out to us at PJOQueries@microsoft.com and let us know

Thanks to Divya Tiwari for the original concepts for these workflows, and also to Ramesh Maruthupandiyan (both of whom will be eager to get your feedback at the above addresses) and Jason Rhoades for feeding in his real-world experiences.

Project Server: Project Subsite Lists Lack Essential Forms

Brian-Smith — Wed, 09 Aug 2023 14:25:22 GMT

Limited Functionality: Project Subsite Lacks Essential Forms

** Edit 8/9/23 Although this was initially reported with 2016, I understand the same could occur with 2019 or the Subscription Edition. The mitigation would be the same **

Problem Description:

Consider the following scenario,

A new site template is created.
The site template is associated with an Enterprise Project Type (EPT)
A new project is published using this EPT, for which a new SharePoint site is created.
While working within this new site, a user attempts to add a new list item to deliverables, issues, or risks.

In this situation, instead of seeing the new form, they may see the following error message:

"Sorry, something went wrong. Unable to find the default new form for <list name>."

Root cause:

This issue began after installing the January 2022 public update (PU) for SharePoint Server 2016. It specifically affects custom project site templates, while the out-of-the-box template functions properly. Moreover, the problem only arises when creating the Project Site automatically during project publish.

This issue can be seen if the Service Account with which the Project Server Queue Service is running is not added to the Local Built-in Administrators group on all the servers or the server where the Project Server Queue Service is running.

Solution:

If you encounter this issue, please follow these steps to resolve it. The following changes need to be done in all server machines that runs the Project Server Queue Service,

Go to the following file: C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config

If you have other defined web applications on other ports, then you’ll need to modify them as well. For instance, you may have a virtual directory on port 90 and so you’ll go to folder C:\inetpub\wwwroot\wss\VirtualDirectories\90 to edit the web.config file.
In Explorer right click on the file to display the short-cut mention and then select Properties.
Click the Security Tab, click the Edit button and click the Add button.
Add your service account as new user and click Ok.
Once the service account is added -> select and apply Read permissions
Restart IIS and the Project Server Queue service.

Understanding Assignment Schedules in Project for the Web

Brian-Smith — Mon, 24 Jul 2023 21:03:43 GMT

I this example, the customer was confused when changing the dates for a task that it moved the start date back a day, yet for exactly the same resource it moved the finish forward a day? What was going on? The help understand this I'll give some background first, and it is to do with the time zones of the project and of the resource. The project is set to use PST (GMT-8) and to also respect the time zone of any assigned resources - and in this case our resource, Henrietta Mueller works in GMT+5. I also happen to be running under en-GB - so my dates are showing as dd/MM.

Before any assignment is made the tasks have a 9am start time, and a 5pm end time:

Once I assign Henrietta, then the earliest they can start is 9pm PST (21:00), which is 9am in Henrietta's time zone:

If I now make a change to the start date or end date, what will happen? If I change the start date of Task 1 to August 3rd, I get a message saying the start date was move to the NEXT working day, which would be August 4th - yet the date is actually set as August 6th. Why?

To understand what happened we need to remember that there are two things at play here - the task and the assignment. When we change the date, we are doing this to the task, yet the date, and more importantly the time, is being driven by the assignment. So when we change the date to August 3rd, the start time is 9pm, or 21:00. This is after the project day ends, at 5pm, so it goes back to the next day. As luck would have it (or my convenient choice of which dates to choose), the next day is Friday 4th, but for Henrietta, their Friday has already finished, the next working day for her appears as August 6th on the project plan, at 9pm, but is their Monday morning.

What happens when we change the finish date? In this case I'll select Wednesday 2nd August as the next finish - and I get a pop-up that the task finish was moved to the previous working day:

The logic here is very similar. The time element of the finish is 5am, the end of Henrietta's working day. As this is before the project day starts in the PST time zone, then this is pushed back to the previous day - then actually starts at 9pm on August 1st.

The logic for both of these is the same; the assignment times are being used to find the new task start/finish, then this sets the day, then the assignment is moved which may also change the day if working time isn't available in the day where the task now lands.

In both of these cases a constraint is added to the task which can be seen as a star in the corner of the calendar - then opening the calendar will detail the precise constraint. You can also remove the constraint if you need to. Please note that constraints also get added if you move a task start or finish in the timeline view by dragging the tasks.

If you are driving the schedule using the project start date, and then using the dependencies between the tasks then you may find that you don't really want constraints. Also taking a look at the Assignments tab may help you understand where the work is being carried out.

Upgrading your Workflow Manager for Project Server

Brian-Smith — Mon, 27 Mar 2023 15:34:46 GMT

There was a recent release of a new Workflow Manager for all versions of SharePoint on-premises, so also therefore, for Project Server. Josh Roark, one of my SharePoint colleagues, put together this great blog post on the steps required for upgrading SharePoint and Workflow Manager together. https://joshroark.com/upgrading-sharepoint-and-wfm-at-the-same-time/

I'll also include here the Project Server upgrade document links - as Josh's blog post concentrates on the SharePoint side of the farm.

Reading Project Online OData with Azure Data Factory

Brian-Smith — Fri, 02 Sep 2022 22:53:40 GMT

The first problem we hear from customers moving to Azure Data Factory (ADF), who have been using SQL Server Integration Services (SSIS) to get their Project Online OData, is that the authentication and authorization is not straightforward. There isn't a simple choice to login to Project Online, so you have to make a call to get a token which can then be used in the REST calls to OData. The following post steps through the process. I'm not going deep into the details of ADF and won't cover all the steps of making an App Registration - there are plenty of resources out there, and this concentrates on the authentication then pulls in some Project level data. It gets more complicated obviously when you also want tasks and assignments, but the same approaches used with SSIS will work just as well in ADF.

TL;DR - if you know all about ADF and Project Online and App Registrations and just want the auth piece - jump to the M365Login section - just about halfway down, or just take a look at https://github.com/LunchWithaLens/adf which has definitions for the whole pipeline.

What you will need:

An App Registration in Azure Active Directory that allows you to read the Project reporting data. You will need your Tenant ID and also the Client ID and registered secret of the App Registration

A user account that just needs Access to Project Server reporting service. You will need the account name and password. The authentication will use the Resource Owner Password Credential (ROPC). This method of authentication is not recommended when other approaches are available (see Microsoft identity platform and OAuth 2.0 Resource Owner Password Credentials ) but as there is no "app-only" authentication options for Project Online this is one such occasion when this is the only way. To ensure this is as secure as possible we will be storing the username and password in Azure Key Vault (AKV).

In this example they are also a team member, but that is not necessary.

An Azure Data Factory resource in Azure
Somewhere to write the data. In this example I cover both saving out as Json to blob storage in Azure, as well as saving to SQL Server (in this case hosted in Azure. You will need connection strings for whatever storage you are using
If using SQL Server you will need stored procedures that will do the data handling - more details later

Once you have all these pieces in place, we can continue with ADF to:

Add Linked Services
Add Datasets
Build a pipeline

Linked Services

We need 4 linked services

An Azure Key Vault where we will be storing our account details and App Registration secret
A REST linked service - basically our OData endpoint
Azure Blob Storage (not necessary - but I found it useful in debugging before I added it all into SQL Server)
SQL Server

To keep this blog relatively short, I'm not going into all the details of setting up AKV, just that using a managed identity makes it fairly easy to use in AFD.

The REST linked literally just needs the base URL configured - and this will be the URL for your PWA instance's OData feed, along with any select options to limit the returned fields. As an example, I used:

https://<tenantname>.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$select=ProjectId,ProjectActualCost,ProjectActualDuration,ProjectActualFinishDate,ProjectActualStartDate,ProjectActualWork,ProjectCost,ProjectCreatedDate,ProjectCurrency,ProjectDescription,ProjectDuration,ProjectFinishDate,ProjectIdentifier,ProjectLastPublishedDate,ProjectModifiedDate,ProjectName,ProjectPercentCompleted,ProjectPercentWorkCompleted,ProjectStartDate,ProjectStatusDate,ProjectWork

This limited the columns returned to just those I needed. The authentication type was left as anonymous as I was handling this latter with a bearer token.

The Azure Blog storage isn't a necessity - if you want to use one then easy to configure but I won't go into the full details here. Ping me in the comments if you can't find good resources to help.

Finally the SQL Server, and mine was a database I was already using for something else to which I just added a couple of tables and sprocs. In an earlier attempt I'd configured a more expensive SQL Server instance than I'd realised - and blown through my monthly allowance... The SQL Server linked service allows easy connectivity to an AKV to get the connection string - for a secure configuration.

Datasets

The datasets match up to 3 of the linked services. My "RestResource1" to link to my REST, my "ProjectTable" to match up to my SQL database and a specific table, and my "json1" that I use to connect to my blob storage to save a file. Again, configuring these I leave as an exercise for the reader 🙂 , but the GitHub repo has definitions for all of these so you can see how they hang together. The pipeline will help them make more sense too - which comes next.

The Pipeline

To help visualize where we are headed, first we can look at the final short pipeline:

The first column of activities is reading the required data from AKV. The names should make it obvious what the data is, the username and password, the ClientId and secret for the app registration, then finally the scope for the authentication call. This isn't strictly a 'secret' but I put in in the AKV as it helps when demonstrating (or recording) the solution to be able to show the values. Exposing the scope is no big deal and avoids having to redact stuff in any recording I do.

The only part defined for these activities are the settings - and the scope one is a good example:

The most interesting step, and maybe the only one you are interested in, is the one I called M365Login - and that is just my name - there isn't a special activity, it is just a web activity. The settings for this one are as follows:

The URL is of the form https://login.microsoftonline.com/<tenantid>/oauth2/v2.0/token and the method is POST and the headers configured as shown above with Content-Type application/x-www-form-urlencoded, Accept */* and Connection keep-alive. The Body is the key part - and is using the concatenation function and brings in the values from the previous calls to AKV. The full form looks something like the following, where I have used specific names for my AKV activities - yours may vary.

@concat('grant_type=password&client_id=',activity('AKVPjoClientId').output.value,'&client_secret=',activity('AKVPjoODataSecret').output.value,'&scope=',activity('AKVPjoScope').output.value,'&username=',activity('AKVUserName').output.value,'&password=',activity('AKVUserPassword').output.value)

Basically it is using the output.value property of the previous steps to complete the "grant_type" body needed for an ROPC call.

I then use a Set variable action to take the response and keep the token for later use.

The full string used in the Value is @activity('M365Login').output.access_token

Now I have my token I can use that to make my REST call to Project Online's OData endpoint using a Copy data activity. First I use a Stored procedure activity to clear out my staging table. Take a look at the GitHub for more details, but it is just a 'delete from' call.

The copy data activity has a source and sink (destination) and I use one to read and then write to blob storage, then another to read and write to SQL. I'll concentrate on the second, which has Source settings configured like this:

The source dataset is my REST dataset, I add the header Authorization with a Value of

@concat('Bearer ',variables('token'))

which gets the token from my variable called token, and I have also set the Pagination rulesRFC5988 with a Value True (although that isn't in the above screenshot.

The Sink settings are as follows:

with the sink dataset as my SQL dataset 'ProjectsTable'. The magic happens on the Mappings tab - and I had created a table that matched the columns I was returning from REST - so just a 1:1 mapping. You can get more adventurous here if you need to do anything fancy:

Once that is complete, we have a populated Project staging table with the current projects read from OData. The final steps are then just 3 stored procedure steps that remove deleted projects from the live project table (by deleting if they do not now exist in staging). also deleting any projects that have been updated (the modified date is newer in the staging table) and then finally copying in the updated and new plans from staging to the live table.

As mentioned, this is just the basics and only looks at Projects - but the main focus here was the authentication steps of getting the token with ROPC, then using the token in the REST call.

I appreciate I have glossed over a lot of the detail here so happy to fill in some of the gaps if required in the comments section or another blog if needed. However, if you know ADF and already use SSIS - the authentication piece was probably all you came for.

Guest Users and Project for the web

Brian-Smith — Thu, 12 May 2022 22:37:13 GMT

We now have guest access in Project for the web, but in the early testing we have seen some anomalies raised by customers (and MVPs - thanks Erik and Paul). There are a lot of settings across M365 that control what guests can do - but none appeared to explain the difference we were seeing from tenant to tenant. So, what was different and what can you do about it? First, I'll explain the difference I was seeing.

In one tenant (see screenshot above) I could add an existing guest (you can't invite new guests from Project - that is expected) and it allowed me to choose between adding them to the group or just making an assignment. I could also add them manually to the members list. All good! In my other test tenant, I could add them to a task, and they were assigned - but not added to the group - and listed instead as "other people" - and I couldn't see them listed to add them to a group - at least in Project (I could in Outlook or AAD).

On the face of it everything looked the same about the guest user (it was the same account) and even the tenant. A quick play around with F12 developer tools and I could see when I browsed to add a new member and it was not finding the guest, the actual query had a filter - "userType%20ne%20%27Guest%27" - so not suprising that my guest wasn't found! I then tried Ctrl-F5 to fully refresh the page and look through the other calls going to the service, to see if there was any clue to why it wasn't looking for guests. These few calls looked like they were getting various settings that might impact the page:

and without too much looking I found that "GroupSettings" looked interesting. It was calling out to an API to get TenantSetting/GroupSettings. The response it got was {"value":[]} - basically a NULL array. Odd. I tried the same thing on my working tenant and saw the response as this:

{"value":[{"displayName":"Group.Unified","templateId":"62375ab9-6b52-47ed-826b-58e47e0e304b","id":"3c4adb15-25c8-48a5-b060-106e025803a2","values":[{"name":"NewUnifiedGroupWritebackDefault","value":"True"},{"name":"EnableMIPLabels","value":"True"},{"name":"CustomBlockedWordsList","value":""},{"name":"EnableMSStandardBlockedWords","value":"False"},{"name":"ClassificationDescriptions","value":""},{"name":"DefaultClassification","value":""},{"name":"PrefixSuffixNamingRequirement","value":""},{"name":"AllowGuestsToBeGroupOwner","value":"False"},{"name":"AllowGuestsToAccessGroups","value":"True"},{"name":"GuestUsageGuidelinesUrl","value":""},{"name":"GroupCreationAllowedGroupId","value":""},{"name":"AllowToAddGuests","value":"True"},{"name":"UsageGuidelinesUrl","value":"https://guideline.example.com"},{"name":"ClassificationList","value":""},{"name":"EnableGroupCreation","value":"True"}]}]}

This looked familiar as I'd set some of these values in the past using PowerShell. A great article that describes all this can be found at Azure Active Directory cmdlets for configuring group settings . I looked at both of my tenants to see what settings were present, using Get-AzureADDirectorySetting -All $true. My tenant that didn't resolve the guest came back empty:

In my tenant that did show some settings I saw what I expected:

It appeared that I had no specific settings configured, and in that case the NULL array returned didn't give Project the information it needed to know that guests were ok to resolve. I'm not sure at this point whose bug this is - Azure AD for not having any defaults show up - or Project for not filling in the gaps. We will see who gets to fix it. But to fix it yourself you just need your tenant admin to follow steps in the article listed above to set the settings they want - and assuming they don't block guests you should then be able to assign and add guests to your projects and they will be members. CTRL-F5 once the Group Settings are present, and then I can add my guest to members:

Project Server Subscription Edition Public Updates

bdiana — Tue, 09 Jul 2024 04:51:26 GMT

Here are all the articles for fixes released for Project Server Subscription Edition from its launch to date.

June 2024

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-june-11-2024-kb5002603-2bf5fb1a-0322-44ce-b420-ce1b8bbf7f06

This security update also contains a fix for the following nonsecurity issue in Project Server:

Fixes an issue in which the connected SharePoint site displays as not created with specific Unicode characters in URL.

DB Version: 16.0.17328.20362

May 2024

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-may-14-2024-kb5002599-3e38ec3d-3d59-40d4-8807-0002f89d9ab8

DB Version: 16.0.17328.20292

March 2024

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-march-12-2024-kb5002564-11110625-a13c-4f09-9a60-d1176d9769ba

DB Version: 16.0.17328.20136

February 2024

https://support.microsoft.com/en-us/topic/february-13-2024-update-for-sharepoint-server-subscription-edition-kb5002560-d2b9cb2a-

a35c-46f3-b863-30f69b8f0b39

DB Version: 16.0.16731.20526

January 2024

https://support.microsoft.com/en-au/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-january-9-2024-kb5002540-a34c5764-cd4a-484b-8bf1-cefd033f8d7e

DB Version: 16.0.16731.20462

This security update also contains a fix for the following nonsecurity issue in Microsoft Project Server:

Fixes an issue in which the edited field value in a list is automatically deleted after you press Enter while you're in the classic experience of the document library.

September 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-september-12-2023-kb5002474-c5869260-56d1-439b-a159-45fa38935276

DB Version: 16.0.16731.20180

This security update also contains a fix for the following nonsecurity issue in Project Server:

Fixes an issue in which you cannot update the FixedCostAccrual property on DraftTask, PublishedTask, and ProjectSummaryTask objects by using the client-side object model (CSOM).

August 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-august-8-2023-kb5002437-9487a2bf-0010-4f41-93b7-4ab8e8526737

DB Version: 16.0.16130.20684

July 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-july-11-2023-kb5002424-a5880c63-1550-4f63-b788-9c8e79d05f47

DB Version: 16.0.16130.20642

This security update also contains fixes for the following nonsecurity issues that occur when you use the client-side object model (CSOM) in Project Server:

You cannot set a project or task calendar for a project.
You cannot update the ScheduledFromStart property for a project.

June 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-june-13-2023-kb5002416-30534b01-588b-422e-8b87-865d22244a83

DB Version: 16.0.16130.20548

May 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-may-9-2023-kb5002390-5d150cf3-e42d-4a0e-b015-0b4357b8e5ea

DB Version: 16.0.16130.20420

April 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-april-11-2023-kb5002375-1bbdc497-d120-4f43-a0d2-e9392d62dd1f

DB Version: 16.0.16130.20314

March 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-march-14-2023-kb5002355-9a893845-0b9b-40af-ac82-6f3cd252c378

DB Version: 16.0.16130.20206

This security update also contains fixes for the following nonsecurity issues in Microsoft Project Server:

Fixes an issue in which you might not be able to update resources by using the client-side object model (CSOM) if a remote event handler is attached to a resource event, such as Resource Changing.
Fixes the following issues that you might encounter when you publish projects:
- Logging on and connecting to the server that contains the Project client might be blocked.
- Opening projects that have no relationship to the publishing projects might not be possible.
- Loading projects in the Schedule web part might be slow.
- Team members might not be able to access the Tasks page.
- Going to the Force Check-in Enterprise Objects administrative page might be blocked.

February 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-february-14-2023-kb5002353-837bb6b7-4b8d-4974-a2ee-015b0d72d408

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-language-pack-february-14-2023-kb5002352-97afbca4-650d-43c1-be2e-b1842452a019

DB Version: 16.0.15601.20478

This security update also contains fixes for the following nonsecurity issues in Project Server:

Fixes an issue in which you might not be able to update resources by using the client-side object model (CSOM) if a remote event handler is attached to a resource event, such as Resource Changing.
Fixes an issue in which the Save and Cancel buttons are disabled on the Add dependency page of a Portfolio Analysis.

January 2023

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-january-10-2023-kb5002331-816d71a6-ffa3-4978-9d87-cb22c74d9aa1

https://support.microsoft.com/en-us/topic/january-10-2023-update-for-sharepoint-server-subscription-edition-language-pack-kb5002326-a76c92b4-a264-439f-82c2-c08c9208f5fb

DB Version: 16.0.15601.20418

This security update also contains a fix for the following nonsecurity issue in Project Server:

Occasionally, administrators may have to restore server metadata, such as the Enterprise custom fields. Although the restore process starts the Reporting Database Refresh queue job, the following items might occur:
- The queue job fails.
- When you access reporting data either directly from the reporting schema or through Open Data Protocol (OData), no data is returned.
- In the Universal Logging Service logs, the following error is reported:
  
  SPValidatingBinder: blocked type Microsoft.Office.Project.Server.BusinessLayer.ReportingLayer.RefreshStorageData denied.
This update fixes the problem so that the Reporting Database Refresh job finishes successfully.

December 2022

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-december-13-2022-kb5002327-eb4896cd-681b-4756-8c60-f33c9ac2af47

DB Version: 16.0.15601.20316

This security update also contains a fix for the following nonsecurity issue in Project Server:

Fixes an issue on Project Web App (PWA) pages, such as the Project Center, in which the keyboard focus is lost when you select any item through the keyboard from the View, Filter, and Group by menus.

November 2022

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-november-8-2022-kb5002296-f6c47ad6-692f-4617-ae12-9ca05fa96e39

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-language-pack-november-8-2022-kb5002291-cd7749b3-0293-4d70-b836-eabf28ab5995

DB Version: 16.0.15601.20238

This security update also contains fixes for the following nonsecurity issues in Project Server. Consider the following scenario:

You have the Content Editor web part placed on a Project Detail Page (PDP).
You edit a project in Project Web App (PWA).
While you are editing, the SharePoint web page security validation times out.
You close the project, and you try to check in the project.

In this situation, the "Processing" toast appeared, but it never went away. Additionally, the project didn't get checked in. This issue is fixed so that now the following expected message appears:

The security validation for this page has timed out. Click Back in your Web browser, refresh the page, and try your operation again.

On Project Web App (PWA) pages, such as the Project Center, fixes an issue in which you cannot use voice to access the View, Filter, and Group By buttons.

October 2022

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-october-11-2022-kb5002290-29091e4f-76fc-461e-abf0-233875cc1c69

DB Version: 16.0.15601.20158

September 2022

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-language-pack-september-13-2022-kb5002270-a5150614-316f-45d3-8126-340a3237d662

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-september-13-2022-kb5002271-9b21704b-ce7b-4da8-93e4-b0d6f9dbbd8d

DB Version: 16.0.15601.20052

August 2022

https://support.microsoft.com/en-gb/topic/august-9-2022-update-for-sharepoint-server-subscription-edition-kb5002247-11f577a7-53b6-4e1d-a077-00c2aca9f584

https://support.microsoft.com/en-gb/topic/august-9-2022-update-for-sharepoint-server-subscription-edition-language-pack-kb5002233-fe2642f8-bdf3-47e0-b8ba-3b2d0d790ff4

DB Version: 16.0.14931.20612

This update also contains the following fixes in Project Server:

Fixes an issue in which the actual hour totals in timesheets are not always recalculated correctly.
Fixes an accessibility issue in which the name property of the various cards in the Track your work section isn't correct on the Project Web App home page.
Fixes an accessibility issue for the outline menu buttons in which the elements don't have unique IDs.

July 2022

https://support.microsoft.com/en-au/topic/july-12-2022-update-for-microsoft-sharepoint-server-subscription-edition-kb5002234-66a3e5c3-9696-460d-84f6-27cd44470026

DB Version: 16.0.14931.20502

June 2022

https://support.microsoft.com/en-gb/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-june-14-2022-kb5002224-79a58952-d8be-40c4-b7b9-32c50f9198f8

https://support.microsoft.com/en-gb/topic/june-14-2022-update-for-sharepoint-server-subscription-edition-language-pack-kb5002225-e2ff6519-9350-418a-870c-c22a0067a3cd

DB Version: 16.0.14931.20418

May 2022

https://support.microsoft.com/en-gb/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-may-10-2022-kb5002194-058d68b3-727b-4725-bb2b-589d8481dcd5

https://support.microsoft.com/en-gb/topic/may-10-2022-update-for-sharepoint-server-subscription-edition-language-pack-kb5002144-d07dcb4c-6ffe-43bd-a54d-b23f1ba76ec4

DB Version: 16.0.14931.20286

April 2022

https://support.microsoft.com/en-gb/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-april-12-2022-kb5002191-93b96ecf-aeb6-42a3-8e3f-fc43d1ad4a86

DB Version: 16.0.14931.20196

This security update also contains fixes and improvements for the following nonsecurity issues in Project Server:

Fixes the DatabaseUndefinedError and System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'pk_lt_uids' in the database errors that occur when you query lookup tables by using the SharePoint client-side object model (CSOM).
Fixes an issue in which the language of the values inflag-type custom fields within an online analytical processing (OLAP) cube do not match the Project Server language.For example, the flag values appear as "True" or "False" in English, and they are "Vero" or "Falso" in Italian, but the values are always displayed in the English form.
Fixes an issue in which upgrading from Project Server 2019 to Project Server Subscription Edition fails and generates an "Object too new" error message in some cases.

March 2022

https://support.microsoft.com/en-gb/topic/march-8-2022-update-for-microsoft-sharepoint-server-subscription-edition-kb5002165-151c658b-37e3-486b-bd83-8e3e69a37403

DB Version: 16.0.14326.20796

This update also contains a fix in Project Server:

Fixes the DatabaseUndefinedError and System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'pk_lt_uids' in the database errors that occur when you query lookup tables by using the SharePoint client object model (CSOM).

February 2022

https://support.microsoft.com/en-gb/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-february-8-2022-kb5002145-e15e1bdd-e28d-4027-85f3-747d3c24b450

DB Version: 16.0.14326.20742

This security update also contains a fix for the following nonsecurity issue in Project Server. Consider the following scenario:

On a task, you set the Status Manager.
You publish the project.
Later in the same editing session, you change the Status Manager to a different manager.
You publish the project.

In this situation, the Status Manager property does not get updated in reporting. Instead of reflecting the new Status Manager, the old one is still observed.

January 2022

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-january-11-2022-kb5002111-ff855b0d-f4f7-41e9-a03e-2912cd7f2bb1

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-language-pack-january-11-2022-kb5002110-196ee39c-c32c-4af1-9400-50c4bc546c1d

DB Version: 16.0.14326.20714

December 2021

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-december-14-2021-kb5002045-2766a574-8f5e-43c1-9b15-a7c08d1cab18

https://support.microsoft.com/en-us/topic/description-of-the-security-update-for-sharepoint-server-subscription-edition-language-pack-december-14-2021-kb5002047-d8284466-a5d7-4fc6-b45c-c753ee168b3e

DB Version: 16.0.14326.20620

RTM DB Version: 16.0.14326.20450

Constraints, Calendars and Time Zones in Project

Brian-Smith — Tue, 21 Sep 2021 12:00:00 GMT

The idea for this posting came from some support questions that landed with some of my Dynamics 365 colleagues from users of Project Operations, but an interesting topic for any user of Project. When facing a static screenshot and being asked why the duration is what it is, or why did my start date move the answer will always be "it depends" but in the vast majority of cases Project is doing exactly what it should - and based on the order of actions - exactly what you asked it to do!

I'll start with an example - two tasks, in a fixed units based project, both assigned to me and they are apparently identical. But are they?

Next I will add Alex Wilber to my twin tasks and see what happens.

Whoa! These twins obviously are not identical! It is a fixed units project, so it added 16h of work on each task for Alex, and that is ok - but why did the dates change? And 3.25d duration? The first one was pushed out to finish 9/23 and the second says it needs to start 9/10 - and was marked as running late (the yellow highlight shows that if I hover over). To understand the difference let's take a deeper look at the original tasks - and also understand how I created them.

The first task I created as a 2d task, I did not set the dates, I just assigned myself (I started creating this blog post 9/16). Both the project default working template and my resource calendar are Pacific Time. The task start time is 9am.

The second task I created with the start date set for today (9/16) and the finish date set for 9/17. Then I also assigned myself. At this point they appear identical, apart from the calendar icon on the finish date of the 2nd task, which only shows if the cell is either selected or I hover over. Here I selected the cell then hovered over the other finish date to show the difference.

The star in the lower right corner indicates that there is a constraint. If I open up the calendar dialog this shows me exactly what is set.

The task is constrained to finish no earlier than 9/17/21. This happened because I chose the finish date by making an explicit selection, whereas in the first task the finish date was scheduled by project, as the end of the 2 day task that started on the project start date. Best practice when scheduling is to not set any dates that do not explicitly need to be set. The project will flow based on the project start date, the durations, and any tasks set to depend on other tasks. That way if the project needs to be pushed back a week I can just change the project start date and all dates will move. If have constrained any dates they will not move.

Now we know why the tasks behaved differently we can look at why the duration changed to 3.25d when I added Alex, and why it didn't just stay at 2 days. The answer lies in Alex's calendar, and his working day in 9am to 12pm daily - so only 3 hours of work can be completed each day. If I hover over the finish date of the first task I see it completes at 10am.

Alex works 3h per day on the 16th, 17th, 20th, 21st and 22nd, then completes his 16h of effort on the 23rd by working from 9am to 10am. The 3.25d is the calculated duration for the task - with the first two days seeing me complete 16h work and Alex 6h (22h total) then the remaining 10h is another 1.25 * 8 hour days. Currently this is a hard-coded hours per day calculation that Project uses.

The calculation is the same for the 2nd task, except that the finish date is constrained - so it would need Alex to start his assignment at 11am on 9/10 to do the remaining 5 * 3h days to complete by 9/17. As Alex does not have a time machine the PM will need to intervene here and probably removing the constraint is the best solution.

Let's take a look at adding a different resource in place of Alex, and this time I'll choose Lidia.

Interesting! We know why the 2nd task starts earlier as the finish still has the constraint, but that 4 day duration is intriguing - as the task actually looks shorter than the 3.5 day duration we saw earlier? What gives? Time to look at Lidia's calendar.

I've included the left hand part to show what I see in her calendar in my time zone display, as well as the dialog of the calendar settings which shows Lidia's time zone (GMT +1) and the actual local hours she works. In my time zone, which is where the project was created, Lidia can start at midnight, has a 1h break at 3am, then works 4.5h in the afternoon - making a 7.5 hour day. So how does this project get worked? Let's look at the first task, and if I hover over I see the start time as 8am. This 'may' be a bug, and I'd normally expect it to be 9am - still investigating exactly why this is... But we'll go with it for now. On 9/16 I work 8h from 9 to 5, and Lidia works from 8am to her finishing time of 8:30 am, so just 0.5h. On 9/17 I work 8h from 9 to 5 and Lidia works 7.5h, from midnight to 8:30am, less 1h. On 9/20 Lidia also works 7.5h so at that point has completed 15.5h of the 16 - with the final 30 minutes completed on the 21st at 12:30am - and this is confirmed by the finish time if I hover over:

This all turns into a 4 day duration as we have 8.5h on 9/16 (Lidia's 8am to 8:30 plus my 9 to 5) then on 9/17 we have midnight to 5pm, less 1.5h for Lidia's break and the gap between 8:30 and 9am - so 15.5 hours, then 7.5h on 9/20 and finally 0.5h on 9/21. That makes 32. 32 divided by 8 = 4. In this case working across time zones extends the day so the duration calculation can be a little counter intuitive. Two people working concurrently can have 1 day duration and work 16h if each has an 8h calendar - but if they are in time zones that do not overlap then it could be 2 days instead!

With the Project client previously, and Project Online the working time was not taking account of time zones, and customers would need to be creative with setting all working calendars on a consistent time zone. Now Project for the web supports time zones it makes that a lot easier to manage in some ways, but just be careful to understand the different calendars that may come into play - from the initial working time that the Project gets created in - which will drive the overall start time - to all the resources and their calendars. If my plan also had dependencies between resources working on different time zones then you can see how that would have added another layer of complexity.

Schedule Modes, and task and resource usage in Project for the web

Brian-Smith — Fri, 20 Aug 2021 17:04:13 GMT

The ability to choose different scheduling modes was one of the recent features to be added to Project. This means that at the project level you can choose:

Fixed Duration (out-of-the-box default)
Fixed Effort (work)
Fixed Units

You can also choose at the organization level if you want to have a different default, and if you want to allow anything other than the default to be used. Once you change the setting then all future projects will have that default (or a set mode if you allow it). There is no change to existing projects. (8/19/2021 - I added a note on how to navigate to the setting - scroll right to the end of the document)

As you can see, I have left my default as fixed duration, but changed the other option to allow Project Level Schedule Mode Overrides.

Once the schedule Mode Override is allowed, you can then choose the schedule mode when creating a new project. Here I have changed the project to use Fixed units. Once set it cannot be changed:

The behavior is the same as in previous versions of Project and obeys the expected 'Effort = Duration x Units' formulae, so the article at Change the task type for more accurate scheduling - Project (microsoft.com) is still very much applicable. (Note: This article uses the term 'Work' to refer to 'Effort'. One difference to earlier versions currently, is that the mode is set for the complete project and cannot be set on a task-by-task basis. I'll give examples of each, and also explain what is happening behind the scenes (and show how to 'see' behind the scenes), as there are some scenarios where it might not initially be obvious what is going on.

I've created 3 projects, one of each mode, and so far they have no tasks. I have actually opened the tasks pane for each plan - and this action has set the Finish Date and Earliest Start Date values - which when the plan is initially created are not populated in this view.

Lets start with the default, a fixed duration plan. I'm going to add a task of 5 days:

If this is 5 day task maybe the first question is why does it start 'today' (I'm writing this on 8/16) but not finish until next Monday? Shouldn't it finish on Friday? One clue to what's going on here can be found on the Summary tab, where we also see the time as well as the date:

Next I make an assignment and add myself to that task and as expected I see 40h effort (work) as I work the default 8h day. If my calendar was different from the project calendar I might see the start times change.

If I add another resource then my duration is fixed at 5 days, and my work increases to 80h. 80h = 5 days * 2 units (8h/day). All looks good!

Now the same steps for fixed effort:

Looks the same. But lets re-run that, but I'll make the assignment and save, and then set the duration to 5 days:

What was the difference? It is a fixed effort task, and the effort gets fixed when there is effort added, so in the 2nd task the effort was 'fixed' at 8h then the duration increased to 5 days, whereas the first one I have 5 days already, so it adds 5 full days of effort before 'fixing' the effort. One thing I found useful in looking behind the scenes is to use Power BI and look at the project, task and assignment data - which gives a sort of 'task usage' or 'resource usage' view, but without the timephased part. This shows that my assignment dates match the tasks so my work is spread over the 5 days:

Adding a second resource to the fixed effort task results in the fixed amount of work being shared between Lidia and me - but what's with the odd durations?

The 2.5 days makes sense - lets see how project shared the work for that 0.83 day assignment:

The first assignment is shared quite neatly, but the 2nd is split in a 5 to 1 ratio, due to the way the duration and assignments were set for each one. For those who think the sky is falling right now - here are the exact same steps executed in the Project desktop client to show that this is working EXACTLY as Microsoft Project always has! The scheduling engine behind the new Project is the same as it has always been.

On to the last mode, fixed units. Same steps. Cut to the chase, as the work is not fixed in this case the order of adding the assignment and extending the duration makes no difference - both tasks show the same 40h work:

If we add another resource we again see the exact same behavior as in the desktop client of Project, and the effort increases to 80 hours as we now have 2 units (people) for 5 * 8 h.

In the desktop client there is an addition option to allow 'effort driven' Fixed Unit tasks - and in that case the worked would have stayed at 40h. That option is not available here.

Increasing the duration at this point will also increase the work, as our units are fixed (at 2). As a double check I also show the Power BI view of things at this stage:

These examples have been quite straightforward with only the fixed effort needing a little more explanation, but it can get more complicated in an active project with assignments added while tasks are actively being worked and effort already recorded, so that it is less obvious what is going on. I think that is when Power BI might help with the ability to see the tasks and assignment data so you will be able to confirm why dates are as they are. One common example might be a task not showing complete when all the work is apparently done - but it could be that the duration is not complete yet - and the assignments may all finish before the end date of the tasks. As an example, if some effort is completed and then duration extended and another assignment made - you can get something like this - which from the tasks view in Project it will not be obvious that the 2nd assignment does not start until the 2nd day - but Power BI shows us the details:

One final point to mention before I explain the duration calculation that gave us the 0.83d earlier - is that it is possible to set the schedule mode options for the default environment of Project for the web, as well as named environments, but as your users will likely be living in the 'project.microsoft.com' experience and not the Dynamics one, they will not be seeing the 'Summary' tab and therefore not aware of the schedule more that has been set. This could get confusing for users - so just be aware that it could happen.

So how did that 0.83d duration for the Fixed Effort task calculation work? Initially I was assigned and that triggered 8h effort for my default day. The duration was increased, but with effort fixed the work was spread out to 1.6 hours each day for 5 days, or 0.2 hours (12 minutes) of effort for each hour. When Lidia was also assigned she could do 8h per day, or 1h of effort for each hour. For the first hour of the day we therefore have 1h work from Lidia and 12 minutes from me - so 1.2h. After 6 hours we have completed 7.2h of the 8h effort required, so at the same rate in the next hour it only takes 40 minutes to complete the remaining 0.8h (48 minutes) with me contributing 8 minutes to Lidia's 40. 6h 40m = 6.67h. 6.67/8 = 0.83 (to 2 decimal places).

As a final validation I also used the 'export user data from Project for the web' PowerShell sample (Export user data from Project for the Web - Project for the web | Microsoft Docs) on a fresh example of the Fixed Effort project - and in this specific repro the start times was 4pm, so I actually see 1h of time available on the first day - but I can see the task usage view exactly the same as if I'd created the plan in Project:

I hope you have found this post useful, and certainly in my many years of supporting Project when a customer comes in with a "Project is calculating this incorrectly' case the answer is usually that there is a good (logical?) explanation and Project is working as designed!

Admins - To get to the Project parameters setting to change the organization default go to the lower left corner when in your Project Power App and click Project (where you see the blue 'P' and in the Change Area menu that appears select Settings. Click the long GUID in the Default Organizational Unit column, then under the Related tab select Project Parameters, Click the word Parameter in the description column and you will then see the options to choose and set the Schedule Mode and Project Level Schedule Mode Override Permitted option. (and thanks to Chad Olson - the proper navigation is select the row with the log GUID and then Edit - this will save a couple of steps).

Getting M365 Admin Center info with the Graph API

Brian-Smith — Mon, 16 Aug 2021 18:33:04 GMT

I wrote the original message center to Planner integration using the Office 365 Communications API, but this has now moved over to the Graph API, and the older API will no longer be available past the end of the year. The built-in Message Center to Planner will have no issues of course, but if you used my code, or the additional Service Health Dashboard code I added a while back then you will need to update. The code now fully uses MSAL for authentication and Graph to read both Message Center and Service Health Dashboard data. It didn't need much changing, but I did notice that the MC posts now have a new reference type of 'Roadmap ID' which I am not using and it broke my code as I was only expecting an external link. I just filter for the external links for now, but I'll likely come back and add Roadmap info and hopefully tie this into details from the M365 Roadmap too.

The code can be found at LunchWithaLens/AdminCenterAutomation: Updated code to use MSAL, as well as simplifying to loop on products not tasks - hence fewer queue items. Also added a function for SHD posts into Teams channels. (github.com) and I won't walk-through it here as the basics haven't changed since the last walk-through blog post.

The code does two things:

The original message center to Planner sync - not as convenient as the built-in code, but useful if you need to push into different plans or assign to various different people. This writes a queue entry for each workload then another Azure function pulls these off and writes to Planner

2. Pushes service incident data into Teams, and will also @mention someone too. Subsequent updates will add to the thread.

This code might also be useful just to see how to write into a teams channel and a thread and @ mention.

I might get around to porting these to C# at some point, and I really ought to tidy up the posts that have HTML formatting in them too!

For those Microsoft 365 changes that need a project to implement

Brian-Smith — Tue, 27 Apr 2021 18:15:19 GMT

*** 4/27/2021 - If you have used this and get the "Resource not found for segment" error, then this means you are using the new Dataverse connector which expects the OData format for the record and not just the GUID. I've updated below to reflect this ***

This blog post grew out of a conversation with one of my Microsoft colleagues about how one would push a Planner task to Azure DevOps - and the scenario was that they are very happy with the Message Center to Planner integration as a means to triage changes as they come through - but to actually execute on the changes they would normally use Azure DevOps. So I got my thinking cap on and took a look at what was available in Power Automate.

Planner is somewhat limited in direct actions and triggers, but of course you can use the Graph API too once you get a Flow started - but the trigger is what you need here and I wanted to avoid a timer job just looking in Planner. The "On completed" trigger seemed like the best approach, as if you were carrying out the work in Azure DevOps (or Project) then the plan task could be closed. Then with the number of supported labels increasing in the very near future I thought that, combined with a complete task, would get me started.

Power Automate then has an action to read the Planner labels, so I could check if the task needed to go to Azure DevOps - and if so use the "Create a work item" action.

In this example I decided to create as a Feature - and in the description I crafted a link that would navigate back to the Planner task to get the full information. A proper job here would probably involve a few more steps to read the information and populate it into Azure DevOps, but as a quick proof of concept it worked for what I needed.

And here is my newly created Azure DevOps feature.

My next thought was - that nice - but what if I want to use Project Online or the new Project to manage a Microsoft 365 change? (The eagle eyed may have already seen the :Send to Project Online and Send to PfW in my labels list) so I extended my Flow to also look at those labels and react accordingly.

It is important to mention here that I am creating these as projects in Project Online and Project for the web - and not as tasks - as the assumption is that you would use this when you needed more than just a task (otherwise why not leave in Planner?). Likewise for Azure DevOps - I created a feature but there may well be other entities added to deliver the change.

For the examples I've used I'll admit that these could probably just have been handled in Planner - as I can't imagine our adding of text predictions justifies a complete project plan to ready users...

And in the new Project we see the other task turned to a plan - in this case in a different environment than the default using the new flexible deployments option.

The Flow was pretty basic adding these two too - just a condition driven by the label that was applied, then the Create a new Project for Project Online (as the description doesn't support HTML I didn't create the href):

and for the new Project in the Dataverse environment I used the following:

(updated 4/27/201 with new Dataverse connector details) With the new Dataverse connector the "add a new row" option expects the OData format, so I used the Get a new row by ID to get the 4 required fields:

Then for each field in the dynamic content section I can get the OData id which is the right format. I think I could also hard-code to the plural table name and the GUID - which might save some calls. Note that the 4 OData id's are different - and selected from the appropriate section in the dynamic content.

The tricky piece here was working out what the GUIDs needed to be, and I used the option to open the datasets in Excel to get a good look at the GUIDs so I could test out and confirm what these needed to be. You can do this (permissions allowing) by going to make.powerapps.com, selecting the environment, then under Data, Tables select the table of interest (set filter to "All") then use the "Edit data in Excel option (don't get too excited - you can't really edit the data for this Project stuff in Excel).

Calendar Id can be found from the Calendar column of the Work template table – in my case I only had one work template, the default – so not problem using just this one – you might need to have some way to choose if your config is more complex.

Contracting Unit can be found as the Contracting Unit column in the Project table – and again I just had one so ok to hard code – your mileage may vary (Don’t confuse it with the Contracting Unit (Lookup) field…).

Project Manager comes from the Project Manager column in the Project table, and has its origins in the User table and the User column. It is NOT the same as the AAD GUID.

Like the Azure DevOps example you'd probably want to add a few more steps to pull more information in - and I did skip a big "gotcha" on the Project Online side - as Project Online does not allow certain characters in a Project name I chose my example carefully. You'd need to swap out any occurenace of the following characters - : .(period) \ " / : ; | ? ' < > * # ~ % { } +.

Finally - a screenshot of the whole Flow (before the new Dataverse connector stuff which is shown above):

Let me know what you think - and what would make managing Microsoft 365 changes easier?

Enterprise Global not loading after migration to Project Server 2019

Brian-Smith — Wed, 02 Dec 2020 23:30:15 GMT

Great to see so many people upgrading to the latest - but one issue we have heard is that the Enterprise Global template (EGT) cannot be opened in Project Server 2019 after the migration. The problem appears to be that the Project UID for the EGT is changed - then it cannot be found under its new GUID. Often this is seen to be happening when migrations start at an earlier release - like 2010 or 2013, but from the reports we have the problem only occurs at the final stage - and the EGT opened just fine in 2016.

The solution requires a bit of work in SQL Server, so if you are not comfortable working in SQL then either work with the database administrator for Project Server - or of course open a support ticket. Always best too - if you either take a SQL backup before making any changes - or make the changes just after your normal backups occur - in case you need to revert.

To detect the issue you would first run a query like this one - substituting your content database name for dbname:

use dbname

select PROJ_UID, PROJ_NAME from pjpub.msp_projects where proj_name like '%eglobal%'

This may return a result similar to the following:

PROJ_UID PROJ_NAME

75CDD7DB-1B68-4B70-A8D6-2FE52DA83ACD EGlobal

87537CC3-5AF6-421E-A42F-A27CB2A2EFA2 EGlobal-20150320041555

Here, the EGlobal has the proper association to PROJ_UID = 75CDD7DB-1B68-4B70-A8D6-2FE52DA83ACD (this is the static GUID) and an older version of the EGlobal is shown here (the second row).

If the EGlobal has a different UID, then it’ll need to be updated to use the proper one.

For example, suppose the returned result looks like this:

PROJ_UID PROJ_NAME

DF59652B-073D-4FB1-A16E-1F7233C439AE EGlobal

87537CC3-5AF6-421E-A42F-A27CB2A2EFA2 EGlobal-20150320041555

You’d need to run an update query that looks similar to this:

use dbname

Update pjpub.msp_projects

Set PROJ_UID = '75CDD7DB-1B68-4B70-A8D6-2FE52DA83ACD'

where PROJ_UID = 'DF59652B-073D-4FB1-A16E-1F7233C439AE'

Use the GUID (in this example starting 'DF59,,,' that is the incorrect GUID returned from the previous select statement.

If this doesn't solve the problem - or if the GUID looks to be ok, then you have a different problem and would need to open a support ticket.

Thanks to Adrian and Ajith for help with putting these details together - and Dale and Paul for alerting us to the issue.

My suspicion is that this is a 2019 client issue - as just changing the GUID to try and repro the issue in 2016 does not give any problems - the EGT opens just fine whatever GUID it has...

App + User authentication and Project Online development

Brian-Smith — Mon, 23 Nov 2020 22:43:50 GMT

With the most recent updates to the SharePoint client object model (CSOM) libraries it is now possible to authenticate to SharePoint and Project Online with the MSAL libraries rather than ADAL - and this opens up the use of .NET Standard rather than needing the .NET Framework. This DOES NOT however mean that Project Online supports App ID only authentication. SharePoint Online does support app only - but the additional authorisation level in Project to understand who the user is and what they can do requires app + user. See more information here - https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/using-csom-for-dotnet-standard and the API permissions you can choose are shown here. Im my example I've just selecting the Project.Write which allows me to create and update a project.

You application would need to reference the Application (Client) ID associated with these permissions when requesting token - but would also need to pass in the credentials of a user with permissions and license to Project Online. This could be an interactive login - or using a securely stored username and password (not recommended) or using a stored token that is refreshed periodically. Attempting to connect by with the application ID will fail with an "unauthorized" response.

As an example, the following code would get the token and set for project context to make further CSOM calls:

string domainName = "brismith.onmicrosoft.com"; string PJOAccount = "brismith@brismith.onmicrosoft.com"; string scope = "https://brismith.sharepoint.com/Project.Write"; string redirectUri = "http://localhost"; string pwaInstanceUrl = "https://brismith.sharepoint.com/sites/pwa/"; // your pwa url int DEFAULTTIMEOUTSECONDS = 300; HttpClient Client = new HttpClient(); var TenantId = ((dynamic)JsonConvert.DeserializeObject(Client.GetAsync("https://login.microsoftonline.com/" + domainName + "/v2.0/.well-known/openid-configuration") .Result.Content.ReadAsStringAsync().Result)) .authorization_endpoint.ToString().Split('/')[3]; // This client ID just has project.write PublicClientApplicationBuilder pcaConfig = PublicClientApplicationBuilder.Create("87edf46a-466d-4241-8afc-b9650d7fb0d7") .WithTenantId(TenantId); pcaConfig.WithRedirectUri(redirectUri); // This section uses the interactive flow for auth var TokenResult = pcaConfig.Build().AcquireTokenInteractive(new[] { scope }) .WithPrompt(Prompt.NoPrompt) .WithLoginHint(PJOAccount).ExecuteAsync().Result; //The following section uses the username and password - this would be best pulled from Azure Key Vault or use another auth flow //This also requires the app registration to be set as a public client //SampleConfiguration config = SampleConfiguration.ReadFromJsonFile("appsettings.json"); //string text1 = config.Text1; //var sc = new SecureString(); //foreach (char c in text1) sc.AppendChar(c); //var TokenResult = pcaConfig.Build().AcquireTokenByUsernamePassword(new[] { scope }, PJOAccount, sc).ExecuteAsync().Result; // Load ps context csom.ProjectContext psCtx = new csom.ProjectContext(pwaInstanceUrl); psCtx.ExecutingWebRequest += (s, e) => { e.WebRequestExecutor.RequestHeaders["Authorization"] = "Bearer " + TokenResult.AccessToken; };

- using the latest MSAL (Microsoft.Identity.Client v4.22) and Microsoft.ProjectServer.Client from Microsoft.SharePointOnline.CSOM 16.1.20616.12000 at the time of writing. These will also work with legacy auth disabled which is a setting that may break some existing custom applications.

To check if legacy auth is disabled you can open the SharePoint Online Management shell, connect to your admin Url and run Get-SPOTenent. Look in the returned properties for:

LegacyAuthProtocolsEnabled : False

which in my case shows that legacy auth is disabled.

Hopefully we will get the sample on Github updated with this latest information.

Project Online: Adding tasks where you want them with Power Automate

Brian-Smith — Mon, 03 Feb 2020 18:46:37 GMT

This is a guest post from Daniel Partridge - who answered an internal enquiry at Microsoft, for a colleague who needed to add a task under a summary using Power Automate (Flow). Great timing as Daniel was also looking at the same challenge.

In Daniel's words and pictures:

In order to accomplish this, I used the “Send and HTTP Request to SharePoint” action in my Power Automate flow, and the end result appears in the schedule as shown in this image:

The flow process would be as follows (*IMPORTANT: The REST API to use for this would be ProjectServer vs. ProjectData in the URL). ProjectData is used for reporting - OData.

Get the Project Id (GUID)
Get the Id (GUID) of the Summary task
Checkout Project - Power Automate Project Online Action
Send HTTP Request to SharePoint - Power Automate SharePoint Action

HTTP POST: _api/ /Draft/Update()/Projects('ProjectGUID')/Draft/Tasks/Add()

Header JSON

{

"Accept": "application/json;odata=nometadata",

"Content-Type": "application/json"

}

Body JSON

{

"parameters":

{

"Name": "task title",

"Notes": "task notes",

"Duration": "2d",

"ParentId": "summaryTaskGUID"

}

Send HTTP Request to SharePoint - Power Automate SharePoint Action

HTTP POST: _api/ProjectServer/Projects('ProjectGUID')/Draft/Update()

Header JSON

{

"Accept": "application/json;odata=nometadata",

"Content-Type": "application/json"

}

Body JSON

Leave blank

Check-in and publish Project - Power Automate Project Online Action

The images below show an example using the Power Automate Project Online Action for “Creates new task” which is unnecessary if you are using the Project Summary task or some other task that already exists to link to as the parent task:

Thanks Daniel for sharing, and I hope this helps other people using Power Automate and Project Online to get their tasks flowing into their projects just where they want them!

My holiday gift to you - update posts are up to date!

Brian-Smith — Fri, 27 Dec 2019 22:01:28 GMT

I'd let these slip a bit - but I just updated the Project 2016, Project Server 2016 and Project Server 2019 posts with the updates through to December 2019. For the 2019 client - the updates can be found at https://docs.microsoft.com/en-us/officeupdates/monthly-channel-2019 or https://docs.microsoft.com/en-us/officeupdates/semi-annual-channel-2019 depending on the channel you get Office through (Monthly or Semi Annual - other channels are available)

Project 2016

Project Server 2016

Project Server 2019