Is it possible to create a chart that shows every month even when there is no data?

Question

I've created a report chart to show the number of enquiries (custom object similar to opportunities) that we receive per month from each account.

The report is a matrix and we group enquiries by month.

PLotting all enquiries for all accounts we get a very nice, accurate graph, showing volumes.

However when it comes to filtering down to an individual account we get a graph that only shows values for the months that we have had enquiries.

I pass this via SF support and they escalated it a couple of tiers and said "No can do".

So how can I get all the missing months in the graph below?

So - Matrix on Accounts with Enquiries with created date grouped by month

Thanks for any thoughts

Answer 1

SF support is right if they say there's no elegant way of creating such graphs. Salesforce reporting is a very powerful tool, but sometimes lacks seemingly basic features. If the report is really really crucial for your business, you can take a more expensive approach.

1. Create an analytic snapshot

You could create a summary report using a custom report type for Accounts with and without Enqueries, counting Enqueries for each Account every month end (or with any density you wish). Later you could easily report on the gathered monthly data, including zeros in the proper places.

2. Use Visualforce for building custom reports and chars

Another option is creating a Visualforce page with a custom APEX controller and building reports and charts of any shape using HTML, apex:chart or great Google Charts. This approach gives you greatest flexiblity, but is also most costly in building and maintaining.

Answer 2

You could make sure there is one "placeholder" Enquiry for each Account in each month in your data. To avoid counting the placeholder in your totals, create two new fields on Enquiry:

Enquiry Type (picklist with values "Normal" and "Placeholder"
Enquiry Count (Number formula that equals 1 if it's Normal and 0 if it's Placeholder)

Then in your reports, take the SUM(Enquiry_Count__c). For empty months, this will be a 0.

In order to set all this data up, I suggest an Apex Scheduled Batch process to go through all Accounts and create Placeholders as far ahead as you need them. This would only need to run once a month.