Tag: charts

How to Populate Dynamic Charts in Your Generated Document


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.


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


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.
Preformat Chart
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.

How to Populate Dashboard Images in Your Generated Document


You want your generated document to contain a dashboard image.


1. Navigate to the Salesforce Dashboard and right-click on the image. Select ‘Copy image address’.
Copy image address
2. Navigate to the record you are running from and paste the URL into a text field. For our example, we are using the Description field on the Lead object.
URL Field
3. Next, navigate to the DDP and click the Field Tagger button. Once the Field Tagger window has opened, select your Main Object and the Field containing the dashboard image URL. For this example I am running from a Lead record and have my dashboard image URL in the Description field. The tag given to us in the Field Tagger is <<Lead_Description>>.
4. Now that we have the field tag, we will need to insert a placeholder image into our template document. Open your PowerPoint or Word template, select Insert from the toolbar ribbon and click Pictures. Select a photo from your computer to be used as the placeholder image and click the Insert button.
5. Once your placeholder image is inserted in the template, click Home > Select > Selection Pane. If you click on the image you will see the image name is ‘Picture 1’ in the Selection Pane window on the right-hand side.
6. Double-click on ‘Picture 1’ in the Selection Pane to rename the image. Paste the field tag from Step 3 and append ‘__image’ to the end. For our example the tag will be <<Lead_Description__image>>.
7. Save the template.
8. Upload the template to the DDP.
9. Run the DDP. When the DDP is ran the placeholder image will be replaced with the dashboard image.

Note: The generated document will populate with the latest image from the dashboard. To ensure the dashboard image in your generated document is up to date you will either need to manually refresh the dashboard prior to the document being generated or schedule the dashboard to refresh.