How to Report on Salesforce Notes & Attachments

Many clients have asked how they can report on data appearing in the Notes & Attachments related list, and I always point them to one of several ideas on Salesforce’s IdeaExchange and explain that we can create custom objects to replace this functionality. The downside is that we need to create a workaround to begin with, making Salesforce look a bit wonky. I’m proud to announce that with two custom objects, a handful of custom fields, and a free-to-use or existing ETL tool, standard notes and attachments are reportable.

Theoretics

Salesforce provides API access to the Note and Attachment standard objects, along with all of the fields for each. While these are not reportable via the Report Builder, administrators have been able to perform exports to users and business analysts for greater analytics. By using this API access with a free, schedulable data extraction tool like Jitterbit’s Data Loader or Informatica’s Data Cloud, administrators can perform exports of the Note and Attachment objects and load records to custom objects for which reporting is available. My test example using Jitterbit has two queries to dump all note and attachment records to files on the desktop (or server), and two upserts to custom objects against the original record’s ID (an external ID text field) that I have set to run daily.

By using the Record ID and Owner ID fields of these records, administrators preserve the All vs My reporting standards and can write button-hacks to launch a report displaying all notes for a particular record (e.g. Account ID = null and the pv0?={Account.Id} parameter; see image below). Users are able to utilize these workarounds to see a full list of attachments and notes for a record, or for all of their notes and attachments in Salesforce.

Don’t Drink the Kool-Aid Yet

While Salesforce does not provide reporting for these, it is for a good reason. Parent ID on the Note and Attachment object act similarly to the What ID and Who ID fields on Activities; these are able to look up to virtually any object in Salesforce. Building a report type on these is incredibly difficult if you were interested in seeing all notes and/or attachments for “my accounts”. The workaround is great for providing business analysts and other specified roles the ability to grab the data from these objects, though caveat emptor.

There are a few considerations to review before proceeding.

• Duplicate data is created in an org, which will impact data storage limits.
• Users may see a delay between entering a new record and it appearing in a report.
• Client-based ETL tools will require maintenance and monitoring by administrators.
• Duplicate objects for standard objects is generally frowned upon, because it presents trouble for user experience about where to log their data.

Second-Class Objects in Salesforce

Business needs for notes and attachments reporting exist, whether it be for users via Report Builder or to include data for merged documents via Conga Composer, and this is one of the solutions that an administrator can easily build and maintain. Salesforce’s product management has not remarked on this widely popular idea in over a year and I do not expect to see any reporting made available for these objects in the near future. As more attachments go to Chatter and notes from calls go into completed tasks or events, administrators need to make a decision: do we still need to report on these, or can we train users to leverage other Salesforce features?

Want to discuss more or the specifics about my solution? Feel free to leave a comment on our Facebook page, below in Disqus, or via Twitter @RogerMitchell and we can review in greater detail.