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

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 pm@gml.com 10.00
Donation 2 Peter Mckone pm@gml.com 35.00

Donation 3 Peter Mckone pm@gml.com 25.00

ContactId : Cxxxxxxxx111 30.00

Donation 4 Steve Ronald st@gml.com 10.00

Donation 5 Steve Ronald st@gml.com 20.00

ContactId : Cxxxxxxxx777 40.00

Donation 1 Bob Kat bk@gml.com 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 pm@gml.com 70.00

ContactId : Cxxxxxxxx111 Steve Ronald st@gml.com 30.00

ContactId : Cxxxxxxxx777 Bob Kat bk@gmail.com 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.

Best Answer

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)

Related Solutions

[SalesForce] Report that groups same record on 2 date fields

What if you created a Master Detail relationship between Service__c and a new object, Month__c. This new object has the Service__c field for it's parent record, and a Date field.

For every Service__c object, there exists a Month__c detail object for every month you want to count the Opportunity as "in service". Maybe the field should just be a string like '2012-12' instead of a Date datatype.

Use Batch Apex to create the initial Month__c records by iterating through all Service__c objects.

If a Service__c object is deleted for some reason, all of it's related Month__c records will go with it.

Add a trigger on Service__c to create Month__c objects for new Service__c objects or if either Contract_Start_Date__c or Date_to_Disconnect__c change.

Now you can create SOQL queries on Month__c to get the SUM of records for each month, and the total MRR. You could add one or more formula fields to represent whether the parent Service__c is a connect or disconnect in the given month, and your SOQL could include the sum of connects and disconnects.

For example, the detail records for a contract that starts in July 2012 and ends in November 2012 would look like this:

 Date      Connect  Disconnect
2012-07       1          0
2012-08       0          0
2012-09       0          0
2012-10       0          0
2012-11       0          1

It might not be the most elegant solution, since you end up with a lot of Month__c objects, but this seems like something that can't be easily calculated in a SOQL query. After the initial setup, it should be easy to maintain the Month__c objects using triggers on Service__c.

[SalesForce] Setting up a Summary Report the right way to use in Analytic Snapshots

I'd suggest you look at two different references. The first being Chapter 10 of Force.com Platform Fundamentals. The other reference I suggest you look at is the analytics workbook.

You said:

I set up my report to show me all sales made in the last month, and then set the analytic snapshot to run on the first day of each month.

and

However, how do I create a Year or Month field in the summary data in the report? Here are the options: * Group by Month - the summary label shows as "January 2013" - when that gets into the Target Object it is just text and it is not sortable by month. * Group by a formula field giving me the month value or the year value - that would be great except that you have to summarise that data, so even if you choose max or min you get "max 1" for January or "max 2013" for 2013 - these would have to go into a text field in the Target Object.

It sounds as though you may need two different reports or else may need to do either a joined report or matrix report of some kind (you don't say what kind of report you were trying to create). A detailed report on what happened last month is quite different than a rolling summary report that compares the previous month (relative to the current month) to the last 12 months or current calendar year (depending on how you set it up).

It also sounds as though you entered your dates as text rather than allowing the report builder to group the periods (and data) for you by month or somehow "bucketing" them (the latter I'd expect to not be the way to go). Without knowing more about your object and the fields you're trying to report on, I don't think anyone can provide you with more detailed help other than pointing you to the two references I've listed above.