7

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:

enter image description here

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?

2
  • Is your new object also a master-detail of opportunity?
    – Jagular
    Apr 19, 2015 at 17:14
  • No it is not a master-detail. I thought I could solve my issue with Joined Reports or Custom Report Types. But reading your answer, it doesn't work to join some fake stuff into some real stuff. That was my plan as Matthew Lamb said: map a second data point into the report. So in the end I would relate it however it's needed. My gosh, it would only be a checkbox in Salesforce and for sure not much lines of code to simply draw a Zero into each month on their end. Who ever could like non-linear and quasi-random scaled X-axes? I can't imagine this is a feature... :-(
    – Uwe Heim
    Apr 19, 2015 at 18:04

1 Answer 1

5

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).

3
  • Thanks Jagular, I'll keep this question open for a while to inspire people to come with more ideas. Your approach will work but at least duplicate the amount of RevenueItems because of their shadow I guess. In production we'll face a huge amount of records.
    – Uwe Heim
    Apr 19, 2015 at 21:40
  • The number of records is clearly a potential problem with my approach. I look forward to seeing how others approach this issue.
    – Jagular
    Apr 19, 2015 at 22:41
  • Other than writing a wrapper, it seems there's no other way :(
    – MnZ
    Oct 12, 2016 at 8:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .