Historic RAG

%3CLINGO-SUB%20id%3D%22lingo-sub-1049152%22%20slang%3D%22en-US%22%3EHistoric%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1049152%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20a%20simple%20way%20to%20collect%20historic%20RAG%20statuses%20etc.%20from%20Project%20Online%3F%20We%20want%20to%20show%20month%20on%20month%20changes%20to%20statuses%20however%20all%20reporting%20is%20'live'%20-%20help!%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1049205%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1049205%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EThis%20might%20not%20be%20a%20solution%20for%20you%20but%20as%20we%20had%20the%20same%20use%20case%20I%20made%20following%20process%20as%20a%20workaround%20and%20thought%20to%20share%20it%20with%20you%20in%20case%20it%20helps%3A%3C%2FP%3E%3CP%3E1.%20A%20custom%20list%20in%20Project%20Site%20(SharePoint)%20created.%3C%2FP%3E%3CP%3E2.%20on%20each%20reporting%20round%20(weekly%20or%20monthly%2C%20etc)%20a%20new%20item%20created.%20We%20have%20status%20commentary%20beside%20the%20RAG%20status%20for%20each%20project.%3C%2FP%3E%3CP%3E3.%20in%20reporting%20solution%20(in%20our%20case%3A%20Power%20BI)%20we%20pull%20all%20the%20items%20from%20that%20custom%20list%20and%20then%20we%20can%20review%20history%20of%20RAG%20changes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20as%20you%20rightfully%20mentioned%20currently%20the%20RAG%20field%20in%20Project%20Online%20doesn't%20keep%20the%20history.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ENaser%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055797%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055797%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F331771%22%20target%3D%22_blank%22%3E%40Naaser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20that%20automated%3F%20We%20have%2050%2B%20projects%20to%20monitor%20do%20you%20use%20a%20single%20line%20for%20each%20project%20each%20month%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1057197%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1057197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Paul%2C%3C%2FP%3E%3CP%3EThe%20status%20of%20each%20project%20is%20independent%2C%20therefore%20we%20have%20each%20project%20has%20its%20own%20list%20of%20status%20and%20RAG%20list%20which%20get%20updated%20with%20the%20its%20project%20manager%20on%20weekly%20basis.%3C%2FP%3E%3CP%3EAlso%20remember%20that%20project%20site%20(SharePoint%20site)%20is%20dedicated%20to%20each%20project%20in%20Project%20Online%20environment.%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20what%20do%20you%20mean%20with%20automation%3B%20the%20updates%20are%20done%20by%20project%20managers%20manually%20and%20the%20report%20is%20automatically%20populated%20in%20Power%20BI.%20This%20is%20overall%20project%20health%20which%20is%20based%20on%20all%20aspects%20of%20the%20projects.%20we%20have%20other%20RAGs%20like%20cost%20or%20schedule%20and%20etc.%20which%20is%20rule%20based.%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ENaser%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1059800%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1059800%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20simple%20solution%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fpwmather.wordpress.com%2F2018%2F12%2F14%2Fprojectonline-snapshot-data-to-sharepoint-list-using-msflow-microsoftflow-ppm-pmot-office365-powerplatform%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fpwmather.wordpress.com%2F2018%2F12%2F14%2Fprojectonline-snapshot-data-to-sharepoint-list-using-msflow-microsoftflow-ppm-pmot-office365-powerplatform%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1072111%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1072111%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F8793%22%20target%3D%22_blank%22%3E%40Paul%20Mather%3C%2FA%3E%26nbsp%3Bthis%20does%20get%20close%2C%20but%20the%20information%20we%20want%20to%20collate%20is%20from%20the%20lists%20(same%20format)%20on%20the%20various%20project%20sites%20that%20sit%20are%20basically%20subsites%20to%20the%20PWA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20flow%20that%20will%20extract%20the%20same%20fields%20from%20these%20lists%20and%20copy%20them%20to%20another%20list%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1072535%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1072535%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EYou%20will%20find%20Flow%20samples%20either%20in%20blogs%20posts%20or%20as%20templates%20that%20will%20get%20you%20so%20far%20but%20you%20will%20need%20to%20build%20this%20flow%20-%20for%20example%20some%20of%20this%20Flow%20would%20be%20applicable%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fpwmather.wordpress.com%2F2019%2F07%2F11%2Fprojectonline-risk-to-issue-escalator-built-using-microsoftflow-office365-sharepoint-javascript-ppm-projectmanagement-msproject-pmot-pmo%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fpwmather.wordpress.com%2F2019%2F07%2F11%2Fprojectonline-risk-to-issue-escalator-built-using-microsoftflow-office365-sharepoint-javascript-ppm-projectmanagement-msproject-pmot-pmo%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3Eas%20this%20reads%20a%20list%20item%20and%20creates%20a%20new%20list%20item%20using%20some%20of%20the%20data%20from%20the%20original%20list%20item.%20This%20article%20also%20includes%20something%20similar%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Fhelp%2F4467880%2Fhow-to-copy-items-between-two-sharepoint-lists-by-using-flow%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Fhelp%2F4467880%2Fhow-to-copy-items-between-two-sharepoint-lists-by-using-flow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1073638%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1073638%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F8793%22%20target%3D%22_blank%22%3E%40Paul%20Mather%3C%2FA%3E%26nbsp%3B%20-%20really%20helpful.%20One%20question%20is%20there%20a%20way%20to%20gather%20from%20multiple%20lists%20into%20one%20list%3F%20The%20lists%20all%20sit%20on%20the%20individual%20project%20sites.%20I%20am%20trying%20to%20find%20a%20way%20round%20the%20fact%20that%20the%20PowerBI%20content%20pack%20for%20PWA%20only%20draws%20from%20native%20fields%2C%20not%20custom%20one.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1073915%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1073915%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EYou%20could%20build%20a%20process%20to%20push%20the%20data%20from%20all%20Risks%20lists%20etc.%20into%20one%20central%20list%20on%20the%20PWA%20site%20etc.%20or%20better%20still%20push%20it%20to%20something%20like%20Azure%20SQL%20DB%20then%20report%20of%20the%20SQL%20Database.%20There%20are%20many%20example%203rd%20party%20tools%20out%20there%20from%20Project%20partners%20that%20do%20this.%20We%20typically%20push%20all%20of%20the%20PWA%20data%20into%20an%20Azure%20SQL%20database%20(project%2C%20tasks%2C%20assignment%2C%20project%20site%20list%20data%20etc.)%20into%20an%20Azure%20SQL%20database%20and%20report%20from%20that.%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1076043%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076043%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F8793%22%20target%3D%22_blank%22%3E%40Paul%20Mather%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMight%20be%20a%20little%20outside%20of%20my%20skill%20set!%20Guessing%20that%20means%20there%20isn't%20a%20way%20with%20Flow%20to%20do%20this%3A(%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20your%20help!%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1076407%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1076407%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EYes%20you%20probably%20can%20do%20it%20with%20Flow%20-%20but%20you%20would%20need%20to%20build%20it%20%3A)%3C%2Fimg%3E%3C%2FP%3E%0A%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1087314%22%20slang%3D%22en-US%22%3ERe%3A%20Historic%20RAG%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1087314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F407918%22%20target%3D%22_blank%22%3E%40pauleast%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20very%20simple%20field%3CBR%20%2F%3Eformula%20(for%20a%20custom%20task%20duration%20field)%20which%20gets%20close%20to%20what%20I%20think%3CBR%20%2F%3Eyou%20want%3A%20IIf(ProjDateValue(%22NA%22)%26lt%3B%26gt%3B%5BActual%3CBR%20%2F%3EFinish%5D%2C-99999%2CProjDateDiff(now()%2C%5BFinish%5D))%3CBR%20%2F%3EAs%20you%20can%20see%20this%20calculates%20a%20variance%20between%20Now()%20and%20the%20scheduled%3CBR%20%2F%3Efinish%20date.%20If%20the%20task%20has%20an%20actual%20finish%20date%2C%20we%20set%20this%20value%20to%20a%3CBR%20%2F%3Elarge%20negative%20number.%3CBR%20%2F%3EFor%20the%20indicators%2C%20compare%20to%20the%20value%3A%20for%20example%3A%3CBR%20%2F%3E-%20Less%20than%20-200%20d%3A%20Blue%20circle%3CBR%20%2F%3E-%20Equal%20or%20less%20than%200%3A%20Red%20circle%3CBR%20%2F%3E-%20Equal%20or%20less%20than%207d%3A%20Yellow%20circle%3CBR%20%2F%3E-%20Greater%20than%207d%3A%20Green%20circle%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi

