Report Question - Show unique relationships?

[Justin PR]

I have 2 related objects, 'Projects' and 'Users'.  A user may have many projects (or none at all), but a project only has one User (and must have one).

I'd like to create a report (that will ultimately become a dashboard metric) that shows how many unique users there are amongst all projects.  Is there a way to derive this total from a report?

I can get the data from a summary report, but can't get a total from it without exporting or manually counting.  A list of all users won't work because there are users with zero projects.

Is this possible to do?

[tt · Accepted solution]

You can use a reporting trick to get the number of users:

http://blogs.salesforce.com/analytics/2006/08/the_power_of_on.html

Do you want users without projects counted also?

if the "users" are a custom object, then you can create a custom report type of

users

with and without

projects

and then you can report on users and projects, and you'll get a count of users from the trick above. You can get a count of projects using record count.

[Justin PR]

I never knew about the hack to make this work and have been trying to solve this reporting issue for years.

Thank you!!!