Contact us Blog

Multi-step reporting

Multi-step reporting offers a way to create reports that span any number of Salesforce objects, both native and custom. It lets you use separate filtering conditions for each object in your report. It also includes the ability to get "ranked" data, e.g., finding the top 10 opportunities by amount for each partner account, or finding the most recent pending tasks on each open case.

A multi-step report executes a series of steps, where each step retrieves data records from a single Salesforce object. The retrieved data is stitched together into a report using linkages, which are usually the key or lookup fields common between steps. You might think of a multi-step report as mimicking what a developer would do when hand-coding a complex report: construct and run series of database queries, and calculate the composite report by "lining up" (or joining) the results of those queries along the appropriate columns.

A short example

Suppose you need a report of all your Opportunities closing in the next month, including the Opportunity's name, amount and close date. Suppose also that for each opportunity record, you wish to include in the report the names and email addresses of all those related contacts whose role is "Decision maker." Below is a mock-up of such a report.
If you were to code this report using database queries, you might construct two queries, the first one retrieving the Opportunity records, and the second retrieving the contacts related to those Opportunities via the Opportunity Contact Role object. And if you were to create a multi-step report, you would do essentially the same thing: create a two-step report, as follows.
  1. The first step retrieves Opportunity records, with fields Opportunity ID, Name, Amount and Close Date, filtered so that the Close Date falls within the next month.
  2. The second step retrieves Opportunity Contact Role records. It extracts the Opportunity ID field from the Opportunity Contact Role object, and the Name and Email fields from the Contact object. This step links its Opportunity ID field to the Opportunity ID field of step 1.
The linkage set up in step 2 establishes the relationship between the steps, ensuring that we "line up" the Contact Role records retrieved by Step 2 with the corresponding Opportunity records retrieved by step 1.

Comparing multi-step and single-step reports

Apsona for Salesforce includes the single-step reporting feature, which can be used for simple reports. But the multi-step reporting add-on offers much more. Here are a few key distinctions between the two reporting tools.

What does a step do?

There are two kinds of steps: query steps and calculation steps. A query step is one that retrieves data from the database, and a calculation step is one that calculates using formulas. We discuss calculation steps elsewhere.

Each query step is designed to execute a single query, and retrieve records from one selected Salesforce object. The step can specify the fields to retrieve from the object, the filters to apply when retrieving those fields, the sort and range, and linkages. In specifying what a step does, you provide five pieces of information, organized in the user interface as five tabs:
  1. Step info, which provides a name and description for the step, and specifies the object from which data records will be retrieved by the step.
  2. Retrieved fields, which lets you specify the fields to be retrieved from the object and its parent objects.
  3. Filter terms, which lets you provide the filter conditions that should apply to the record retrieval. The filter conditions can use any fields from the step’s selected object as well as its parents or children. Calculated values such as number of records or total amount can be retrieved from the metrics section of each object.
  4. Range and Sort, which lets you limit the number of retrieved records. This tab also has the ability to get "ranked" data, e.g., the top five Opportunities by Amount associated with account, or the most recent 10 activities associated with a Contact.
  5. Linkages, which lets you relate the records retrieved in a step with those retrieved in previous steps. For example, you might retrieve your top five partner accounts in Step 1, and their corresponding most likely opportunities in the Step 2, and you link the Opportunities of Step 2 to the Accounts of Step 1.
There is no a-priori limit to the number of steps in a multi-step report. Some things to note about what each step can do:

More about linkages

A linkage aligns the records of two steps by relating two output fields of the two steps. The two fields to be related must meet either one of these two conditions: For example, you might link the ID field of the Opportunity object of one step with the Opportunity ID field of the Opportunity Contact Role object from an earlier step. You can even set up a linkage where both sides of the linkage are lookup fields, e.g., the Opportunity ID of an Opportunity Contact Role step with the Opportunity ID of an Opportunity Line Item step. But you cannot, for example, link an Opportunity ID field with an Account ID field, because those two are of different types.

Left joins and inner joins

In database parlance, there is the notion of "left" and "inner" joins. Suppose you create a first step that extracts records from a parent object - say, Campaign - and a second step that retrieves from a child object - say, Opportunity. With no filtering conditions, the resulting report will produce Campaign records even if they have no corresponding Opportunity records. This would be an example of a left join. If you wish to restrict the report to only produce Campaign records that have Opportunities, you will have to filter the first step appropriately, e.g., using a quantified filter.

Alternatively, you can create just one step on the child Opportunity object, and retrieve any fields you need from its parent (Campaign) object within that same step. This is possible because you can extract fields from parent objects within the same step, as noted above. When you extract the parent object's fields in a step for a child object, you will be producing an equi-join, i.e., your report will include only those records in the parent object that correspond to records in the child object.

Linkages across picklists

