It is quite common for organizations to bring order history into Salesforce to add to other interactions captured in Salesforce to have a complete picture of a customer. Once the information has been brought into Salesforce, it is easy to report on account order history, but what if you would like to report on accounts that don’t have order history for a particular product in the last 12 months. This is more of a challenge but could be very useful information for Sales to be able to identify accounts that haven’t made a purchase of a certain product in the last year to understand the reasons and perhaps target them for future sales activity.

Aron Schor, a Salesforce user from Acme United had this challenge and shared the the solution detailed below, using the example of reporting on stores that haven’t sold a particular running shoe. The objects included in this example are Account, Sales Order and Sales Order Line. Account is the Master Object of “Sales Order”. “Sales Order” is the Master Object of “Sales Order Line”. It uses 2 rollup summary fields, one to rollup the number of the particular product purchased for each sales order. The second rollup summary field rolls up the number of the particular product purchased for an account across all sales orders. A report is then created that will report on the accounts the purchased products. In order to report on a particular period, a filter is added to the rollup summar field to limit the data, for example for the year to be reported on. Obviously this filter would need to be adjusted or new rollup summary fields added for different periods.

ACCOUNT -> SALES ORDER -> SALES ORDER LINE

The product used in this example is the running shoe: Saucony Guide 7

Here is the sample data used:
The Fairfield Running Store purchased 75 pairs on 11/23/2014 and 100 pairs on 1/2/2015.
The Greenwich Running Store purchased 200 pairs on 1/2/2015.
The South Norwalk Running Store purchased 125 pairs on 2/11/2014.
The Stamford Running Store purchased 45 pairs on 5/14/2014.
The Westport Running Store has never purchased the product.

1. Create the first Roll-Up Summary Field

Go to Create -> Objects -> Sales Order -> Custom Fields & Relationships and click New.

Step 1-Select Roll-Up Summary under Data Type and then click Next.
Step 2-Let’s call the Field Label – “Sales Order Purchase QTY-Saucony Guide 7.”
Step 3. Define the summary calculation
Select “Sales Order Lines” for “Summarized Object.”
Select “Sum” for the Select Roll-Up Type.
Select “PurchaseQuantity” For “Field To Aggregate”
Select “Only records meeting certain criteria should be included in the calculation by Filter Criteria.”
Field – Operator – Value would be ProductName  equals  Saucony Guide 7.

Steps 4 and 5, leave the default settings. Hit Save for step 5.

2. Create the first Report

Go to Create -> Report Types

Step 1: Create a Report type with
Report Type Label – “Accounts with Sales Orders with Sales Order Lines”
Record Type Name – “Accounts_with_Sales_Orders_with_Sales_Order_Lines”
Store In Category – Accounts & Contacts

Step 2:
A Accounts (Primary object)
B Sales Orders (A to B relationship)
C Sales Order Lines (B to C relationship)

Click the Report name under Label and then Edit Layout.
Select View and then Sales Order Fields.
Drag “Sales Order Purchase QTY-Saucony Guide 7” to the Sales Order section.
Double click and make sure “Checked by Default” is selected.

Hit Save.

3. View The First Report

Go to Reports and click New.
Create a new Accounts with Sales Orders with Sales Order Lines report.
Add “Sales Order Purchase QTY-Saucony Guide 7”

(We can verify the sales information provided at the top.)

4. Create the Second Roll-Up Summary Field

Go to Customize -> Accounts -> Fields and click New by Account Custom Fields & Relationships.
Step 1-Select Roll-Up Summary under Data Type and then click Next.
Step 2-I called the Field Label “QTY Saucony Guide 7 purchased (All time)”

Step 3: Define the summary calculation
Select “Sales Orders” for “Summarized Object.”
Select Sum for “Select Roll-Up Type.”
Field to Aggregate should be “Sales Order Purchase QTY-Saucony Guide 7.”
“Filter Criteria” should be “All records should be included in the calculation.”

For Steps 4 and 5, leave the settings as default. Hit Save for step 5.

5. Create the Second Report

Go to Create – Report Types
Add “QTY Saucony Guide 7 purchased (All time)” to your custom Report in the Accounts section. (It will save this info for other Account Report types.)

6. View the Second Report

Go to Reports
Create a new Account report.
Add field “QTY Saucony Guide 7 purchased (All time)”
Notice this is information is showing 2014 and 2015 data.

7. Modify the Period of Time

To just get 2014 data go to Customize -> Accounts -> Account Custom Fields & Relationships and edit “QTY Saucony Guide 7 purchased (All time)”
Select “only records meeting certain criteria should be included in the calculation.”
Order Date – equal or greater – 1/1/2014 AND
Order Date – less or equal – 12/31/2104

The Label was changed to be “QTY Saucony Guide 7 purchased” (2014)

Now go back to the Report, the results are:
The Fairfield Running Store purchased 75 pairs.
The Greenwich Running Store (remember, they only purchased in 2015) and the Westport Running Store purchased 0 pairs.
The South Norwalk Runnig Store purchased 125 pairs.
The Stamford Running Store purchased 45 pairs.
All of this matches.

Set “QTY Saucony Guide 7 purchased (2014)” to be 0 to only display Accounts who haven’t purchased.