Work in Progress Reporting Help

[JustinEn]

I'm trying to create a second report, similar to the screenshot below, but of our non-marketing Partners projects. I copied the report I made and changed the filter from EQUAL to NOT EQUAL with the same criteria, but the OR in-between each statement seems to be throwing it off and including all partner names. If I include the NOT EQUAL filter and try to stack multiple partner names with AND, it only takes one of those filters and kicks the rest out. 

I included the filters I used to create this one, which are basically a CRITERIA OR CRITERIA repeated 5 times. For our non-marketing Partners, we have 26 of those, so recreating that is not impossible but is time consuming. Does anybody know of an easy way to flip these filters to get the data I need?

[skyehansen]

I have never found the screenshot of filters to be very useful. I'd prefer people to copy and paste whatever is actually in the filters section, and this is how I have usually seen it done in this forum.

If you can take a moment to edit your report (the working one) and then convert the filter to all text and then copy and paste it into your post -- along with another copy/paste of the changes you made, this would provide a clearer context for folks trying to help you.

[JustinEn]

Not sure how to edit the post - but below is the text mode of the filters from my report. I'm trying to invert the filters to exclude these partner names from a secondary report so we can see Marketing vs. Non-Marketing related work. 

DE:Partner Name=Brand & Agency
DE:Partner Name_Mod=cieq
groupID=4e8cbba6002547e9b75ef2bbd8cbb7b0
groupID_Mod=in
status=CUR
status_Mod=in
OR:1:DE:Partner Name=Strategy & Campaign Planning
OR:1:DE:Partner Name_Mod=cieq
OR:1:groupID=4e8cbba6002547e9b75ef2bbd8cbb7b0
OR:1:groupID_Mod=in
OR:1:status=CUR
OR:1:status_Mod=in
OR:2:DE:Partner Name=Marketing Operations & Technology
OR:2:DE:Partner Name_Mod=cieq
OR:2:groupID=4e8cbba6002547e9b75ef2bbd8cbb7b0
OR:2:groupID_Mod=in
OR:2:status=CUR
OR:2:status_Mod=in
OR:3:DE:Partner Name=Demand Center
OR:3:DE:Partner Name_Mod=cieq
OR:3:groupID=4e8cbba6002547e9b75ef2bbd8cbb7b0
OR:3:groupID_Mod=in
OR:3:status=CUR
OR:3:status_Mod=in
OR:4:DE:Partner Name=Corporate Communications
OR:4:DE:Partner Name_Mod=cieq
OR:4:groupID=4e8cbba6002547e9b75ef2bbd8cbb7b0
OR:4:groupID_Mod=in
OR:4:status=CUR
OR:4:status_Mod=in

[skyehansen · Accepted solution]

Thanks for this. By the way, I am making the assumption that your Partner Name field is a text field, so I am answering with that in mind. (You would have had less issue had you made this a dropdown field, but I can't really speak for what's allowable in your workflows.)

Your report filter above, is a combination of 4 separate filters. Each one searches independently of each other. As you can probably intuitively see, this works great if you are looking for SOMETHING.

e.g. in a Fruit Report:

Filter 1: look for apples. OR

Filter 2: look for pears. OR

Filter 3: look for grapes. OR

Filter 4: look for bananas.

Each filter executes in turn, and pulls in apples, pears, grapes and bananas. (not a perfect explanation but here you go)

But now, you're asking for the opposite and your filter set is being built as follows.

Filter 1: No apples. OR

Filter 2: No pears. OR

Filter 3: No grapes. OR

Filter 4: No bananas.

As you can hopefully intuitively see, each filter will execute in turn. Filter 1 will bring in pears, grapes and bananas. Filter 2 will bring in apples, grapes and bananas. Filter 3 will bring in apples, pears and bananas. Filter 4 will bring in apples, pears and grapes. Add oxford commas throughout.

I'm going to suggest a solution to you but I'm going to leave it to you to test and then report back if it works.

First: read up on "AND" statements, for example:

https://experienceleaguecommunities.adobe.com/t5/workfront-questions/filter-not-blank-and-not-x/m-p/...

I think/hope this will end up being the answer to your problem. You need to filter with ANDs instead of ORs. i.e. one filter that says no apples, pears, grapes, AND bananas.

Your experiment would end up looking like this (who knows if this actually works... you'll have to test it out):

DE:Partner Name=Brand & Agency
DE:Partner Name_Mod=cine

AND:1:DE:Partner Name=pears

AND:1:DE:Partner Name_Mod=cine

AND:2:DE:Partner Name=grapes

AND:2:DE:Partner Name_Mod=cine

AND:3:DE:Partner Name=bananas

AND:3:DE:Partner Name_Mod=cine

groupID=4e8cbba6002547e9b75ef2bbd8cbb7b0
groupID_Mod=in
status=CUR
status_Mod=in

If this does work, remember to leave it in text mode. It will NOT convert back to standard.

[JustinEn]

Thank you Skye - very helpful. I'll test this out next week. One question: Should I build a reverse filter of "Not equal" to 1 partner name, and then convert to text mode and add in the additional AND statements?

Our "partner name" field is actually a calculated field that pulls from the Queue Topic name on our Request queue. 

[skyehansen]

You absolutely can do this. That's how I ended up deciding that "cine" would work, but yes, ideally if you were building it yourself (I recommend you do!), this is EXACTLY how you would do it -- check and see what the filter would be for one, and then convert to text and add on the others.

Great job. Good luck with your testing.

[JustinEn]

Thank you so much Skye - I created the text mode using AND statements this morning and it worked!!