1

Let's say we have a custom object called 'Business Reviews' (BRs) which has a lookup (Consultant__c) to User.

Now would like to have a [lightning or classic] report that can show all users that were created in the >=4 months ago that have <= 2 BRs. Any other users have to be filtered out from the report. There isn't a Master-Detail reln between them so I can't use roll-up fields.

I tried a few things (group summary field, row formula, cross filter, joined report, new custom report type) but was not able to do this nicely.

Any tips?

2 Answers 2

0

I don't have a nice answer, but it is an answer. My company had a similar problem and we solved it by creating a custom field on User, called Number of Business Reviews. We maintain this field via an Apex trigger on the Business Review object, but it can also be done with a process.

And then you have to run a one-time job to fill this field initially for all Users.

It is definitely not a nice solution, but it is a solution.

2
  • yes, definitely don't want to go down the apex trigger route. It is shame an idea to filter on summary fields in reports has been open for 12+yrs and 21,000 points.
    – MarcLee
    Dec 9, 2020 at 15:44
  • Yes, it is not a preferred solution, but when it is the only one, you have no choice. Hopefully someone will come up with a solution that does not involve Apex. Dec 9, 2020 at 15:46
0

I will put down what I did if it helps anyone. Basically created a new Custom Report Type 'Users And Business Reviews' -> choosing to show Users with and without Business Reviews.

Then basically created a formula on Business Review which would default to 0 if the Business Review was not completed and 1 if it was (sort of like Power Of One).

Then created a summary formula in the report, summing up the Business Reviews and showing the Sum on the dashboard as a table. In our case we are only interested in seeing the data for new starters (people who started in the last 3 months) so it is not too big a list.

The issue of course, as I alluded to in my OP, is that there is no way to filter the list to show only people that have summary formula = 0. This is an outstanding 'idea' for the last 12 years and 21k points: https://trailblazer.salesforce.com/ideaView?id=08730000000Brh8AAC

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .