0

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?

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 '20 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. – Sander de Jong Dec 9 '20 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.