Creating connected pivot tables from inside Excel

So far, we've created pivot tables by starting in Dynamics GP 2016 and sending information to Excel. We can also use Excel 2016 to pull data out of Dynamics GP. For our dashboard, we need some sales data and receivables totals. The sales data that we want exists as a Dynamics GP data connection. The receivables data connection doesn't contain quite what we need, so we'll have to adjust it.

Building the sales pivot table

Let's add a sales pivot table first. To create our sales pivot table, follow these steps:

1. In Excel 2016, click plus (+) next to the worksheet tabs 2016 to add a new worksheet. Name the worksheet Top Ten Customers.
2. Select the Data tab and click on Existing Connections from the ribbon.
3. Click on Browse for More… and navigate to where you installed the GP 2016 data connections for the sample company. Earlier, we deployed it to C:\GP2016XL\Data Connections\TWO.
4. Select the Sales directory and double-click on TWO SalesTransactions Posted Invoices.odc:
   
5. The Import Data window will open. Select PivotTable Report and click on OK.
6. Click in the pivot table framework area to bring up the PivotTable Field list on the right-hand side.
7. Drag Customer Name into Rows.
8. Drag Document Amount into Values.

We don't need all of our customer's sales; that's too much information for a dashboard. Let's limit it to the top 10 customers by sales. To show only the top 10 customers, follow these steps:

1. Click on the first customer name in the sales pivot table.
2. Right-click and go to Filter | Top 10.
3. Notice that we're not limited to the top or just to 10 items, but this is good enough for now. So, click on OK:
   
4. To sort the sales from the highest to lowest, select the first customer in the pivot table. Click on the filter icon for the Row Labels and select More Sort Options…:
   
5. In the Sort (Customer Name) window, select Descending (Z to A) by: and choose Sum of Document Amount from the drop-down list:
   

   Tip

   Like formatting, if you want to save your sort options for a pivot table, you'll need to perform the sorting in the pivot table itself. Often, formatting and sorting performed from the ribbon will not save.

6. Format the Sum of Document Amount column as you did for the previous pivot tables.
7. Right-click in the pivot table and select PivotTable Options. Name the pivot table Top 10 Customers.
8. Save the file.

Adding a receivables pivot table

Now, our pivot table has been reduced to just the top 10 customers by sales and ordered appropriately. All that is left for our foundation is adding receivables aging. To add receivables, follow these steps:

1. In Excel 2016, click plus (+) next to the worksheet tabs to add a new worksheet. Name the worksheet Receivables Aging.
2. Select the Data tab and click on Existing Connections from the ribbon.
3. Click on Browse for More… and navigate to where you installed the GP 2016 data connections for the sample company. Earlier, we installed this in C:\GP2016XL\Data Connections\TWO.
4. Select the Sales directory and double-click on TWO Customers Past Due Customers.
5. The Import Data window will open.
6. We need to modify the data coming in, so go to Properties | Definition.

This view holds past due customer amounts. We want all customer amounts, not just past due totals, so we have to remove the limitation on this data. Follow these steps:

1. In the Command text window, scroll down to the word where. Highlight the word where with your mouse and select all the rest of the text:
   
2. Press Delete to remove the restrictions. Click on OK to confirm the message that this workbook will no longer match the file and click on OK to continue.
3. Select PivotTable Report and click on OK.
4. Click in the pivot table canvas area to bring up PivotTable Field list on the right-hand side.
5. Drag each one of the Aging Buckets down into Values.
6. Drag Values from the Columns area to Rows:
   
7. Format each Aging Bucket on the pivot table to displays a currency or with commas and decimals.
8. Right-click in the pivot table and select PivotTable Options. Name the pivot table Receivables Aging.
9. Save the file.

Now, the data foundation of our dashboard is complete.