Does anyone know a simple way to collect historic RAG statuses etc. from Project Online? We want to show month on month changes to statuses however all reporting is 'live' - help!:)

 

Thanks

 

Paul

11 Replies

@pauleast 

Hi,

This might not be a solution for you but as we had the same use case I made following process as a workaround and thought to share it with you in case it helps:

1. A custom list in Project Site (SharePoint) created.

2. on each reporting round (weekly or monthly, etc) a new item created. We have status commentary beside the RAG status for each project.

3. in reporting solution (in our case: Power BI) we pull all the items from that custom list and then we can review history of RAG changes.

 

PS: as you rightfully mentioned currently the RAG field in Project Online doesn't keep the history.

 

Regards,

Naser

Hi @Naaser 

Is that automated? We have 50+ projects to monitor do you use a single line for each project each month? 

Thanks

Paul

@pauleast 

Hi Paul,

The status of each project is independent, therefore we have each project has its own list of status and RAG list which get updated with the its project manager on weekly basis.

Also remember that project site (SharePoint site) is dedicated to each project in Project Online environment.

I am not sure what do you mean with automation; the updates are done by project managers manually and the report is automatically populated in Power BI. This is overall project health which is based on all aspects of the projects. we have other RAGs like cost or schedule and etc. which is rule based.

