Creating Word reports and charts from SharePoint metadata

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

With JungleDocs, you can easily create clear-cut reports and charts that have all the relevant data automatically retrieved from SharePoint.

Configuration

In this tutorial, we will create a sales report that compares the performance of each month against that of previous years. We will start by extracting months and years into separate columns and finish by creating the report itself.

1. Extract variables into separate columns

1. Go to the library that contains your sales information.

2. Select Library settings from the ribbon.

3. Scroll down to columns and select Create column.

4. Enter a column name. In the Formula field, enter the formula for extracting data from document properties. In the example, we want the formula to display the month, so the formula is =MONTH{[Document Date]}. Finally, select the data type to be collected. Click OK.

5. Go back to the library and create the year column. You need to fill in the same fields as in the previous step. The formula for displaying the year: =TEXT([Document Date]," yyyy ").

2. Create a grouped view

1. Go to the library that contains your sales information.

2. Click on the three-dot icon (context menu), and select Create View.

3. Select your view type and view name, then select the columns that you want to be displayed.

6. On the Group By section, for the FIrst group by the column, select Month. For the Then group by the column, select Year.

7. Click OK to save view settings. This is the result:

3. Create a JungleDocs template

Now we can create the template for our sales report. To save time, you could use an existing template and adapt it to your current needs.

1. Go to the library where you save document templates and create a new Word document from the ribbon.

2. Open the template and modify its structure and content to suit your preferences. If your document contains a chart, JungleDocs can fill this chart with SharePoint data automatically.

3. Right-click on the chart and select Edit Data.An Excel of your chart with a predefined table will now open. We are going to use the grouped view we created earlier, and the Matrix function to let JungleDocs gather data automatically when prompted.

4. Right-click on the A2 cell and select Insert Comment to enter a JungleDocs formula that populates the chart with SharePoint data. In the example, we used the following formula: =Repeater(Matrix("Invoices";"Monthly comp";"Sum($Items;\"Subtotal Amount\")"))

As said before, in the report we want to see how well each month of the year performs in comparison to previous years. We used the Invoices list where the information we want to gather is stored; the Monthly comp grouped view because we want an overview of months; and a formula containing the Sum function that will give us the subtotal of the revenue gathered from all the invoices.

4. Create a JungleDocs rule

Now that we have created our template we are ready to create a JungleDocs rule. You could also opt to create a new content type. However, creating a new JungleDocs rule is simpler and faster.

1. Navigate to the library in which your reports will be saved once created, and open JungleDocs from the ribbon.

2. On the New tab, click  New rule.

4. On the Save location, select Save document to the current library. Then, under Document template select Custom template and click Manage custom template. Enter the URL of the template you created in the previous part of this tutorial.

5. Select a name for your Base rule and click OK to save the new rule. 

Results

You can now easily create new sales reports straight from JungleDocs. Let's have a quick look at how to do that.

1. Navigate to the list view that you chose to save sales reports.

2. Open JungleDocs from the ribbon and click on the rule that you've created in the previous step.

A Word report will be created and opened for you, based on the template configuration and having the related SharePoint data already included in the chart. Needless to say, you only have to configure the rule once, but it will save you a lot of time down the road.