HI All, I put a new Task History report together with some calc fields to create a shared column that displays a block of dates that some of my users are finding valuable in tracking task history and turnaround. Thanks to Narayan for the shared column instructional video. I am looking for any feedback anyone may have to make this better or bring to light any gaps or downsides to this method. The "Original Baseline" calc captures the PCD of the task the FIRST TIME the project is made Current. This will not change no matter how many times the project is toggled from Planning to Current. Then, whenever the PCD is changed on the task, the date is captured in the next blank "Revised..." field (Changed PCD...). Finally, the Actual PCD is captured when the task is completed. None of this works for existing projects unless the project is toggled from Planning and back to Current. One glitch I found is that the lines that apply the bold label text for example "column.11.value= Revised: " is not showing the font color that I specify in the hex code. I am trying to get a red color but I can't seem to find a hex code that works in this string. I got a nice Blue color for the "Actual" date but it was hit and miss. Does anyone have an alternate HTML expression that I could use that might be more reliable with applying a color, specifically Red, to the bolded text?
Calc Fields Needed:
Original Baseline-
IF(Project.Status="CUR",IF(ISBLANK(Original Baseline),Planned Completion Date,Original Baseline),Original Baseline)
Changed PCD-
IF(Original Baseline!=Planned Completion Date,IF(ISBLANK(Changed PCD),IF(!ISBLANK(Original Baseline),Planned Completion Date,Changed PCD),Changed PCD))
Changed PCD 2-
IF(Changed PCD!=Planned Completion Date,IF(ISBLANK(Changed PCD 2),IF(!ISBLANK(Changed PCD),Planned Completion Date,Changed PCD 2),Changed PCD 2))
Changed PCD 3-
IF(Changed PCD 2!=Planned Completion Date,IF(ISBLANK(Changed PCD 3),IF(!ISBLANK(Changed PCD 2),Planned Completion Date,Changed PCD 3),Changed PCD 3))
Optional Calc Fields:
Last Default Baseline-- [Not used in the report column example shown but I have this field column adjacent to the shared column below.]
Default Baseline Task.Planned Completion Date [I tried re-setting the project's "Original" baseline as the "Default" (i.e. Default=True) but this calc above does not see it. It only shows the most recent baseline PCD of the task, not any earlier ones previously captured. I thought this curious. You should have an automatic baseline being captured when your projects go Current for this field to populate.]
Status History- [Not used in the report column example shown but I have this field column adjacent to the shared column below. Thanks to Doug Den Hoed for this code.]
LEFT(IF(LEFT(Status History,LEN(IF(ISBLANK(Status),"-",Status)))=Status,Status History,CONCAT((IF(ISBLANK(Status),"-",Status))," - ",Last Updated By.Name," - "," (",$$NOW,") | ",Status History)),2000)
[Screenshot attached of report column]
Shared column: [Change the column numbers below to add them to your own report once the calc fields are created or recreate the shared column from scratch.]
column.9.displayname=Planned Completion Date History
column.9.sharecol=true
column.9.textmode=true
column.9.usewidths=true
column.9.value=Original:
column.9.valueformat=HTML
column.9.width=225
column.10.displayname=
column.10.linkedname=direct
column.10.namekey=Original Baseline
column.10.querysort=DE:Original Baseline
column.10.sharecol=true
column.10.textmode=true
column.10.valuefield=Original Baseline
column.10.valueformat=customDataLabelsAsString
column.11.sharecol=true
column.11.textmode=true
column.11.value= Revised:
column.11.valueformat=HTML
column.12.displayname=
column.12.linkedname=direct
column.12.namekey=Changed PCD
column.12.querysort=DE:Changed PCD
column.12.sharecol=true
column.12.textmode=true
column.12.valuefield=Changed PCD
column.12.valueformat=customDataLabelsAsString
column.13.sharecol=true
column.13.textmode=true
column.13.value= Revised 2:
column.13.valueformat=HTML
column.14.displayname=
column.14.linkedname=direct
column.14.namekey=Changed PCD 2
column.14.querysort=DE:Changed PCD 2
column.14.sharecol=true
column.14.textmode=true
column.14.valuefield=Changed PCD 2
column.14.valueformat=customDataLabelsAsString
column.15.sharecol=true
column.15.textmode=true
column.15.value= Revised 3:
column.15.valueformat=HTML
column.16.displayname=
column.16.linkedname=direct
column.16.namekey=Changed PCD 3
column.16.querysort=DE:Changed PCD 3
column.16.sharecol=true
column.16.textmode=true
column.16.valuefield=Changed PCD 3
column.16.valueformat=customDataLabelsAsString
column.17.sharecol=true
column.17.textmode=true
column.17.value= Actual:
column.17.valueformat=HTML
column.18.displayname=
column.18.linkedname=direct
column.18.namekey=actualCompletionDate
column.18.querysort=actualCompletionDate
column.18.textmode=true
column.18.valuefield=actualCompletionDate
column.18.valueformat=atDate
Thanks for any feedback! Regards, Steve Steven Hirsch The Estee Lauder Companies Inc
