Reporting Question

Question

I have an interesting report I need to write, and could really use your help.

Here's my problem. I have Contact records in my instance with a few different scores applied to them. For the sake of simplicity, we'll just say it's three unique fields with possible values of 0-100

I want to separate my data into two different groups.

1. All records that score a 90 or above in ALL of the three score fields. (This one is simple to create)
2. All records that score at least 75 in each of the score fields; however, it must exclude all of the records that are pulled in the first report. Meaning the records also cannot score a 90 or above in ALL three score fields.

Here are some examples to help illustrate:

1. A record has a score of 80, 80 and 95. It should not qualify for report #1, but should qualify for report #2.
2. A record has a score of 78, 82 and 80. It should not qualify for report #1, but should qualify for report #2.
3. A record has a score of 92, 95 and 98. It should not qualify for report #2, but should qualify for report #1.
4. A record has a score of 70, 65 and 74. It should not qualify for either report.

What is the best way to accomplish this? Again, I need two separate reports, with absolutely no overlap in the contact/lead records that are pulled.

What I ended up doing to solve this was create the first report, and I used Data Loader to load a specific value into a field that is not being used. Then in report two, I just filtered those records out. That being said, it would be nice to have something more dynamic - meaning I don't have to update the top records each week/month/etc.

Answer 1

Create a formula field:

UPDATE: More efficient formula by @sfdcfox from the comment below:

IF(
 AND(Score1__c>=90, Score2__c>=90, Score3__c>=90),
 2,
 IF(
  AND(Score1__c>=75, Score2__c>=75, Score3__c>=75),
  1,
  0)
)

Original formula:

MIN(
 IF( Score1__c < 74, 0,
  IF( Score1__c < 90, 1, 2)
 ),
 IF( Score2__c < 74, 0,
  IF( Score2__c < 90, 1, 2)
 ), 
 IF( Score3__c < 74, 0,
  IF( Score3__c < 90, 1, 2)
 )
)

Your report filters would be MyFormula equals 2 for Report1, and MyFormula equals 1 for Report2

Answer 2

If you determine the calculation, then create a formula field to build up the logic and then filter based on formula field value.

Formula logic will be like this (be sure to check braces)

IF(Number1__c=80 && Number2__c=80 && Number3__c=95,'Report1',
    IF(Number1__c=72 && Number2__c=82 && Number3__c=80,'Report2',
        IF(Number1__c=92 && Number2__c=95 && Number3__c=95,'Report1',
            IF(Number1__c=70 && Number2__c=65 && Number3__c=74,'NA',
            )
        )
    )
)

And then in the first report, filter with formula value = 'Report1' and in the second report filter with formula value = 'Report2'