In a dozen (plus) years working with Salesforce, I've never been entirely happy with the built in reporting of my opportunity pipeline. I can export details, or a printed (formatted) view, but I lose the interactivity. I can use a dashboard, which is better now in Lightning in that I can see a lot more fields in a table component. But flexible views require filtered dashboards, and just maintaining the filter values (if they go down to a rep level) can be onerous. Plus applying (or removing) a filter tests my (very limited) patience.
So I've typically exported the report details into Excel, and there created a workbook that includes (1) summary info, (2) scrollable deal lists with hyperlinks back to the opportunity records in Salesforce and (3) filters that let me instantly switch views, say among teams, reps, opportunity types, or fiscal periods. My process around this involved LOTS of VBA and LOTS of formulas.
Lately, I've (finally) gotten turned on to slicers in Excel. Using those, along with the XL-Connector tool that I've mentioned a few times, I now have a much simpler version of that pipeline reporting workbook.
I'll outline the report and the process to create it over three posts:
For this example, I'm managing a sales team at an auto dealer. The report we're building is available here.
To start building this report, I'll start with a new Excel file and log in w XL-Connector.
Still on the XL-Connector tab, click Reports, then select the report and import it to Excel. My report includes the Sales Rep, Model Year, Close Date, New/Used indicator, whether the purchase will be financed and the price (Amount). I also include the forecast category, the Closed and Won fields, Stage, and the close month (this could be calculated in Excel based on the close date, but I've included it in the Salesforce report). The report also MUST include the 18-digit opportunity id (see this discussion of the CASESAFEID). If there are other fields (Next Step?) that I'd want to include in my deal list, I'd include those in the report as well.
On my summary sheet, I allow the user choose to view opportunities in various groupings: All Open, Closed Won, Committed, Closed+Commit, Best Case, Pipeline, Omitted but Open and Closed Lost. For each of those options, I've created a pivot table listing the associated opportunities.
Start with the first category, 'All Open': Click on any cell in the report data table; select Insert Pivot Table. Create the pivot table in the column A of a new worksheet. Put 'Forecast Category', 'Closed' and 'Stage' in the Report Filter section, the Opportunity ID in the Row Labels section. For data, put Amount and any non-numeric field (eg Opportunity Name). Be sure that the values are summarized by Sum and Count, respectively. Right-click on Sum of Amount and select Sort | Sort Largest to Smallest. To get the pivot table to match the description, change the filter for Closed to 0 (indicating False, or not closed).
Right-click on row 1 and choose Insert, moving the pivot table down a row. In cell A1, just above the pivot table enter the label: All Open. In cell B1, I want to capture the selected opportunities' total value: =GETPIVOTDATA("Sum of Amount",A5). And in cell C1, I capture the number of opportunities represented: =GETPIVOTDATA("Count of Opportunity Name",A5). Note that the cell reference is relative (A5) and not absolute ($A$5)! Rename this worksheet Pivots.
Next I replicate that pivot table seven times, once for each of my remaining groupings. First I'copy columns A:C and paste them to cell E1, I1, M1, Q1, U1, Y1 and AC1. Then I go back and adjust the label (row 1) and filters (rows 2:4) for each of the pivot tables to match the groupings listed earlier. For instance, the second grouping is Closed-Won. I change cell E1 to Closed Won, leave my Forecast Category unfiltered, clear the Closed filter, and set the Stage filter to Closed Won (you may use a different stage, or maybe even have different 'won' stages for different record types; adjust the selection to your own org's needs.). (For most of the groupings, the filters are pretty apparent. For Closed+Commit, I first clear all the filters. Then in the Stage filter, I select all but Closed Lost. And then in the Forecast Category filter, I select Closed and Commit.)
That's the first part of the process. Next time, I'll offer some resources to get up to speed on slicers, then build the slicers and the summary sheet for my workbook.
So I've typically exported the report details into Excel, and there created a workbook that includes (1) summary info, (2) scrollable deal lists with hyperlinks back to the opportunity records in Salesforce and (3) filters that let me instantly switch views, say among teams, reps, opportunity types, or fiscal periods. My process around this involved LOTS of VBA and LOTS of formulas.
Lately, I've (finally) gotten turned on to slicers in Excel. Using those, along with the XL-Connector tool that I've mentioned a few times, I now have a much simpler version of that pipeline reporting workbook.
I'll outline the report and the process to create it over three posts:
- This one focusing on the general outline, building the Salesforce report, and creating the various pivot tables I'll use
- The second will focus on creating the slicers and the summary sheet; that's what my end-users will see
- The last will discuss updating the report, then automating that refresh process by building on the VBA covered in this prior post.
For this example, I'm managing a sales team at an auto dealer. The report we're building is available here.
To start building this report, I'll start with a new Excel file and log in w XL-Connector.
Still on the XL-Connector tab, click Reports, then select the report and import it to Excel. My report includes the Sales Rep, Model Year, Close Date, New/Used indicator, whether the purchase will be financed and the price (Amount). I also include the forecast category, the Closed and Won fields, Stage, and the close month (this could be calculated in Excel based on the close date, but I've included it in the Salesforce report). The report also MUST include the 18-digit opportunity id (see this discussion of the CASESAFEID). If there are other fields (Next Step?) that I'd want to include in my deal list, I'd include those in the report as well.
On my summary sheet, I allow the user choose to view opportunities in various groupings: All Open, Closed Won, Committed, Closed+Commit, Best Case, Pipeline, Omitted but Open and Closed Lost. For each of those options, I've created a pivot table listing the associated opportunities.
Start with the first category, 'All Open': Click on any cell in the report data table; select Insert Pivot Table. Create the pivot table in the column A of a new worksheet. Put 'Forecast Category', 'Closed' and 'Stage' in the Report Filter section, the Opportunity ID in the Row Labels section. For data, put Amount and any non-numeric field (eg Opportunity Name). Be sure that the values are summarized by Sum and Count, respectively. Right-click on Sum of Amount and select Sort | Sort Largest to Smallest. To get the pivot table to match the description, change the filter for Closed to 0 (indicating False, or not closed).
Right-click on row 1 and choose Insert, moving the pivot table down a row. In cell A1, just above the pivot table enter the label: All Open. In cell B1, I want to capture the selected opportunities' total value: =GETPIVOTDATA("Sum of Amount",A5). And in cell C1, I capture the number of opportunities represented: =GETPIVOTDATA("Count of Opportunity Name",A5). Note that the cell reference is relative (A5) and not absolute ($A$5)! Rename this worksheet Pivots.
Next I replicate that pivot table seven times, once for each of my remaining groupings. First I'copy columns A:C and paste them to cell E1, I1, M1, Q1, U1, Y1 and AC1. Then I go back and adjust the label (row 1) and filters (rows 2:4) for each of the pivot tables to match the groupings listed earlier. For instance, the second grouping is Closed-Won. I change cell E1 to Closed Won, leave my Forecast Category unfiltered, clear the Closed filter, and set the Stage filter to Closed Won (you may use a different stage, or maybe even have different 'won' stages for different record types; adjust the selection to your own org's needs.). (For most of the groupings, the filters are pretty apparent. For Closed+Commit, I first clear all the filters. Then in the Stage filter, I select all but Closed Lost. And then in the Forecast Category filter, I select Closed and Commit.)
That's the first part of the process. Next time, I'll offer some resources to get up to speed on slicers, then build the slicers and the summary sheet for my workbook.
Comments
Post a Comment