3

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

3

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.

|improve this answer|||||
  • Benj - Many thanks. The developers suggested #1 but the client wants to filter the reports, which increases the chance of meaningful zeroes to the point where he would need too many fake records. We'll look in to #2. I'm going to leave this open for a bit in case anyone else chips in, but will otherwise accept this answer in a few days. – Jude Fisher Dec 5 '13 at 18:28
  • Quick follow-up, if possible: would we have this problem if we simply queried using SOQL - that is, is it only the Analytics API that won't return zero columns/rows? – Jude Fisher Dec 5 '13 at 18:45
  • 1
    Unfortunately, the same would apply with SOQL. The underlying problem is that you can only see values that you DO have, not ones that you don't. Having said that, you could theoretically run a query or report on the full data set and cache that to get the full list of possible summary rows. When a user filters the report, use the cache to build the display and then pop in the actual data from the report instance. – Benj Dec 6 '13 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.