Notice that the above requirements allow you to also set up linkages across picklist values. For instance, you might create a step that shows total opportunity values by stage in 2013, by retrieving the Opportunity Stage and Sum of Amount (metric) values from the Opportunity in one step. And in a later step, you might retrieve similar data for 2014. By linking the two steps on the Opportunity Stage field, you can now show a single report containing total opportunity values by stage for both years.

Multiple linkages

Sometimes we would want to enforce more than one set of linkages. For example, suppose you wish to identify the influential contacts in your database. So you want a list of those Contacts who are part of your "Spring Outreach" campaign who were also decision makers on one or more opportunities obtained from that campaign. You could create a multi-step report that produces this list, with steps as follows.
  1. Get the Campaign Members. In the first step, retrieve from the Campaign Member object the Contact ID values for all the contacts in the "Spring Outreach" campaign. You can do this by filtering on the Campaign field of the Campaign Member, and selecting only the Contact ID field for output.
  2. Get the Opportunity records. In the second step, retrieve from the Opportunity object the record IDs of the opportunity records that were obtained from that same campaign, by filtering in a similar manner.
  3. Get the Contact Roles. In the third step, use the Opportunity Contact Role object to retrieve the Contact ID values of the records with Role of "Decision Maker," whose Opportunity IDs are linked to the Opportunity ID values of step 2 above.
  4. Find the target contacts. Finally, retrieve from the Contact object those contact records whose record IDs match the Contact ID values of step 1 as well as step 3. This will require two linkages.
When you create a step in the multi-step report builder, it automatically detects all the available linkages to previous steps, and lets you add as many linkages as you need for your report.

Negations in linkages

The multi-step report builder lets you choose either positive (i.e., "matches") or negative (i.e., "does not match") polarity when setting up a linkage. To illustrate, consider the above example again, but this time, suppose you wanted the Decision Maker contacts who were not a part of the specified campaigns. In such a case, you could proceed essentially as outlined above, except with a change in the last step. There, you would select those contacts that match the Contact ID values of step 3, but do not match the Contact ID values of step 1.

"Or" conditions in multiple linkages

When using multiple linkages in a particular step, you could also stipulate that the result of the step matches any one of the linkages, instead of all of them. This would be useful, for example, if you wanted (in the above example) to construct the list of contacts who are either Decision Makers or were part of the campaign in question.

Multiple output blocks

If you create a multi-step report in which every step is linked to exactly one earlier step, then you have essentially set up a linear chain of dependencies. So the results of all the steps can be laid out horizontally in one table (or "block"). If, however, a particular step is linked to more than one earlier step, or to none at all, then the records of that step cannot be naturally aligned with those of earlier steps. In such a situation, the report is displayed in multiple tables, one for each linear dependency chain in the report.

Controlling the record range and sort order

With each step of the report, you can specify - via the Range and Sort tab - the number of records to retrieve, and their ranked order relative to a previous step. This tab lets you make one three choices:
  1. All the matching records upto a specified maximum number, say 10,000. This is the default choice, and applies to the most common situation where we simply want all the records that match the filter conditions and linkages of the step. If you choose this option, the step's records will be sorted in ascending order according to the order of the fields in the Retrieved fields tab. Therefore, if you want the step's output to appear sorted on a particular field, simply make that field the first one in the list of retrieved fields by dragging and dropping it in the Retrieved fields tab.

    Note that there can be a little confusion here: The maximum number you specify applies to the total number of records retrieved by the step, and not to the number of records linked to any particular record in a previous step. To illustrate, consider the short example we showed above, where we retrieved Contact Role records associated with Opportunity records. In that example, it might seem reasonable to say that since any given Opportunity does not carry more than (say) ten contact roles, we can limit the maximum number retrieved by step 2 to at most 10. But such is not the case, because step 2 executes only once, and independently of step 1, and therefore the limit we specify applies to the entire record range that it retrieves - not just to one particular parent-child linkage. In other words, the process is that Step 1 is executed first, and then Step 2 is executed with the specified record limit, and finally Apsona correlates the two record sets according to the specified linkage. So if we chose a limit of 10 for step 2, then it would retrieve only 10 Opportunity Contact Role records and try to correlate them with all the retrieved Contact records, so that quite a number of the Contact records would show no associated roles.

  2. The highest or lowest record when sorted according to a specified field. This choice applies in cases where, for each parent record, you want to retrieve just one child record that is "maximal" in some way. For example, for each Opportunity retrieved by a previous step, you might want to retrieve the most recent associated Activity record, i.e., the Activity record with the largest Start Date value. Along these lines, if you set a filter so that it retrieves only completed activities, then this choice would retrieve the most recent completed activity.
  3. The first n records when sorted according to a specified field, ascending or descending. This choice is similar to the previous one, except that it retrieves a specified number of "maximal" records instead of just one. For example, you might use this choice when you want to retrieve the five highest-value open Opportunities with each of your Partner Accounts.

