Showing if a project was ever in On Hold status in a task report column

[Madalyn_Destafney]

Hi folks, seeing if anyone has a way to do this...

I have a task report pulling values from a task custom form on all projects via templates that indicates if the project met or didn't meet the SLA (based on the task custom form looking at when a certain set of tasks in the project was completed vs the SLA). I have this working fine, and reports with bar charts show projects that didn't vs. did meet the SLA. What I'm after is if any the projects ever when to On Hold status any point in the project. I want to add this as a report column and not a filter since I don't want to filter them out, but just to give context in a column to show for example XYZ projects that didn't meet the SLA, the column can show me if it ever went On Hold (not tasks, the project itself). Is this possible in a text mode value expression in a column? I tried messing with cicontains=On Hold and project:noteText but not getting anywhere.

[KellieGardner]

For system updates like status you'll want to pull Audit Text instead of Note Text. However there seems to be a glitch in the matrix, I'm able to get this to pull in comments made by users

displayname=User Updates
listdelimiter=<p>
listmethod=nested(updates).lists
textmode=true
type=iterate
valuefield=updateNote:noteText
valueformat=HTML

But I'm not able to get this to pull in any system updates by changing the note text to auditText even though it's listed in the same list of the API Explorer

displayname=System Updates
listdelimiter=<p>
listmethod=nested(updates).lists
textmode=true
type=iterate
valuefield=updateNote:auditText
valueformat=HTML

Additionally, every time I change valuefield to valueexpression on the noteText that was previously working it returns no results.

[Madalyn_Destafney]

Hi there, yup I failed to mention that I had already tried Audit Text and wasn't getting any results...so neither 'text' option was working for me but I was thinking I just needed to fix my value expression in the column. I wonder if I just need a separate report that pulls projects ever in On Hold status and need to look at it separately instead of in same report like I was after. I'll see if I continue to get these (no) results on my end but thanks for confirming it's Audit Text, that's what I was thinking but couldn't get anything to work even with projects as an example that I knew when on hold.

[KellieGardner]

I also searched high and low in the forums for any details and couldn't find anything resembling someone successfully doing it in a project report. 

It might just need to be a separate Journal Entry report that shows that status changes in comparison to a project report. I'll keep testing and see if I can come up with anything.

[Madalyn_Destafney]

Hi Kellie, thanks for spending time on this, don't worry about it. I decided to just make a separate project report that pulls any project that went on hold (along with other filters) and was able to do that, was just hoping there was a way to see this within my task report of the SLA info (even in the task report I was adding 'project:' in front of relevant value expression rows and still nothing, kept giving me an error that project:updateNote:auditText or project:allNotes:auditText was not a valid parameter). I am able to use All Notes > Audit Text > contains 'On Hold' in my standard report filters so am just rolling with that, I guess it just doesn't work in a column value expression.

[AmyReilly]

Hi Madalyn, would it work for you to capture the timestamp of when the project went to ON HOLD in a custom field?  Then, you could add that custom field to the report as a column.  If the field were blank, you would know that it had never been on hold.

Timestamp info:
Getting timestamp from status changes - Adobe Experience League Community - 466691

[Madalyn_Destafney]

Hi Amy, I was trying to avoid creating another field in our already-hefty form but yes, I could do that if the separate report I made capturing any project that ever went On Hold (via Audit Text contains filter) gets to be too much to reference separately. Will bookmark that thread, thanks!