Is there a way to force the Analytics REST API to return groupings where all the values are zero?

Question

I'm programming an HTML SPA which consumes data from Salesforce. I'm a web developer by trade, and this is my first experience working with the Salesforce API. Another company are doing the report development.

Much of the data is in the form of reports, queried through the new analytics REST API. The reports are mostly in either tabular or matrix format.

We've run into an issue where if a vertical or horizontal grouping returns no results, the entire grouping disappears, meaning we can't build a dynamic UI and have it feature meaningful 0 values.

For example in one report we have a row for each month, and four columns of data. If all four values are zero, the entire month is omitted from the report, instead of correctly displaying the zeros. (This example is easy to fix, because the months in a year don't change; other examples have categories which we can't as easily hard-code).

I am assured by the SF developers that this is just how it works, and absolutely cannot be remedied. I don't have any reason to doubt them but wanted to check before implementing a lot of brittle code in the UI to handle the issue.

Does anyone know of a way to get the Analytics API reports to return rows/columns (groupings) of zero values?

(Cross posted from Stackoverflow, in the hope of getting an answer).

Answer 1

I don't have specific documentation to support this, but the developers you spoke with are right: the SF reporting engine does not include summary rows for which there is no underlying data, and there is no way to change that behavior.

You have two possible work-arounds:

1) Ensure that there's always data. This is rarely possible given the variety of parameters you may need to filter by, but I've seen it work in a few situations. For example, if you want to make sure that a report on 'revenue earned last year by month' report includes a summary row for each month in a year, create a dummy/fake Opportunity record for each month of the year with an amount of 0. These records will be pulled into the report (and summarized) but won't affect these total revenue calculations. They would, of course, throw off average calculations, though.

2) Use the report meta-data to figure out what the other summary rows should be. When you load a report with the Analytics API, you can inspect the design of the report and then reconstruct the missing data. Check out the extendedMetadata object:

var report = loadReportData('/services/data/29.0/analytics/reports/' +
     '<report ID>/instances/<instance ID>');
console.log(report.extendedMetadata); // show the full metadata object

From there, you can identify the field name and data type and deal with it appropriately. If relevant, you could use the normal REST API to build a dictionary of expected rows (for example, by making a describe call to see what picklist values are available on that field). Definitely a headache, but it could be done.