Salesforce Exception Reports

I think it’s fair to say that consideration of reporting and analytics is not traditionally a technical architect’s direct concern. In the Salesforce context I always challenge this presumption and promote the idea that a project architect should absolutely be looking at the high-level analytical requirements during the foundation stage of the project. Why you may well ask? The answer to this is simple, in order to design a fit-for-purpose data model you must consider data coming out (via the native reporting tools) as well as data going in efficiently. I make this point in a prior post that outlines my view on the Salesforce development process. The number of projects I see where this thinking hasn’t been applied and 3rd party tools are wheeled in to address the gap continues to surprise me. Key to success here is understanding what can be achieved with the Salesforce reporting functionality. Which brings me on to the topic for this post – exception reports, a type of reporting not obviously catered for by the standard reporting tools. For instance – show me a list of Accounts without an Opportunity in the last year. Such reports are invaluable in identifying omissions, focusing sales activities, generating call lists, tracking inactivity etc. etc.

I’ll use 2 examples below to clarify how to approach such a scenario.

Simple Example.
Accounts without a closed Opportunity in the last 30 days.

For this report we use a “without” Cross Filter as per the screenshots. Cross Filters were added in Spring ’12 and enable records to be filtered based on the existence (or non-existence) of related records which meet defined criteria. Within a Cross filter up to 5 sub-filters can be defined. Note, the report’s top level filters do not apply at the sub-filter level. Cross filters can be added for any related object, custom or standard – the limit is 3 per report (each with up to 5 sub-filters). Powerful functionality indeed and a great convenience that avoids a Custom Report Type for the outer join, note the sub-filter is limited to fields on the related object, no-cross object sub-filter logic is possible.

simple exception report

Complex Example.
Accounts without a Won Opportunity involving a certain Product Family in the last 60 days.

For this report we start with a Cross Filter report to satisfy the “without Won Opportunity” requirement. From there we need a indicator (of the existence of an Opportunity Product linked to a product of the specified family) at the Opportunity level which can be used in a sub-filter. This is actually straightforward to do and relies on the old school technique for exception reports, namely the use of a rollup summary fields and simple workflow.

Solution components :
Opportunity Product – Custom Field – ProductFamilyHardwareFlag
Number (18,0 default 0)

Opportunity Product – Workflow Rule – “Opp Product – New or Changed Product”
Evaluate – Created and Edited – Formula (OR(ISNEW(), ISCHANGED( Product2Id )))

Opportunity Product – Workflow Action – “Opp Product – Set ProdFamilyHardwareFlag”
Field to update [ProductFamilyHardwareFlag] with Formula = IF( TEXT(Product2.Family)=’Hardware’, 1,0)

Opportunity – Custom Field – ProductFamilyHardwareCount
Rollup Summary Field (Opportunity Product – SUM – Field to aggregate = ProductFamilyHardwareFlag). All records included.

How does this all work? In short, a workflow rule on Opportunity Product sets a numeric field equal to 1/0 based on the product family meeting the defined criteria. The numeric field is then rolled up to the Opportunity level via RSF, which is then applied in a sub-filter as below.

complex exception report

Note, this is just an example to illustrate the concept. An actual implementation would typically require a more generic approach.

Salesforce Development Process

There are typically two interpretations of the term “development process” – one being the tools, practices and methods applied in software development (i.e. methodology, plus build automation, standards etc.) the other being the process applied to get from requirements to working software (i.e. iterative or waterfall, plus how the analysis-design-build-test-release disciplines are executed). This post outlines one high-level approach to the latter in the context of Salesforce developments. The intent of this isn’t to be overly prescriptive, generally speaking each project requires its own defined process that factors in resources available (and their skills and experience critically) plus the nature of the work and the timescales. That said it is a truism that failed projects fail for a variety of reasons but successful projects are typically successful for the same reasons. A fundamental success factor being the adoption of a clearly defined and simple process – others being team empowerment and shared commitment.

The process above assumes an iterative process and focuses the initial iteration on the foundation of a robust data model, set of user profiles and permission sets, role hierarchy, record access model and statement of the reporting requirements for the project. Subsequent iterations improve the quality of the foundation over time, as new functional areas are developed. The data model in this context will include a statement of the org-wide defaults for each object and the specifics of each relationship (master-detail, lookup, mandatory lookup etc.). The record access model is critical – this shows how each user population maps to a user profile and role and how they gain access to the data required, i.e. sharing rule, Apex managed sharing etc. In my experience defining an approximate access model upfront and then refining during the feature build-out helps to avoid expensive refactoring later in the process and sets out a clear understanding for all contributors to the declarative and technical build. A piecemeal approach to defining a sharing model is commonplace – this rarely provides a clear and cohesive result. For the similar reasons defining a list of permission sets upfront ensures that user profiles are kept clean and focused, avoiding proliferation of profiles down the line. It may be surprising to see analytics such as reports and dashboards being considered during the foundation stage, this however is one of the primary inputs to the definition of a fit-for-purpose data model. I’ve worked on countless projects where reporting has been overlooked until a late stage, at which point it has become apparent that standard reporting features can’t produce the reports given the structure of the data. Ideally the data model should be designed from the outset to work well for both transaction processing and analytics.

A final point for consideration is the by-exception approach to identifying technical components. When breaking out the solution components required for a certain feature – expertise must be applied to ensure that standard product functionality or declarative options (workflows, reports etc.) are considered fully before bringing expensive technical options such Visualforce or Apex to bear.