How to do conditional selections in reports?

[chrigu1]

Hello together,

I am facing a requirement for a report in SAP Sales Cloud to filter opportunities based on field A depending on the value of field B.

E.g. Show all opportunities but the ones where Category = ABC and Status = Closed
In this case the report should contain opportunities where Category = DEF and Status = Closed

It looks like a pretty straight forward requirement but I can't get my head around how to solve this as to my knowledge the selections on fields always apply in an "AND" condition.

So what I am looking for is a conditional filter option within reports.

I appreciate any input,

Best Regards Christopher.

[former_member226]

Hi Christopher,

Got your point.

There are 2 options to do this:

1. A quick workaround would be to Execute the report with a filter as "Category=DEF" in selection screen and then apply a column filter as shown:

option1-0.png

option1-1.png

2. A long term solution would be to create a dummy "Restricted Key Figure" using "Counter" as base KF and Restriction can be defined as per your business need. Sample for pulling all opportunity which is still in sales phase other than "Close" is as attached. So when you include such a restricted KF in your report first level of filtering is already done by the system and then you can do another conditional filtering from the selection screen.

option-2.png

Thanks

Saurabh

[chrigu1]

Hi Saurabh,

your workaround would not come to the result above. Executing the report with selection "Category=DEF" would result in:
Name; Category; Status

Oppo3;DEF;Lost
Oppo4;DEF;Open

at this point, I lost Oppo1 which you can't get back with a column filter.

For your 2nd solution. If I build the KF as explained and set the restriction characteristic to "Status!=Lost" (to stick with the example above) I would get
Name; Category; Status

Oppo1;ABC;Open
Oppo4;DEF;Open

at this point, I lost Oppo2.

This does unfortunately not lead to my desired result.

Thanks Christopher.

[former_member226]

Hi,

You should be able to do this within reports Selection Screen. At forst you need to pull the fields to Selections creen if not already available as shown:

selection-fields.png

enable-field.png

Once done then you cna make use of FIlter value help -> Advance filter to apply "Is not"

selction-field-1-advance-filter.png

Finally the parameter can look like this:

final-selection-screen.png

And run the report.

Thanks

Saurabh

[chrigu1]

Hi Saurabh,

I think I wasn't clear in my explanation. Considering I have the following opportunities:

Name; Category; Status

Oppo1;ABC;Open
Oppo2;ABC;Lost
Oppo3;DEF;Lost
Oppo4;DEF;Open

Now I want to get my report to show:
Oppo1;ABC;Open
Oppo2;ABC;Lost
Oppo4;DEF;Open

The filter I want to apply here is: don't show opportunities with Status=Lost if Category=DEF

If I apply the filters as you showed on field Status isNot(Lost) and Category isNot(DEF) the result is:
Oppo1;ABC;Open

Hope that makes it more clear.

Regards Christopher.

[meenakshi_palaniappan2]

Hi Christopher,

Create variables for Status and Category. In advanced tab, you can maintain

Category is not ABC

Status is Closed.

So the report should show results for -> Category = DEF and Status = Closed

[meenakshi_palaniappan2]

Hi Christopher,

I think this can be done using "Variables" and "Advanced" tab - Choose option as "is not"....cusersmeenakshipalaniappacontactsdesktoptempvariab.png

[chrigu1]

Hi Meenakshi,

thanks for your reply. How would I create a filter like "is not (Status = Closed and Category = ABC) with the "Advanced" tab?