Apsona Multi-Step Reporting: Learn the Basics

September 19th, 2017

This is the first in a series of posts about Multi-Step Reporting

Introduction

The sky’s the limit with Apsona’s multi-step reporting (MSR) tool for what kind of reports you can generate. Need a report that spans multiple objects, including sibling objects as well as parent/child? No problem, MSR can do it. What about applying a different filter to each object? Yes, got that covered. Can I output calculated fields? Check, and check.

Are you excited yet? You should be!

However–if you’ve never worked with MSR before, it can be intimidating to start. In the MSR interface you’ll see “steps”, filters, “linkages”, and more. How do you know which objects to pick, how to configure steps, and what to do with linkages? Description: confusion 2.jpg

Don’t worry, we’re going to break it all down. In this first in a series of posts about MSR, you’ll learn:

  • a strategy for designing your report
  • what steps are, and how to configure them
  • how to link your steps, and what that means
  • how to filter results to get only the records you want

Let’s work through each of these points using a typical, fairly complex reporting scenario. Imagine your boss has asked for this:

I want to see the top three opportunities for all of our accounts, along with the name and email of the contact in the role of business user for each of those opportunities.

Design Your Report

A little upfront planning will save you time (and frustration) in the long run. (As an aside, this is true regardless of the reporting tool you’re using.)

Consider these questions:

  • What are you reporting on?
  • What do you want the report output to be?
  • Which records and how many do you want?

Let’s look at our report requirements again:

 

I want to see the top three opportunities for all of our accounts, along with the name and email of the contact in the role of business user for each of those opportunities.

  1. What are you reporting on?

    This question gets at the objects you’ll need for your report.(Don’t worry about the fields yet–that comes in the next step.) 

    In our example, it would be: Opportunities, Accounts, Contacts.

    At this point, it can be helpful to do a quick sketch of these objects so you can see how they’re related:

    Description: IMG_0009.JPG

    Even though we don’t need any data from the Opportunity Contact Role object, we include it in our sketch because it’s the link between the Opportunity, and the Contact for that Opportunity,

  2. What do you want the report output to be?

    What fields do you want to see from which objects? In what order?

    Another quick sketch comes in handy:

    Description: rpt_output_sketch.JPG

  3. And finally, which records and how many do you want? In other words, are there any restrictions on the records you want returned?

From our report example:

  • Accounts: all of them, no restrictions
  • Opportunities: the top three opportunities
  • Contacts: only business users for the opportunities

Fleshing Out Requirements

You might wonder– what does “top three” mean? And can the opportunities be in any stage, or just the closed/won opportunities? And how do I identify business users?

Great questions! This is when you’d go back to your boss and ask for clarification. This is also a good chance to review your sketch with her and make sure you’re on the same page in terms of the report output.

Let’s say you had that meeting (where your boss was very impressed with your sketches and well-thought out questions.) Now you know that “top three” means the opportunities with the highest value in the amount field, regardless of stage. But speaking of stage, your boss realized she’d also like to see that information in the report output. Oh, and the opportunity amount.

You also found out that “business user” means someone with the opportunity contact role of “business user.”

Here’s our revised sketch;

Description: revised_sketch.JPG

What Are Steps and How Do I Configure Them?

Ok, now that we know where we’re headed, let’s start building!

A “step” is the big cheese of Multi-step reports. Think of a step as a container that holds all the information about one of the objects you want in your report. The step holds information about:

  • which object you want to report on (Step Info tab)
  • which fields you want from that object (Retrieved fields tab)
  • how you want to limit the report results (Filter terms tab)
  • how many records, and what sort order (Range and sort tab)
  • how this step (or object) is related to another step in the report (Linkages tab)

 

Remember–one step relates to one object.

Let’s revisit the sketch we made when designing our report:

Description: schema_sketch_steps.jpg 

As we can see from the sketch, our report spans four objects. So we’ll plan to configure four steps. Let’s get started!

Configure the First Step

