Creating pivot tables from GP 2016 data connections

One problem with creating a pivot table from data in Excel 2016 is that we have to bring the data from Dynamics GP 2016 into Microsoft Excel. That's not much of a burden when we're dealing with a few hundred rows, but when you get to transactional data, it's easy to have a couple of hundred thousand rows. This is where pivot table performance starts to bog down. For companies with a lot of accounts, high transaction volume, and a lot of history, it's easy to exceed Excel's maximum row count of 1,048,576 rows. Microsoft Excel 2016 lets us build pivot tables without having to bring the data into Excel.

When we deployed Dynamics GP 2016's Excel reports, we also deployed the data connectors. The connectors let us use data in Dynamics GP to build pivot tables without having to bring detailed data into Excel 2016. Let's use this to start building our dashboard. For much of the rest of the book, you'll want to save the file that we're working with. It will ultimately grow into our dashboard. To build a pivot table from Dynamics GP without having to bring everything into Excel, follow these steps:

1. Open a new worksheet in Excel 2016. Go to Data | Existing Connections:
   
2. In the bottom-left corner of the Existing Connections window, click on Browse for More…
3. In the Select Data Source window, map your way to the location of your deployed GP excel refreshable reports, by going to Data Connections | <your company ID> | Financial. Select the company ID AccountSummary.odc, in our case TWO AccountSummary.odc:
   
4. Excel 2016 will ask you what to do with the data. Despite also being named AccountSummary, this connector is a little different. The AccountSummary Default report is actually a limited subset of data from the AccountSummary view. The AccountSummary connector has everything in the view, so there is a lot more data available:
   
5. Select PivotTable Report and click on OK.

Tip

In versions prior to Excel 2016, merely double-clicking on a *.odc file would take you right to the Import Data window, allowing you to create a pivot table or chart without bringing the data into a worksheet.

Selecting the Only Create Connection option is another cool way to create your pivot table and/or chart without storing any data in Excel. Each time it opens, it'll refresh with new data.

Building a revenue pivot table

Now, we have the canvas for a pivot table again, but this time, it's based on Dynamics GP data contained in the SQL Server, not on data that we've brought into Excel. This also makes the Excel files much smaller.

Let's use this data to build a revenue pivot table for our dashboard using these steps:

1. Drag Account Number into Rows.
2. Drag Account Category into Filters.
3. Drag Year and Period ID into Columns with Year on top.
4. Drag Period Balance into Values.
5. Click the drop-down box on the Account Category Number filter at the top of the pivot table next to (All).
6. Select the Select Multiple Items box.
7. Uncheck (All).
8. Scroll down and select Sales and Sales Returns and Discounts:
   
9. Select cell B4. This should contain the year 2013. To filter this to only show 2017, select the drop-down box next to Column Labels.

   Tip

   Remember that these steps use the lesson data called Fabrikam. If you are using your own data, the data will vary, so take that into account.

10. Uncheck Select All and select 2017.
11. Select cell B5. This should now contain period 1. To filter this to only show the first four months of 2017, select the drop-down box next to Column Labels.
12. Uncheck Select All and check periods 1 through 4.
13. Finally, let's clean things up. On the menu bar, go to PivotTable Tools | Design | Grand Totals | On for Columns Only:
    
14. Select cells B6 through F6, and right-click and go to Value Field Settings | Number Format. Choose the formatting option you desire for the currency values in these columns:
    

    Tip

    Yes, you could format these cells using the method for any normal worksheet. However, if you want the pivot table to retain these formats, you'll want to use this method.

    Formatting can also be achieved by clicking on the down area for Sum of Period Balance in the Values area of PivotTable Field list.

15. Right-click on the tab at the bottom of the worksheet, select Rename, and type Revenue.
16. Right-click in the pivot table and select PivotTable Options. Name the pivot table Revenue.
17. Save the file as GP 2016 Dashboard.xlsx. Make sure to save it somewhere that you can find it. We will continue working with this file. Saving here is just a precaution.

We've now built the source for the revenue data in our dashboard. This will also become our drill-back to the account numbers to support our revenue total.