Creating pivot tables from GP 2016 Excel report data

The starting point for pivot tables is simply to use data in Microsoft Excel 2016. I often present a session titled PivotTables 101. After almost every presentation, at least one person tells me that they almost didn't attend because they "already knew how to use pivot tables." They then tell me that they are glad they attended as they didn't know about many of the simple Excel features that can turn an ordinary pivot table into an amazing report, chart, or dashboard. Together, we will create and use those features now.

Getting data to Excel

To build our first pivot table, let's start with the TWO AccountSummary Default Excel report that we used in Chapter 2, The Ultimate GP to Excel Tool – Refreshable Excel Reports. Then, follow these steps:

1. In Dynamics GP, on the navigation pane on the left-hand side, click on Financial. The list pane above will change to show financial items.
2. In the list pane, click on Excel Reports.
3. In the navigation list in the center, select TWO AccountSummary Default. Make sure that you select items where the Option includes Reports.
4. Double-click on the TWO AccountSummary Default item.
5. In Chapter 2, The Ultimate GP to Excel Tool – Refreshable Excel Reports, we looked at how to turn off the Excel 2016 security warning at the top of the worksheet. If it still appears, click on Enable Content and then circle back to the security section in the previous chapter. I'm assuming that you've got this fixed now, so we won't revisit it.

Building a pivot table with a calculated field

Follow these steps to build the pivot table:

1. Use your cursor to select any cell in the table of data from the Excel report that you just brought into Dynamics GP.
2. Go to Insert | PivotTable from the Excel 2016 ribbon.
3. Select a table or range should be marked, and the Table/Range: should be Table_AccountSummary_Default:
   
4. Notice that under Choose where you want the PivotTable report to be placed, we are putting this pivot table in a New Worksheet. Click on OK.
5. A new worksheet will open with the canvas for a new pivot table report. In the PivotTable Fields box on the right-hand side, use your mouse to drag items into the areas at the bottom. Drag:
   • Year to Rows
   • Account Number to Filters
   • Period ID to Columns

     Tip

     If you used the (edited) version of the Excel refreshable report that we created in the previous chapter, you'll have a column that displays the net of debits and credits already. You could use that in Values and skip this step. But then, you would miss the fun of creating a calculated field.

   
6. Click anywhere in the pivot table and a new menu option will appear called PivotTable Tools. Go to PivotTable Tools | Analyze | Fields, Items, & Sets | Calculated Field… We will create the net balance right in the pivot table:
   
7. In the Insert Calculated Field window, enter the name Net Balance. In the Formula field, remove the zero (0), select the Debit Amount field from the Fields list, and select Insert Field. Enter a minus sign (-) in the formula and then add the Credit Amount field. We are creating a formula of debits - credits. Click on OK. The new calculated field will automatically appear in the Value area of the PivotTable Field list:
   
8. This covers all of the accounts. Let's just analyze one account for now. At the top of the pivot table next to Account Number, change (All) to 000-1100-00:
   

That's it. You've built a pivot table. It's really that easy. Now, we're ready to move into some more building blocks.