I created a custom enterprise field "Today Status" on the PWA as a formula. The formula is:
Switch(([Actual Duration] / [Duration]) * 100 = 100, "Project Completed", ([% Complete] < 100) And (DateDiff("d", [Finish], Date()) < 0), "Delayed", ([% Complete] < 100) And (DateDiff("d", [Finish], Date()) > 0), "In Progress")
I added it to the Project Center summary page. However, no data is appearing. Can you please guide me what is the issue?
One big disadvantage of Project Server / Project Online custom fields is that those fields are not automatically updated. Custom fields are stored inside the project, and they are not updated outside the project. You need to check-in the project (either in MS Project or PWA), recalculate, save, republish, and check-out.
I recommend you to verify the field value in the Project Information dialog (in MS Project), so you can verify if the custom field did calculate correctly (sometimes, even recalculating the project with F9 could not work!)
Don’t forget to publish the project. Otherwise, the field will have the calculated value but it will not be shown in PWA.
I also see that your formula has Date() in it, which means the formula uses the current date value for defining the “Today Status” custom field. Just consider that you need to update and republish the projects in order to recalculate the field every time you need to.
Yes, I know. It’s a serious limitation, but that´s the way the software works.
Some alternatives:
Create an Excel report (connecting to the database using an ODATA connection) and add the formula there. This way, each time someone opens the report, the formula will be updated in Excel (and only inside the Excel workbook – the ODATA connection is read-only).
Create a dashboard using Power BI and define the formula there. The Power BI dashboard will update the status value when consulted (again, this will happen only inside the Power BI dashboard, it does not update the custom field in Project Server).