Regards,

Naser   

Thanks @Paul Mather this does get close, but the information we want to collate is from the lists (same format) on the various project sites that sit are basically subsites to the PWA.

 

Is there a flow that will extract the same fields from these lists and copy them to another list?

 

Thanks!

Hi @pauleast ,

You will find Flow samples either in blogs posts or as templates that will get you so far but you will need to build this flow - for example some of this Flow would be applicable: https://pwmather.wordpress.com/2019/07/11/projectonline-risk-to-issue-escalator-built-using-microsof...

as this reads a list item and creates a new list item using some of the data from the original list item. This article also includes something similar: https://support.microsoft.com/en-gb/help/4467880/how-to-copy-items-between-two-sharepoint-lists-by-u... 

Hope that helps

Paul

Thanks @Paul Mather  - really helpful. One question is there a way to gather from multiple lists into one list? The lists all sit on the individual project sites. I am trying to find a way round the fact that the PowerBI content pack for PWA only draws from native fields, not custom one.

 

Thanks again!

Paul

Hi @pauleast ,

You could build a process to push the data from all Risks lists etc. into one central list on the PWA site etc. or better still push it to something like Azure SQL DB then report of the SQL Database. There are many example 3rd party tools out there from Project partners that do this. We typically push all of the PWA data into an Azure SQL database (project, tasks, assignment, project site list data etc.) into an Azure SQL database and report from that.

Paul

Hi @Paul Mather 

 

Might be a little outside of my skill set! Guessing that means there isn't a way with Flow to do this:(

 

Appreciate your help!:)

Hi @pauleast ,

Yes you probably can do it with Flow - but you would need to build it :)

Paul

@pauleast 

 

Here is a very simple field
formula (for a custom task duration field) which gets close to what I think
you want: IIf(ProjDateValue("NA")<>[Actual
Finish],-99999,ProjDateDiff(now(),[Finish]))
As you can see this calculates a variance between Now() and the scheduled
finish date. If the task has an actual finish date, we set this value to a
large negative number.
For the indicators, compare to the value: for example:
- Less than -200 d: Blue circle
- Equal or less than 0: Red circle
- Equal or less than 7d: Yellow circle
- Greater than 7d: Green circle