Build Reports and Dashboards

Learning Objectives

After completing this unit, you’ll be able to:

• Build reports around the new data on your competitors.
  
• Explain how to create a competitive insights dashboard.
  

Introduction

You’ve created two new fields with validation rules. Now you can get to the heart of this whole endeavor: setting up analytics that give you insight into which competitors your company runs up against most often and why you lose to rivals.  

Update Opportunities with Competitor and Lost Reason Values

The custom fields you created in Unit 2 do not have any data in them yet. You need data in those new fields to create reports. Here’s how to update a few opportunities in your sandbox or Trailhead Playground so you can build the reports.

1. Click and select your Sales application.
   
2. Click the Opportunities menu item.
   
3. Select the All Opportunities list view.
   
4. Click and then click Clone.
   
5. Name the new list Competitor Updates.
   
6. Verify that Only I can see this list view is selected, and click Save.
   
7. Click and then click Select Fields to Display.
   
8. Add Competitor and Lost Reason to Visible Fields.
   
9. Click Save.
   
10. Click Close Date to sort the list view by this field, in descending order.
    
11. The List View is an editable table. Pick a row, hover over the Stage field, and click the pencil icon. 
    
12. Update the stage to Closed Lost.
    
13. Using the same in-line editing functionality, add any Competitor and Lost Reason values to several opportunities in the table.
    
14. Once you’ve updated several opportunities, click Save.
    

Report 1: Won and Lost Opportunities by Competitor

Now that you’ve got some data, you can create a report. 

1. Click and select your Sales application.
   
2. Click the Reports menu item, and click New Report.
   
3. Choose Opportunities on the left sidebar, and then select the Opportunities report type on the right.
   
4. Click Continue.
   

5. In the outline pane, add Competitor and Stage group rows. 
   

6. Click Filters, and add a new filter for the Stage field.
   
7. Select equals for the Operator.
   
8. Select the values Closed Won and Closed Lost.
   
9. Click Apply.
   

10. Click Add Chart.
    
11. Click in the upper right corner of the chart to edit the chart properties.
    
12. Choose Stacked Bar as the chart type.
    
13. Scroll down a bit and update the X-Axis to Record Count.
    
14. Click again to close the chart properties pane.
    
15. Click Preview. And if the preview looks good, click Save & Run.
    
16. Report Name: Opportunities by Competitor Click Save. 
     
    

Report 2: Lost Reasons by Competitor

In this second report, group Lost opportunities by their Lost Reason value. Here’s how.  

1. Follow steps 2–4 above to create a new report with the type Opportunities.
   
2. Add groups for Competitor and Lost Reason.
   
3. Add a filter for Stage equals Closed Lost.
   
4. Click Apply.
   
5. Add a horizontal bar chart similar to the first report, setting the chart type to Stacked Bar and the X-Axis to Record Count. Your chart should look similar to this.
   
6. Click Preview. And if the preview looks good, click Save & Run.
   
7. Report Name: Lost Reasons by Competitor Click Save. 
   

Report 3: Win/Loss Ratio by Competitor

For the third report, you calculate the opportunity win/loss ratio by competitor with a custom report formula column.

1. Begin by taking the same steps to create a new report with the Opportunities report type.
   
2. Add a group for Competitor.
   
3. Click Filters, then Opportunity Status, and select Closed from the picklist. Click Apply. 
   
4. Click Outline.
   
5. In the Columns section, click | Add Summary Formula Column.
   
6. Enter Win Rate for the Column Name, select Percent for Formula Output Type and 0 for Decimal Places.
   
7. Enter WON:SUM/CLOSED:SUM for the Formula.
   
8. Click Apply.
   
9. Repeat steps 5 - 8 to create a second formula field, but this time name the field Loss Rate and use the formula (CLOSED:SUM - WON:SUM)/CLOSED:SUM
10. Optionally, click Detail Rows at the bottom of the preview pane to hide the individual rows and only show the report summary data.
    
11. Click Run to see the full report results, and then click Save to save the report with the name Win / Loss by Competitor. Here’s a sample of what this looks like. 
    

Build a Competitive Analysis Dashboard

Let’s now add our three new reports to a brand-new dashboard.  

1. From the Dashboards menu item, click New Dashboard.
   
2. Name the dashboard Competitive Analysis and place the dashboard in a folder of your choice.
   
3. Click + Widget, then Chart or Table.
   
4. Select the Opportunities by Competitor report that you created earlier. Click Select.
   
5. For Display As, select Horizontal Stacked Bar Chart, for Y-Axis choose Competitor, and for X-Axis choose Record Count.
   
6. Click Add.
   
7. Click + Widget again to add a second component, and select your Lost Reasons by Competitor report. Click Select.
   
8. For Display As, select Horizontal Stacked Bar Chart, for Y-Axis choose Competitor, and for X-Axis choose Record Count. Click Add.
   
9. Click + Widget again, this time using Chart or Table to select the Win / Loss by Competitor report. For Display As, select Lightning Table. Configure the table by adding the Competitor field in the Groups section, and adding Win Rate in the Measure Columns section. In the Measure Columns section, click to remove the Record Count column from the table. Click Add.
   
10. Move or resize the dashboard components to your liking. Here’s an example. 
    
11. Click Save, and then click Done to see the new dashboard in action.
    

Ask your sales team if they would like you to add these new components to their dashboards.

Resources

• Build a Report in Lightning Experience (Salesforce Help Article)
• Build a Lightning Experience Dashboard (Salesforce Help Article)