Non-lookup linkages in multi-step reports

Table of Contents

Overview #

When linking two steps in a multi-step report, the two fields involved in the linkage must be of similar data type. If they both refer to an object’s record (e.g., both are references to the same object), the linkage is a lookup linkage, otherwise it is a non-lookup linkage. Lookup linkages are discussed elsewhere, and in this article, we discuss non-lookup linkages.

non-lookup linkage links two fields of the same data type in the two steps, where neither of the fields is a lookup field or record ID field. For instance, the two linked fields can be both picklists, both strings, or both dates. (So you cannot, for example, link a string field with a date field.) Moreover, if they are both date fields, and an “extractor” is applied – e.g., they extract the Month and Year, or the Day of Week – then the two fields must both use the same extractor; i.e., you cannot link a date Month and Year field with a date Day of Week field. But unlike lookup linkages, non-lookup linkages can be used on any objects, whether or not the objects have any explicit lookup relationships among them.

There are a couple of constraints with non-lookup linkages:

  • When a non-lookup linkage is used between two steps, those steps can have just that one linkage between them. This differs from lookup linkages, where two steps can include multiple lookup linkages between them.
  • Non-lookup linkages cannot be used for “highest-n” or “lowest-n” queries.

The most common use cases for non-lookup linkages involve creating comparison tables based on the field used for linkage. Below are a few examples. In the first example, there are multiple steps all of which use the same object; in the second, the steps use distinct, unrelated objects.

Example 1 #

As a first example, consider creating a stage-by-stage comparison of opportunity metrics – say, opportunity count and total opportunity value – for each of the last three years. We can create such a report with three steps. Each step retrieves the Opportunity Stage field and the two metrics fields, and each step is filtered to show just the data for the year of the step. Below is a screen shot of the report results.

The steps of the report are linked on the Stage field, which is a picklist. This linkage is, of course, a non-lookup linkage. Below is the linkage of Step 3 of the report.

Example 2 #

As another example, suppose we wish to compare Opportunity metrics, Payment metrics and Asset metrics side-by-side, based on month and year, for some specific time period – say, last year. An example report is below, showing the metrics for each month of the time period.

Similar to example 1, this report uses three steps, one each for the Opportunity, Payment and Asset objects. Each step retrieves the month and year of a date field: the Opportunity step retrieves the Close Date, the Payment step retrieves the Payment Date, and the Asset field retrieves the Installation Date. Each step also retrieves the corresponding metric values. The linkages then link the Close Date of the Opportunity object with the Payment Date of the Payment object and with the Installation Date of the Asset object. This example shows that you can select any date fields from any two objects and use them for a non-lookup linkage. Below is the linkage of Step 2 of the report.

Powered by BetterDocs