Running Excel reports

Our next step is to run an Excel report. These reports can be run from Dynamics GP 2016, or they can be directly opened in Excel 2016. We will look at both these options.

From Dynamics GP 2016

To run an Excel report from within Dynamics GP, follow these steps:

1. In 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 the Option column's options that includes Reports:
   

   Note

   Options that contain the word Reports open Excel reports. Options with Data Connections in the string indicate the data connector to build a new report, not an actual report. You can limit the Excel reports list to just Reports or Data Connections with the Add Filter button just above the Excel reports list.

4. Double-click on the TWO AccountSummary Default item.
5. We disabled the security warning earlier, but just in case, if Excel 2016 opens with a security warning at the top of the worksheet, click on Enable Content.
6. Excel will open with live data from Microsoft Dynamics GP:
   
7. As a test, highlight rows seven through 10 (7-10) on the left-hand side and press the Delete key.
8. Go to Data | Refresh All on the ribbon. Excel 2016 will reconnect to Dynamics GP and bring back in the latest data.

Tip

Saving the report with a different name in the same folder as the GP deployed reports will make that report visible in the list of Excel reports in GP.

From Excel 2016

To accomplish this same task (run a GP Excel refreshable report) from Excel 2016, follow these steps:

1. Open Windows Explorer and navigate to the location where you deployed the reports at the beginning of this chapter. In my example, the reports were deployed to C:\GP2016XL\.
2. Drill down through the folders to Reports | TWO | Financial. This represents the report storage for the sample company's (TWO) financial reports:
   
3. Double-click on TWO AccountSummary Default.xlsx.
4. Excel 2016 will open with live data from Dynamics GP.

Manual versus auto refresh

Excel reports are refreshable, but that doesn't mean that they have to refresh automatically.

Often accountants ask about saving a static version of the file. They love the idea of refreshing data, but they want it to happen on their terms. Most accountants prefer information that doesn't change once it's been finalized, so this request is perfectly natural. By default, the Dynamics GP 2016 connections are designed to refresh automatically when the file is opened, but you can control this.

To understand how to control the refresh options, follow these steps:

1. Start with the TWO AccountSummary Default Excel file that you already have open.
2. In Excel, select the Data tab and then go to Connections | Properties:
   
3. Uncheck the Refresh data when opening the file box and click on OK.
4. Click on Close to return to the worksheet in Excel.
5. To validate that this worked, select rows seven through 10 (7-10) in the Excel sheet and press Delete.
6. Save the Excel sheet to your desktop as TWO AccountSummary Default Manual Refresh and close Excel 2016.
7. To reopen the file, double-click on TWO AccountSummary Default Manual Refresh on the desktop.
8. Excel will open with data, and rows seven through 10 (7-10) will be blank. The sheet did not refresh automatically.
9. To manually refresh the sheet, right-click anywhere in the data area and click Refresh or select Data | Refresh All.