How to create a Summary Report, without repeating the same non-grouped column data?

Question

In our App, Opportunities object is used as "Donations" with custom fields. And our contacts make donations to our organization. So in salesforce terms, "Contacts" can make multiple "Donations" .

Now I am creating a summary report to show cumulative donations made by various contacts. Say for example if Contact A has donated 3 times then I am interested in his cumulative total and his contact details.

So my currently report looks like:

Donation Name First Name Last Name email-Id Amount Cumulative-Total

---

ContactId : Cxxxxxxxx123 70.00

Donation 1 Peter Mckone [email protected] 10.00
Donation 2 Peter Mckone [email protected] 35.00

Donation 3 Peter Mckone [email protected] 25.00

ContactId : Cxxxxxxxx111 30.00

Donation 4 Steve Ronald [email protected] 10.00

Donation 5 Steve Ronald [email protected] 20.00

ContactId : Cxxxxxxxx777 40.00

Donation 1 Bob Kat [email protected] 40.00

I have grouped the above summary report on Contact-Id, so that I could identify the various donations made by single contact. And Cumulative Donation is a formula field and is the Sum of all donations made by that particular contact. All the other fields are non-grouped fields.

Now as you could notice that all the non-grouped fields except Amount and Donation name, remain same for one particular contact, I would like to display them only once.

So my question is, Is there a way, where I could see the First Name, Last Name, Email-id only once, I am happy to give away Donation name and Amount field if required and do not display them.

so more or less my SUMMARY-Report would look like:

                        First Name   Last Name   email-Id   Cumulative-Total 

---

ContactId : Cxxxxxxxx123 Peter Mckone [email protected] 70.00

ContactId : Cxxxxxxxx111 Steve Ronald [email protected] 30.00

ContactId : Cxxxxxxxx777 Bob Kat [email protected] 40.00

As far as I know, there is no straight forward way to achieve this, but worth asking is someone has faced the same issue, and what was the workaround for it.

Many thanks.

Answer 1

I'm afraid it will look very ugly in a summary report. While you can have up to 3 groups in it (Name + Email + Id = cool), it will be displayed as nested groups.

What you can therefore do is to create a formula field on contact (type: text) that will have FirstName + ';' + LastName + ';' + 'Email' + ';' + Id and then group by it in the report. Combine it with 'hide details" (as pointed out by @Saariko) and you're getting somewhere... Still far from ideal I'm afraid.

You should be able to do it in SOQL easily enough, maybe this would be a valid option? I have no idea how your Contacts are linked to Opportunities/Donations so I'll go through standard link. You should manage to adapt it if you have Contact__c on Opportunity or something like that.

SELECT Contact.Name, Contact.Email, ContactId, SUM(Opportunity.Amount) sum
FROM OpportunityContactRole
GROUP BY Contact.Name, Contact.Email, ContactId
LIMIT 10

(As with each aggregated query, if your organisation uses multiple currencies, these results are guaranteed to be in your corporate currency)

Answer 2

It sounds like you might be tracking individuals in Salesforce instead of businesses. If this is the case, and you have Enterprise or Unlimited edition, you could enable Person Accounts. This merges the Account and Contact objects to give the benefits of both.

You could then create a new roll-up summary field on the Person Account which SUMs the Donation Amount field on the Opportunity (roll-up summaries are allowed between Accounts and Opportunities). Then build the report based on the Person Account to show First Name, Last Name, Email, and Total Donations (the roll-up field). This way you'll always only have one row of Person details as well as the total donated.

Some more info

http://emea.salesforce.com/help/doc/en/account_person.htm

http://login.salesforce.com/help/doc/en/salesforce_B2C_implementation_guide.pdf