# Reporting 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.

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

• `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))` is slightly less heavy but should be the same thing. Feb 28 '18 at 4:51
• It’s a lot lighter, I like it! Feb 28 '18 at 4:59

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'