A guest post by Jennifer Shier.

While we sit on the beach watching many customers ride the salesforce.com Wave, let me hand you an umbrella and tell you how to achieve some of the same results using native Salesforce functionality.

One roll-up field on any object can provide instant analytics only thought possible in the world of BI tools.

By occasionally exporting roll-up summary data and then importing the data back into Salesforce, you can start to track trends, patterns, and key statistics while preserving your roll-up summary limit. You can load up any standard or custom object full of data metrics and leave your 10 roll-up fields open for current data or as triggers for workflows.

Whether you expose or hide the fields on a page layout, any admin can have access to as many data analysis fields as they like for use in reports, formula fields, and even quick viewing by users on Salesforce1!

For those of you who have already created a roll-up field in Salesforce, you know that you can sum or count child records on any object. A common use case is summing up closed opportunity amounts per a certain time frame such as the current month, quarter, year etc. Since roll-up fields require specific dates, one of the limitations is that you cannot see the field to constantly calculate THIS MONTH, THIS QUARTER, or THIS YEAR on its own.

Many admins have to turn over the dates on the first of the next month, quarter or year. If you are doing that already, doesn’t it make sense to store the data before you change the field?

Let me show you my personal use cases.

Calculate Sales Totals by Product

This org has accounts that are hospitals, and the invoice is a child custom object to the account. Each invoice includes a date, a product and total amounts. Rolling up sales totals for each product and time frame enables the sales users and managers to evaluate the buying habits of this account quickly. As you can see, I could easily max out my 10 roll-up field limit. So, exporting the roll-up field data and importing it back in under the account continually stores the data for the user to see this:

 

As you can see from the above image, we are storing time period and product line data as simple currency fields so that we are using the roll-up fields for current time periods instead of every possible need.

Trending by Quarter

Each end of quarter, I was asked to build reports showing how the account performed on Plasma Sales during the year based on quarterly sales. So, in the left section on the account page layout (below), I created a static field for each quarter’s sales and filled it with the data from my roll-up fields for each quarter. Then, in the right section in the same section, I created formula fields to calculate the difference. With the data already nested under the account, the users no longer needed an excel report of the account’s quarterly performance!

Enhanced Reporting

I wanted to make reporting simple on the account. For example, this report shows the accounts with the highest plasma sales between Q1 and Q2 using two static fields and a formula field that subtracts them. Admins or users can see this data on an account report without having to include any custom objects or complex summary/matrix report formats.

Understanding the Benefits

If these use cases are not convincing you yet, let’s talk about all the benefits of moving roll-up data to static data analysis fields in Salesforce.

For users:

• Data points are universal! All users from inside sales to executives will benefit from proactive information.
• Quick access to data points without leaving the record while working on their desktop or Salesforce1.
• Easy reference for a field rep to find talking points while in front of the customer on Salesforce1.
• Eliminates the need for users to have to create or request a report showing variations of customer data on the record.
• Useful and actionable data empowers users! This is especially effective in sales where the data can contribute to upselling or preventing a missed sales.

For Admins:

• Each object has a limit of 10 roll-up fields, so moving past data out of a roll-up to a static field will free your roll-up to use for current roll-up needs.
• Less requests for custom reports tailored to each user’s performance to view data points.
• Data fields are now on a single object rather than multiple so data reports can be created from any account report (no relationships or joins required).
• Formula fields can be created on data fields removing the need for excel reports to do the calculation.
• If you are exporting data to Salesforce for yourself or users, why not already have the calculations come over with the report?
• Data fields and formulas on the fields can be displayed to align with current sales initiatives (for example this quarter vs last quarter).
• Data fields can be hidden from users and stored for later use such as year-to-year sales analysis.
• Formulas on data analysis fields can extend your reporting capabilities to create delta and percentages on date combinations that you cannot run in a matrix report (such as Q1 2014 against Q1 2013)

Try it Out

1. Create a currency roll-up field on the sales metrics you want (note that I include the appropriate date frame to match the field title for Q4 2013).
2. Create a currency static field to hold the sales data – displaying the new field on the account layout is optional.
3. If you don’t have one already, you will also want to create a formula field on the standard/custom object you are using to pull the Salesforce ID. In this case, I created this field called Account Salesforce ID using a formula field that pulls the value called IDs.
4. Create a report on the account that includes the Salesforce ID and the roll-up field
5. Export your data from the report and save it as a CSV
6. Import the data back into your static field by using the Salesforce Data Import Wizard, Add New Records and match by Salesforce ID.
7. Map the Salesforce ID and your roll-up field to the static field
8. Once your import is completed, you can add it to a page layout and re-use your existing roll-up for a different calculation!

Now, you are ready to build formula fields and/or reports to complete your mini-data analysis!

How have you used roll-up summary fields to enhance your records and reporting? Share your experience below with a comment!