Reporting: Grouping by Date (Calendar Month) without omission of empty months

Question

I'm generating a series of RevenueItem__c objects, which are master-detail related to Opportunity. The series is sparse: there are month with and without RevenueItem__c objects. Even gaps of many years may happen, followed by some items later. Say we have some items in 2015 and 2016 followed by a gap until 2021 followed by a series from 2021 until 2025.

Now with standard reporting, the rendering is graphically missleading, because the x-axis is not linear. The gaps are causing a somehow random non-linear scale. Many people getting irritated and complain about such non-linear x-axes:

I'm using vanilla standard reports and report charts to inject them into layout - again just standard functionality. Standard-only is also my goal.

I found that it is not possible to avoid this behavior as described here: https://success.salesforce.com/answers?id=90630000000gkXDAAY - however in this article Matthew Lamb says that:

One way to trick it is to map a second data point that does have data across the entire range, then the blanks from your first set will show up.

My question is: how exactly this might work? I have created a second custom object RevenueItemVoid__c and created dummy records one by month for all months between 2010 and 2025 - that are 16 * 12 = 192 records.

How do I get them pushed into that report chart? I tried custom report types, joined reports, plot additional values, etc. but found only dead-ends maybe I'm not really a point&click developer by heart...

Also I don't figure which dummy relations aIhave to set on RevenueItemVoid__c, RevenueItem__c, Opportunity. I also don't want to create 192 item for each oppy. One pack of 192 should be the limit for the sake of db-memory-waste.

Any ideas?

Answer 1

As you have discovered, Salesforce is weak at reporting on missing data.

You might try creating an opportunity with one zero value revenue item in each of the missing months. I suspect that such an opportunity might distort other opportunity reports. It can, of course, be filtered out, but users will need to be instructed to do this.

In order to report on missing attendance, I had to create a new custom object. Each record represents a day of missing attendance for one site. I have one scheduled batch job that checks for missing attendance and another that checks each missing attendance record to see if it has been resolved. That was more complex than I would have preferred, but it does work.

In your case, you could have a custom object, RevenueItem_IncludeZeroes__c, where each existing Revenue Item would have a clone in RevenueItems_IncludeZeroes. In addition to the clones, you would need zero value records for the missing months. A single scheduled job could do the following:

1. Delete existing RevenueItem_IncludeZeroes__c records.
2. Clone existing RevenueItems into RevenueItem_IncludeZeroes__c records.
3. Scan the desired date range for months with no revenue items.
4. Create zero-value revenue items for missing months.

I don't know enough about your situation to tell you if RevenueItem_IncludeZeroes__c needs to be a master detail item or even if it needs to be related to the opportunity. In my case, I opted for simplicity and left the new object unrelated to existing objects (though it does contain enough info that I know what each records relations would be).