Generating Word charts from SharePoint metadata

Applies to JungleDocs for SharePoint 2010-2019.
For a similar tutorial for JungleDocs for Office 365, click here.

In this JungleDocs tutorial, you will learn how to visualize your SharePoint list data in the form of Word charts.

Supported chart types

JungleDocs supports the latest chart types in Microsoft Office 2016/2019, including Histogram, Pareto, Treemap, Sunburst, and more. It's a great way to help you quickly visualize common financial, statistical and hierarchical data from your SharePoint.

Add metadata to a Word chart

This tutorial requires prior knowledge on how to use JungleDocs templates, and skips right to chart configuration. Read more:

• Introduction to templates and rules
• Configuring Word templates

1. Open Microsoft Word and insert any kind of chart. Then, right-click on the chart and click Edit Data.

This will open an Excel chart with a predefined table:
2. In the table cells, you need to add placeholders to let JungleDocs know which cells to populate with what metadata from your SharePoint. Placeholders can be added in two ways.

#1: entering formulas into the cells

Copy the content controls from the sample document to the document template. Those content controls will be replaced with SharePoint metadata when running a rule. This is the quicker and simpler method of the two. However, note that documents created in this way will not be updateable once generated.

Microsoft Excel requires that the cell type is consistent with the data that that is inserted. Default cell type is "General" and if all cells are left this way, some data, like dates and numbers, might not be interpreted correctly. To avoid this, Excel cells must be of the same type as SharePoint fields. You can change the cell types by right-clicking on them and selecting Format cells.

#2: adding comments

1. Add a comment to A2 cell (or any other cell that will comprise the top left corner of your repeated section). In the comment, remove the user name and enter one of the following Repeater functions:

=Repeater(arg1; width; height)

=Repeater(arg1; width)

=Repeater(arg1)

The Repeater function has the following arguments:

• arg1   Required. Any function that returns a list of items from SharePoint. 

• width   Optional. The maximum number of sheet columns to be repeated. The default value is 0, meaning that all columns will be copied in a row to the right from the current cell. For example, setting the width to 1 will only allow one (the first one) column to be repeated.
• height   Optional. The maximum number of sheet rows to be repeated. For example, setting the height to 2 will repeat 2 rows in the Excel sheet for every list entry in your SharePoint. The default value is 1.

Examples:

=Repeater(GetView("HR"; "Staff")) – returns all items from the Staff view, HRin your specified lists’ view.

=Repeater(FindItems("HR"; "Position"; "Manager")) – returns all items from the HR list that have "Manager" in the Position column.

4. Apart from the Repeater function, you need to also add additional commands for every SharePoint column that you want to be retrieved automatically. Use the  =ColumnName syntax.

In the example above, a repeated section is configured to have 6 columns and 1 row for every list item, starting from A1 cell. This section will repeat as many times as there are items in the AllItems view. In the A cells, item IDs are displayed. In the B cells, Title column values are displayed. In the C cells, Status column values are displayed.

Creating charts from grouped views

Using the Matrix function, you can make the chart display grouped views from SharePoint. For example, if you want to the chart to display contacts per company, you will first need to create a grouped view to group your contacts per company. Then, you will need to enter the following formula in your Excel cell: =Repeater(Matrix("ListName"; "GroupedViewName";))

The example of the chart displaying a grouped view from SharePoint metadata: