3 New Awesome Salesforce Reporting Features


In the Spring’20 release Salesforce finally heard our prayers and provided the ability to:

  1. Declare Row Level Formulas.

  2. Filter reports using Field to Field comparison (Generally Available).

  3. Count unique values in report results (Generally Available).

This blog’s goal is to review each one of this new, AWESOME!, features and take a look at some cool use cases where they can be used.

1) Row Level Formulas and how to group your Customers by Tenure

Row Level Formulas allow you to define a formula to evaluate each row of the report from inside the report builder in Lightning. This means less formula fields created at the object level with reporting purposes, less metadata to track and maintain and also, less migration of your reports to Excel or GSheets to accomplish this.

Today we are going to take a closer look at a particular use case for this new row-level formulas, which is how to use them to group your Customers by Tenure and how this looks in a chart that can be used in a Customer’s Dashboard.

So, let’s start by assuming our client’s Org has a basic Account = Customer structure and they have enough historical data to want or need to track those Customer’s Tenure.

In our scenario the Account object has defined a Start Date, a custom date field which is filled out when the first Sale or Service is provided to that customer.

Our client reaches out to us and asks us to built a report, which will show in a Dashboard, grouped by Tenure with the following ranges:

  1. Less than 1

  2. >= 1 and less than 2

  3. >= 2 and less than 3

  4. >= 3 and less than 4

  5. >= 5 and less than 10

  6. >= 10

This is how the report’s chart will look like:

This is how we accomplish it:

  1. Go to the Reports tab and click New Report.

  2. Select Customers as Report Type (this step will depend on the object you want to report on).

  3. Click Continue.

  4. Select All Customers and All Time as filters.

  5. Add the columns Customer Name and Start Date to the report and remove all other columns.

  6. In the Column section click the Caret Down arrow and select the option Add Row-Level Formula.

  7. In the prompt formula editor write down the following formula:

    The Row-Level Formulas have a 400 characters max limit, so you have more than enough to develop your formula writing skills.

    This formula repeats it’s calculation for each range requested by the client:

    It rounds the result of the difference between today and the customer Start Date, taking into account the difference in days of the current year from Jan 1st until Today and from Jan 1st of the Start Date year, until the actual Start Date, and based on the result assigns the appropriate range value text.

  8. Add a name to the Row-Level Formula: Customer Tenure, select the Type = Text and Validate the formula just to be sure you didn’t missed any parenthesis.

  9. Click Save.

  10. Once the Row-Level Formula is saved just drag it from the Columns section into the Group section of the report builder.

  11. Click the Add Chart button and select a Column chart.

  12. Make sure the X-Axis in the chart is the Customer Tenure and the Y-Axis is the Record Count.

  13. Click Save & Run.

That’s it! Now you have a cool new chart that you can add as a component to the Customers Dashboard.

2) Filter Report using Field to Field comparison (Generally Available)

This is one of the most awaited functionalities in Salesforce reporting. How many times did we wish to have the ability to compare two fields to filter a report? How many times we needed to compare two fields of different objects in our report filters?

Well wish no more my friends, the day has come. Presenting to you: Field to Field Comparison.

Let’s say our client requests a new report where they want to see the Customer’s First Service date and the Work Order scheduled for that first service. Now this is super easy to build.

In our scenario the Customer is the Account standard object and the Work Order is a child object of the Customer. In the Customer we are storing the first service date in a Date field originally named: First Service, and the Work Order has its own Date field named Schedule Date.

In order to build our report we just have to follow this steps:

  1. Go to the Reports tab and click New Report.

  2. Select Customers With Work Orders as Report Type (this step will depend on the object you want to report on).

  3. In the Columns section of the report builder select: Customer Name and Work Order Name, as the columns to display.

  4. Move to the Filters section by clicking the tab at the top of the left panel.

  5. In the Filters select: All Customers and Created Date = All Time.

  6. Search the field First Service in the filter’s search and select it.

  7. Select Field as Type in the filter box, as shown in the image:

  8. In the Value section of the filter box search for the Schedule Date field of the Work Order object.

  9. Click Apply.

You are Done! Now your report will show each Customer with it’s first Work Order.

3) How many unique values do I have? (Generally Available)

So, if you are a Salesforce Admin or you spend a chunk of your day building cool reports for your clients you are probably familiarized with the amazing “The Power of One” 👐, which has saved us more than once. In the link you will find a step by step tutorial on how to implement it if you are still using Classic 😒or you don’t want to use this new functionality because is “Generally Available”.

In essence, the Power of One proposes to create a number type formula field with only the number 1 in the Object you want to report on and need to know how many unique values you have. Then you add the Power of One formula field into your report and you summarize that column and then, Magic! The summary result will show the sum of 1 but discarding the repeated instances of the records.

In this Spring’20 Salesforce is giving us this functionality out of the box in reports.

This are the steps you should take:

Let’s start with our use case. In this instance, our client ask us to create a report of All the Customers with All the Work Orders, but he wants two metrics to be shown in the report:

#1 Total number of Work Orders.
#2 Total number of Customers

What you want to do is this:

  1. Go to the Reports tab click New Report.

  2. Select Customers With Work Orders as Report Type (this step will depend on the object you want to report on).

  3. In the Columns section of the report builder select Customer Name and Work Order Name, as the columns to display.

  4. Select All Customers and Created Date = All Time as filters.

  5. In the Customer Name click the Caret Down button and select the option Show Unique Count.

  6. Click Save & Run

Your report is ready! Now, if you look at the metrics at the top of the report, you will see the Total Number of Records  count and the Unique Customer Name count.

In conclusion, Christmas has come really really early for us reporters this year! These three new functionalities will make our reporting easier and more fun.

Leave a Reply

Your email address will not be published. Required fields are marked *