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?