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.
- All records that score a 90 or above in ALL of the three score fields. (This one is simple to create)
- 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:
- A record has a score of 80, 80 and 95. It should not qualify for report #1, but should qualify for report #2.
- A record has a score of 78, 82 and 80. It should not qualify for report #1, but should qualify for report #2.
- A record has a score of 92, 95 and 98. It should not qualify for report #2, but should qualify for report #1.
- 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.