Running Excel reports
Our next step is to run an Excel report. These reports can be run from Dynamics GP 2013 or they can be directly opened in Excel 2013. We will look at both of these options.
From Dynamics GP 2013
To run an Excel report from within Dynamics GP:
- In the Navigation Pane on the left, click Financial. The List Pane above will change to show financial items.
- In the List Pane, click Excel Reports.
- 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.
- Double-click on the TWO AccountSummary Default item.
- We disabled the security warning earlier, but just in case, if Excel 2013 opens with a security warning at the top of the worksheet, click Enable Content. Then go back and review the section on Excel 2013 security earlier in this chapter.
- Excel will open with live data from Microsoft Dynamics GP:
- As a test, highlight rows seven through ten (7-10) on the left and press the Delete key.
- Select Data | Refresh All on the ribbon. Excel 2013 will reconnect to Dynamics GP and bring back in the latest data.
From Excel 2013
To accomplish this same task from Excel 2013, follow these steps:
- 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:\GP2013XL\. - Drill down through the folders to Reports | TWO | Financial. This represents the report storage for the sample company's (TWO) financial reports:
- Double-click on TWO AccountSummary Default.xlsx.
- Excel 2013 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 2013 connections are designed to refresh automatically when the file is opened, but you can control this.
To understand how to control the refresh options:
- Start with the TWO AccountSummary Default Excel file that you already have open.
- In Excel, select the Data tab followed by Connections | Properties:
- Uncheck the Refresh data when opening the file box and click OK.
- Click Close to return to the worksheet in Excel.
- To validate that this worked, select rows seven through ten (7-10) in the Excel sheet and press Delete.
- Save the Excel sheet to your desktop as
TWO AccountSummary Default Manual Refreshand close Excel 2013. - To reopen the file, double-click
TWO AccountSummary Default Manual Refreshon the desktop. - Excel will open with data, and rows seven through ten (7-10) will be blank. The sheet did not refresh automatically.
- To manually refresh the sheet, right-click anywhere in the data area and click Refresh or select Data | Refresh All.