Caveat

In the second and third cases above, where we want the "first n" records according to a particular sort order, the linkage must be one in which both fields involved in the linkage are directly within their respective objects, i.e., linkages across fields in looked-up objects are not supported. Another way to state this constraint is to say that the two fields must be in objects which are one step away from each other via a lookup. If you try to create such a linkage, the report will fail validation. For example, if you have a step A that retrieves data from the Account object, you cannot create a later step B that retrieves the "first n" records from the Opportunity Contact Role object, since there is no direct lookup to the Account object. This constraint exists because of a technical limitation with Salesforce's SOQL queries. Note, though, that you can create a step B that retrieves all matching records from the Opportunity Contact, since you can link the Contact's Account ID field of the Opportunity Contact Role object of Step B to the Account ID field of step A.

Auto-filled output

When a multi-step report involves two or more steps, and each record from an earlier step produces multiple records from later steps, the resulting output can be optionally auto-filled. When creating or editing the multi-step report, you can specify which of these two layouts you want, as in the screen shot below. Note that if you use the
non-auto-filled mode, you should not sort the report by clicking the column headers. Doing so will cause the rendering to be completely distorted. If you wish to retain the ability to sort, you must use the auto-filled option.

Some caveats:

Rendering rows as column blocks

When displaying the list of records from the "many" part of a one-to-many relationship, it is sometimes useful to render the child records in column blocks rather than as rows. Consider, for example, a two-step report in which the first step displays your recent campaigns, and the second step shows the top three opportunities from those campaigns. Such a display might look like the one below. Notice the default rendering, in which up to three rows may be used for a single campaign record, displaying the top three opportunities from that campaign. Sometimes, however, you might want to show the opportunities resulting from the campaign as column blocks rather than rows. In such a display, you would get just one row for each campaign, and the three opportunities will be shown in three blocks of columns - one block for each opportunity. Below is such a rendering of the same data. The latter rendering is useful in several situations, e.g., when you want to generate a document from the multi-step report in which you want to include abbreviated information about each campaign.

To achieve this effect, you can set the check box labeled "Show this report's records in column blocks" when editing the report (see screen shot below). Alongside, you specify the number of column blocks to be used for this rendering (three, in this example). Some notes about column-block rendering:

Combining the results of two steps

There are many practical situations where we will need the combined result of two steps which produce unrelated data. In database terminology, this is referred to as the union of two queries. Here are a few examples. To construct a step that combines two previous steps, proceed as follows.
  1. In the Step Info tab, select the "Combining two earlier steps" radio button. This will cause the second tab to show "Combining rules", and the remaining tabs will be inaccessible, as in the screen shot below.
  2. Click the "Combining rules" tab and select the two steps whose results you wish to combine. You can then set up the output columns of the combining step by "lining up" each retrieved field of the first selected step with a corresponding retrieved field of the second selected step, as in the example below. Note that you can only select matching types of fields to line up. For instance, in the above screen shot, we have set up the Opportunity Id field of the "Primaries" step to line up with the "Opportunity" field of the "Roles" step, ensuring that the two selected fields are of the same type, both being references to an Opportunity object. In general, the rule is that two input fields from the two input steps are allowed to be combined only if (a) they are of the same type (i.e., both dates, both numbers, both currencies), or (b) they both refer to the same object type (e.g., Opportunity or Contact).

    Note also that you can check the "De-duplicate" box to automatically de-duplicate the combined results.

A tour of the report builder

When you click the “Multi-step reports” tab you will see the list of reports on the right and the folder names on the left. “My folders” will display a list of all of your folders.  Below your folders, you will see other users’ names and folders. You can run and clone other users’ reports, but you cannot edit their created reports. Under “Reports” you will see all the reports in the selected folder. You can move reports to different folders by clicking the red arrow. All the folder names will show up in the dropdown menu. Select a folder name and the report moves to that folder.

Click the “New” button to get started. The report wizard opens up. Start by giving the report a name and description. You can also select the folder to save the report. Click “Add step” to start building the report.

 

The Step Info tab

In this tab, provide a logical name for the step that suggests what information will be retrieved by the step, and specify how the data is retrieved: from an object (i.e., it is a query step), a set of calculations (i.e., a calculation step, described here), or by combining the outputs of two previous steps (i.e., a union step). If it is a query step, you also select the object from which to retrieve the data for the step. Each step's name will be shown as a header in the report's output. (If you do not add a name for a step, it will be displayed as “New step” in the report output, and this will be non-intuitive.) You can also give the step a description. While a description is optional, it does come in handy when you run the report at a later time.

