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
.
I'm going to venture a guess that your first two reports are standard report types so they automatically update with all the fields. The last report type sounds like a custom one where you have to manually add the fields to the report type.
Here's the help article on how to add fields to your custom report type.
Best Answer
as mentioned in the other answer you can follow up with Salesforce support on why the formula gives different results between the report and the detail page.
meanwhile to get consistent behaviour you can use ISBLANK function on the date fields before doing the comparison .. something like this..