collections reporting help for projects missing a specific expense type

[catherineh70969]

I need to report on projects that are missing specific expense types. For example, if merchandise was included in a project's statement of work, we expect to see something entered with the expense type "Merchandise". I've set up a filter below on a project report to show me projects where merchandise was sold but no Merchandise expenses were logged. The results in showing me projects with Merchandise expenses on them. I've tried notin and cinotin as the modifier but get the same result. What am I missing? DE:PDS | Assets sold in this program=Merchandise DE:PDS | Assets sold in this program_Mod=in expenses:expenseTypeID=5a4ba30b0062e0c3e344cc5c23ac4371 expenses:field_Mod=notin Catherine Hayes Manager, Business Analytics and Program Operations The Channel Company Westborough MA

[MelindaLa1]

This is a great opportunity to use the EXISTS syntax. What that filter is currently looking for is projects where ANY expense is NOT marketing. What you want is that no expense that is marketing is on the project. https://experience.workfront.com/s/article/Create-Complex-Text-Mode-Filters-Using-EXISTS-Statements-... Here's my quick attempt to get you started. I have not tested it. DE:PDS | Assets sold in this program=Merchandise DE:PDS | Assets sold in this program_Mod=in EXISTS:a:expenseTypeID=5a4ba30b0062e0c3e344cc5c23ac4371 EXISTS:a:expenseTypeID_Mod=notin EXISTS:a:$$EXISTSMOD=NOTEXISTS EXISTS:a:$$OBJCODE=EXPNS EXISTS:a:projectID=FIELD:ID Melinda Layten Technical Project Manager - API and Integration Workfront

[catherineh70969]

Thanks very much @Melinda Layten It's showing me projects with no or merchandise only expenses, but I'll play around with it. I appreciate you pointing me in the right direction! Catherine Hayes Manager, Business Analytics and Program Operations The Channel Company Westborough MA

[MelindaLa1]

Change notin to in. I've got a double negative, not exists not merchandise. Melinda Layten Technical Project Manager - API and Integration Workfront

[catherineh70969]

That did it!!!! You rock! Thanks @Melinda Layten Catherine Hayes Manager, Business Analytics and Program Operations The Channel Company Westborough MA