In general, you start configuring steps with the top-level object, which in our case, is the Account.

  • On the Step Info tab, add a Step Name, and choose Account as the Database Object to get data from.
  • Description: step_info.jpg

  • Next click on the Retrieved fields tab and choose Account Name from the Add Field picklist.

    Description: retrieve_acct_name.jpg

  • Add the Account ID field. (No, we don’t need this field in the report output, but we’ll need it for something else a little later. For now, take it on faith, and hang tight.)

Your Retrieved fields tab looks like this:

Description: retrieved_fields_acct.jpg

  • Click the Filter terms tab.
  • Description: filter_terms_tab_blank.jpg

    This is where we can restrict which records are returned. Since we decided we want all Accounts with no restrictions, we’ll move on to the next tab.

  • Click the Range and sort tab. Description: range_sort_tab.jpg

    This page allows you to choose one of three ways to limit the number of records returned:

    • Limit by number range (1)
    • Limit to one record based on the criteria you pick (2)
    • Limit to the top x number of records (3)

    Again, the report requirements didn’t specify how many Account records or in what order, so we’ll move on.

  • Finally, click the Linkages tab.

    Description: linkages_tab.jpg

    Linkages are about relating steps to each other, Since we only have one step configured so far, there’s nothing to link yet.

  • Click Save.

    Description: click_save.jpg

Congratulations! You’ve configured your first step!

Create the Opportunity Step

Now let’s quickly create a step to get our Opportunity data:

  • Click Add Step.
  • Give the step a name, such as Top 3 Opportunities, and choose Opportunities as the database object.
  • On the Retrieved fields tab:
  • Select the Opportunity Name, Amount, and Stage fields.
  • Also add the Account field, and the Opportunity ID field. (Yes, these are more fields we don’t need in the report output … the mystery will be revealed soon!)
  • Now click the Range and sort tab. Remember, we want only the top three Opportunities (determined by Amount.)
  • Select the bottom option: The first … records when ranked by …
  • Enter 3 for the number, Amount for the field, and descending for sort order:

Description: range_and_sort_top_3_opps.jpg

  • Click the Linkages tab.

This is where we connect our two steps–Accounts and Opportunities.

Remember when we added those fields on the Retrieved fields tab that we didn’t need in the report output (Account ID in the first step, Account in this step)? Well … (drum roll please) … this is why:

On the Retrieved fields tab of each step, include lookup or picklist fields that relate to objects on another step. These are used on the Linkages tab to create connections between steps.

 

In Salesforce, Opportunities and Accounts have a relationship: the Opportunity’s Account lookup field holds the related Account’s Account ID. By adding the Account field in this Opportunity step, and the Account ID field in the previous Account step, we created a way to link the two steps.

(We also included the Opportunity ID field in this step. This will come in handy when we link this step to the next one in the chain.)

  • Choose the first option: Accounts Account ID matches Account.

Description: link_step1_and_2.jpg

 

NOTE: If you forgot (or didn’t realize) you needed to include these linking fields, you would see this message when you clicked on the Linkages tab:

Description: no_available_linkages.jpg

If that happens, no worries — you can always edit any step to add more fields on the Retrieved fields tab.

Create the Last Two Steps

Follow the procedures above to create the last two steps for Opportunity Contact Role, and for Contact.

For the Opportunity Contact Role step:

  • In the Retrieved fields tab, add the Opportunity field (to relate to the Opportunity step), and Contact field (to relate to the Contact step.)
  • On the Filter terms tab, add a filter to select Contact Role records where the Role is Business User. The Filter terms tab will look like this:

Description: filter_terms.jpg

  • Remember to save each step.

Run the Report

Now it’s time to see how all of your hard work has paid off!

  • Click Save and Run to run your report:

Description: report_results1.jpg

Looks great! But we can make it look even better. Those ID fields are there because we needed them to link steps, but we don’t have to include them in the report output.

  • Simply click Set Columns, and uncheck any fields you don’t want to see:

Description: set_columns.jpg

Our final report looks like this:

Description: report_results2.jpg

Points to Remember

  • Design your report before you start building, to understand what objects are needed and how they’re related–and save yourself time in the long run.
  • Configure one step for each object. (There is a shortcut for this … stay tuned for details in further posts!)
  • Include lookup and ID fields in the Retrieved fields tab so you can link steps.

Do you have MSR questions? Have a tip or trick to share? Let us know!