The Power of Apsona Single-Step Reporting

Or, no more banging your head against the wall over standard Salesforce reports

Reports reports reports. One of the critical functions of your Salesforce database is to generate reports that will give you accurate, useful information about your constituents, products, and services.

Standard Salesforce reporting can get you only so far. Sometimes, you need a more complex analysis of your data. For example, take this real life scenario recently posed by a non profit organization:

I am trying to report on how many contacts 65 years of age and older live in households that have received services in the last year.

Example: Jim is 70 years old. His wife Franny is 72. They live with their daughter Sara, who is 50 years old and who received food services last year.

In Salesforce it looks like this:

  • Household Account with three related Contacts: Sara, Jim, and Franny
  • Case record: connected to Sara’s Contact record, and to the Household Account record.

How do I get the report to count Jim and Franny when they are not in the Case loop, so to speak?

Report Scenario.png

First Attempt with Standard Salesforce Reporting

Hmmm, could we do this with a standard Salesforce report? What if we create a report on Account, with cross filters to find Accounts with Cases, and Accounts with Contacts over 65?

Standard Acct report with x filters.png

Standard Salesforce report on Account with cross filters

When we run this report, we want to see a result of 2, since there are 2 contacts over 65 in a household with services.

Sadly, that’s not what we get. Our standard Salesforce report is counting the number of Accounts that meet our criteria, not the number of Contacts:

Standard SF report - results - not quite what we want.png

Standard Salesforce report results — gives us count of Accounts, not count of Contacts.

Darn it. What can we do?

Apsona Single-Step Reporting to the Rescue!

Fortunately, there’s Apsona. Apsona’s incredibly powerful reporting tool will give us exactly the results we want, and then some. Follow along to see how!

  • From the Apsona tab, click on Accounts, and choose Reports.

Account Reports.png

  • Click + New to create a new report

New Report.png

Part 1: Choose Your Columns

Right off the bat with Apsona reporting, we have the ability to choose fields not just from our base Account object, but from any object that has any kind of relationship to Account — without having to create a special report type. All of these related objects are provided right there for you. Think about it — this includes any objects that have a lookup or Master/Detail relationship to Account, as well as any objects that the Account has a lookup to. Powerful!

Apsona report available fields.png

Fantastic, but how do we get the count of Contacts we’re looking for?

First, we’ll need to choose the correct Contact object:

  • In the Available Objects list, choose Contact via Account. The help text shows that this is any Contact record that’s related to the Account via its Account lookup field. In other words, any Contact in the Household Account — exactly what we want.

Available Object - Contact via Account.png

Next, we choose the fields we want. Here’s where the handy “Metrics” feature of Apsona comes into play:

  • Expand the “Metrics” column at the bottom of the “Contact via Account fields” list, and choose # Records

Choose Record Count field.png

That is the magic that will make this report work: displaying the # Records field, and choosing the Contact via Account related object. Of course, we still need to add report filters to make sure we’re only getting Contacts over 65, and only those in Households that have Cases. But how great is it that we can so easily pull metrics such as record count from related objects?

As long as we’re here, why not display the Contacts’ names and ages?

  • Click in the Add column picklist to bring up the Add Column dialog.
  • In Available Objects, choose Contact via Account
  • In Contact via Account Fields, choose First Name
  • Repeat the above steps for Last Name, and Age. (NOTE: , “Age” is a custom formula field that we created on Contact.)

Now our screen looks like this:

Report fields.png

As an aside, another lovely feature of Apsona reporting is the ability to change report column names. Simply hover over the column whose name you wish to change, click on the arrow, and type in the new column name of your choice.

Change Column Label.png

Part 2: Add Filters

Now that we’ve chosen the fields to display, it’s time to filter the report. Again, we only want to see Contacts over 65 that belong to Accounts where someone has received services. Let’s start with the age filter on Contact:

  • On the Filter Terms tab, click the Add Term link.

Click Add Term link.png

  • In the Available Objects list, choose Contact via Account, and then choose Age in the Contact via Account Fields list. (Again, “Age” is a custom formula field that you can create on Contact.)

First Filter Term.png

  • Complete the filter term by adding the appropriate criteria:

Filter Term for Contacts.png

Next we want to make sure we only see Contacts in Household Accounts where at least one Contact has received services, In Salesforce terms, where there is at least one Case associated with the Household Account.

This filter is a little different from the Contact filter, since we’re not filtering on a particular field on the Case, but on whether or not there are any Case records. Apsona easily handles this scenario with Metrics:

  • Click the Add Term link again, and this time, choose Case in the Available Objects list.
  • In the Case Fields list, at the bottom, click the Metrics header

Case Metrics.png

The Metrics options open up.
Metrics options.png

  • Choose # Records, and finish the filter criteria. Since we want both conditions to be true, we’ll leave the Filter logic as is.

Complete filter criteria.png

Part 3: Run the Report!

We’ve chosen the fields to display, and set our filter terms. Let’s test it out!

  • Choose Save and Run to see your report results

Apsona report results.png

Voila! Our Apsona report gives us the results we were aiming for — number of Contacts in households that received services. And, as you can see, you can easily add other fields related to Contacts to verify your data and make your report even more useful.

So stop banging your head against the wall over standard Salesforce reports — and start making use of the power of Apsona reports today!

Leave a Reply