For a query step, you select the data source object by clicking the “Database object” drop down and then clicking the object you want. You will see all the objects available for your profile. Choose the required object by clicking on the object name in the dropdown. You can also type a few characters in the box, and when you do, the dropdown contents will be restricted to just the objects whose names match the characters you type.


Note, in the screen shot above, that

The Retrieved Fields tab

The “Retrieved Fields” tab lets you choose the fields of the object which will be retrieved by the step. To choose the fields, click the dropdown menu in the “Add” selector. You can choose fields and metrics from the chosen object as well its parents. The metrics section offers values for the number of records (count), sum, lowest, highest and average values for currency fields, and newest and oldest dates for date fields.  

Bear in mind that, in each step, you will need to include the record id or lookup fields needed for linkages with other steps. The record id field is indicated by a key icon, and lookup fields are indicated by a link icon. Hovering your mouse over the link icon shows a tooltip displaying the object to which the lookup refers.

Click on fields you would like to see in your report output. Once you click and choose a field you can rename it if required. You can rearrange fields by dragging and dropping them. You also can delete a field by clicking on the “delete” icon.  The field search box allows you to search for fields in the object.

The Filters tab

The filter terms tab is where you specify the filtering criteria to apply when retrieving data for the step. You can choose to use saved existing filters from the chosen object, or add new filter terms for the step. You can also choose filters click the Record id field of the related object and choose in filter as the operator.  Please click the link for more details on filters.

The Range and sort tab

You have three options, as follows: This tab allows you to limit the number of records you want to retrieve especially if  you have a very large database and this helps in not hitting your API limits.

The Linkages tab

The Linkages tab lets you set up and manage the linkage relationships you need between the current step and one or more previous steps. It displays all the available linkages as well as the ones that you have selected. You can delete an existing linkage by clicking the red X button next to it. You can add new linkages by clicking the linkage chooser dropdown next to "Add linkage."

The linkage chooser shows one row for each possible candidate linkage, with four columns: In the screen shot above, the step has used two linkages to the steps named "2010 donors" and "2011 donors", and chooser shows only two candidate linkages for the current step.

Note that the chooser displays only those linkages that are actually available; in other words, only those cases where there is a field in a previous step whose type matches that of a field in the current step.

Running a report

Tip: It is advisable to click “Save” after every step is completed.

When you run a report, you will see the results in a tabular view. If the steps in the report link to previous steps, the blocks display in joined blocks. If the steps are not linked (negation linkages), steps will show up in different blocks. You can sort on each column and also change its width. You can choose to hide fields of the report by clicking on Set columns. All the fields in the report will show up. Uncheck the fields you would like to hide and click Apply. (Lookup fields show up with the Salesforce record id and such fields can be hidden once the report is run) . You can export data of a report into a CSV or XLS format by clicking the down arrow next to Export.

Temporarily changing filters

If you are the owner of the report, or have edit access to reports in general, you will have available a "Change filters" button at the top of the report table. Clicking this button will show a popup window via which you can temporarily change the filters of each step of the multi-step report, and then run it. When you change the filters in this way, you are overriding (i.e., replacing) the filter terms that are wired into the report. But Apsona will not save the changes you make to the filters, it will only use them to run the report when you click the Change button.

If you are not the owner of the report and you do not have edit rights, you will see a "Show filter builder" button via which you can add filters on top of the ones already wired into the report. Unlike with the "Change filters" button, you will not be able to override any filter terms in the report, you can only add new terms to it to further restrict it.

Managing reports and folders

You can organize your reports into folders, grouped according to your needs. When you access your list of multi-step reports, you will see a list of folders on the left, and when you click one of the folders, the reports it contains will be shown in the report list.

To create a new folder, click the "New" button at the top of the folder list. To rename or delete a folder, hover over the folder name, click the downward-arrow that appears, and then select the action you wish (rename or delete).

The folder list is organized with your folders at the top, followed by folders belonging to other users, in alphabetical order by user name. Under each user's name will appear the list of the user's folders which are visible to you. You can click any of those folders, examine the reports in it, and run it or clone it.

Finding a report

You can also search for a report by name, via the "Find" box at the top of the report list. When you do so, the resulting list shows all the reports visible to you, each listed with its owner and folder. The matching is by substring, i.e., the result of such a search will include every report visible to you with a name or description containing the string you type.

Folder visibility

You can set the visibility of each of your multi-step report folders to one of four choices: Note that even if a folder of yours is visible to another user, the user will not be able to edit or delete the report since she does not own the report. The user can only run a report in the folder or clone the report. But an administrator can view and edit all folders and reports. Thus the above visibility levels are enforced only among users who are not administrators.

When you create a folder, its access level is automatically set to "Public". If you wish to change its access level, hover over the folder name and click its menu. Then hover over the "Visible to" menu item, and you will see a sub-menu that displays the folder's current access level. Click the access level you wish to use.