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

I've been asked to assist with creation of a summary / matrix report.

We have a custom object Service__c that holds all the gravy technical details of closed won Opportunities. There are 2 date fields:

  • Contract_Start_Date__c – roughly used to indicate when our monthly income goes up
  • Date_to_Disconnect__c – marks the day when the source of $$$ dries up

It's easy to build a summary report & chart with varying granularity (week/month/year) to see how the installs/disconnections impact the business. It's next to impossible (I think) to build a report to combine these two numbers (I think proper name is to get "incremental monthly recurring revenue" value). Basically SUM(installs) - SUM(disconnections). Something to show the higher-ups that we're gaining/losing the market.

Any idea how to achieve this with standard reporting engine?

What I got so far:

I think this would mean I need to have the Service record in the report twice for short-term contracts (a.k.a. self join in normal databases?) to be grouped by one or another date. Typical "viewing window" will be 1 financial year and most of services are 12+ month contracts but I can't really count on that.

I'm seriously thinking that VF charting or Flot will be the way to go. Should be real-time so not really analytic snapshots; and should be possible to embed the chart as dashboard component (chart means I can't use joined report, right?).

I've been thinking to base the report on field history tracking (both fields are tracked) but Service__c is in master-detail relationship to Account and we're welcome to upvote an idea, heh). This M-D is impacting us in several other places so if there's a brilliant idea I could try convincing the team that this is the last straw and we should convert it to a lookup (let's put aside the question how many other reports will it break…) But even with reporting on history fields being possible I'm not sure how I'd consider "amount" field to be treated as positive/negative depending on "which date falls into current month"… possibly with some formula field in the report and PARENTGROUPVAL-like magic?

Best Answer

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.

Related Topic