How to Populate Dynamic Charts in Your Generated Document

Problem:

You have a Salesforce report with raw data and you want to show a visual representation of this data via a chart in your generated document.

Downloads:

Get the Excel template here: Dynamic Charts – Excel Logic
Get the PowerPoint template here: Dynamic Charts – PPT

Steps:

1. First, we will need to figure out what formatting our data needs to be in to display correctly in our chart. Open your PowerPoint or Word template and insert the desired chart type. Add sample data. Make any needed adjustments to the chart design to get the desired look (add chart labels, remove the legend, etc). Now we know the structure we need our data in. For this example, we want our chart to show the total number of Opportunity records categorized by Stage.

2. If you haven’t already, create a Salesforce report containing your data. Our example will use a Salesforce report with the Opportunity Name and Stage fields. Since we want the chart to group the data by Stage we will ensure our Salesforce report is sorting by the Stage field.

3. Open a blank Excel spreadsheet. We will push our raw Salesforce report data into Sheet 1. We will use Sheet2 to reformat the data using formulas. Add sample data in Sheet1 that matches your Salesforce report formatting.

4. On Sheet2, use formulas to restructure your data. Your data should be restructured to match the sample data you added to your chart in Step 1. For our example, we use formulas in columns A-E. Columns D & E contain our data in the desired formatting, Stage and Count.

5. Highlight the cells containing the desired data and give this range a unique name in the upper left-hand corner. For our example, we’ve selected D1:E16 and renamed the range ‘TableData’. Why did our range stop at row 16? For our use case we know there will never be more than 15 records. What if there are less than 15? When we generate the document our range will only pick up fields with data, automatically deleting any blank cells. Why does my Excel have background colors? For best practices, we have added a blue background for any cells containing a formula so they are never accidentally deleted in the template and a green background for cells that will contain our desired data.

6. Save the Excel and upload to your DDP. Set the Start Page and End Page to 0.

7. Locate the Salesforce report in the left grid, highlight the Excel in the middle-grid, and drag the report to the very right grid. Enter ‘Sheet1’ in the Sheet/Range field. This is the name of the Excel sheet we want the raw data to populate in.

8. Go back to your PowerPoint and open the Selection Page (Home > Select > Selection Pane). Click on the chart and rename it with the range name from Step 5. For our example we used the range name ‘TableData’.

9. Save the PPT template and upload to the DDP. The Excel should be the first file and the PPT should be the second file.

10. Now locate the Excel template again in the left grid, click the PPT template so it is highlighted, and drag the Excel file to the very right grid. The Sheet/Range name should be the name of the Excel range which is also the name of the chart. For our example, the Sheet/Range name will be ‘TableData’.

11. Run the DDP. When the DDP is ran, the Salesforce report data will populate in Sheet1 of the Excel. Any needed reformatting will happen on Sheet2. The cell range selected in Sheet2 will then be pushed into the PPT chart Excel.