How to export a Microsoft Dynamics GP Report to Excel 2007

Export a report to Microsoft Excel

To export a report such as the Trial Balance Detail report to Microsoft Excel, follow these steps:

1. In Microsoft Dynamics GP, point to Financial on the Reports menu, and then click Trial Balance.
2. In the Trial Balance Report window, click Detailed on the Reports menu.
3. Do one of the following:
   • Select an existing report option, and then click Modify.
   • Create a new report. To do this, follow these steps:
     1. Click New.
     2. In the Trial Balance Report Options window, type a name for the report option that you want to use.
     3. In the Include section, click to select the Posting Accounts check box.
     4. Select the settings and restrictions that you want to use with the new report option. Then, click OK.
4. Select the Destination options. To do this, follow these steps:
   1. Click the Destination button.
   2. Click to select the Destination to Screen check box and the To File check box.
   3. Click the Folder icon.
   4. In the "Save to Export file" window, browse to where you want to save the report.
   5. In the Save as type box, click Comma Delimited Files.
   6. In the File name box, type a name for the file.
   7. Click Save.
   8. In the Report Destination window, click the Replace option, and then click OK.
5. In the Trial Balance Report Options window, click the Print button to print the report. After the report has finished printing to the screen, close the window.
6. Open the file in Microsoft Excel. To do this, follow these steps:
   1. Start Excel.
   2. Click the Microsoft Office button, and then click Open.
   3. In the Open window, locate the saved report, and then open the file.
      Note If the file is not displayed, click All Files (*.*) in the Files of type box, and then click Open.
7. To display the data completely in each cell, follow these steps:
   1. Select all the columns.
   2. On the Home tab, click Format in the Cells group, and then click AutoFit Column Width.

Modify a report before exporting to Excel

Data in the reports may not align correctly to their appropriate columns after the report is imported into Excel. For example, in the Trial Balance Detail report example, the Total amount field may not align with the debit and credit amounts.

This problem may occur when the number of column header fields does not match the number of detail fields.

To prevent this problem, create fields in Report Writer that act as filters so that the number of header fields matches the number of detail fields. To do this, follow these steps:

Step 1: Back up the report, and then open the report in Report Writer

1. If you have any modified Microsoft Dynamics GP reports, back up the Reports.dic file. To locate the Reports.dic file, follow these steps:
   1. Open the Edit Launch File window. To do this, do one of the following:
      • Microsoft Dynamics GP 10.0
        
        On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click Edit Launch File.
      • Microsoft Dynamics GP 9.0
        
        On the Tools menu, point to Setup, point to System, and then click Edit Launch File.
      Note If you are prompted to enter the system password, type the password, and then click OK.
   2. In Microsoft Dynamics GP 10.0 and in Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP in the Edit Launch File window.
      
      Note The path is displayed in the Reports box.
2. Open Report Writer. To do this, do one of the following:
   • Microsoft Dynamics GP 10.0
     
     On the Microsoft Dynamics GP menu, point to Tools, point to Customize, and then click Report Writer.
   • Microsoft Dynamics GP 9.0
     
     On the Tools menu, point to Customize, and then click Report Writer.
3. Open the report in Report Writer. To do this, follow these steps:
   1. In the Product list, click Microsoft Dynamics GP. Then, click OK.
   2. Click Reports.
   3. In the Original Reports section, select the report that you want to modify, and then click Insert. For example, insert the Trial Balance Detail report.
   4. In the Modified Reports list, select the report that you want to modify, and then click Open. For example, open the Trial Balance Detail report.
   5. In the Report Definition window, click Layout.

Step 2: Create a new calculated field

1. In the Toolbox window, click Calculated Fields in the Fields list, and then click New.
2. In the Calculated Field Definition window, type Filler in the Name field.
3. In the Result Type list, click String.
4. In the Expression Type area, click Calculated.
5. On the Constants tab, click String in the Type list.
6. Leave the Constant box blank. Then, click Add.
7. Click OK to save the calculated field. Then, close the window.

Step 3: Add the new calculated field to the report layout

Drag the newly created calculated field to the appropriate section in the Report Layout window. After you do this, the number of fields that contain headers matches the number of fields that contain details or amounts.

Step 4: Save the report, and then exit Report Writer

1. Click the Close button to close the Report Layout window.
2. Click Save when you receive the following message:
   Do you want to save the changes to this report layout?
3. Click OK to close the Report Definition window.
4. On the File menu, click Microsoft Dynamics GP.

Step 5: Assign security permissions to the modified report

To assign security permissions to the modified report, use one of the following methods.

Method 1: Use security in Microsoft Dynamics GP 10.0

1. On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to Security, and then click Alternate/Modified Forms and Reports.
2. In the ID box, type the ID of the user who will print this modified report.
3. In the Product list, click Microsoft Dynamics GP.
4. In the Type list, click Reports.
5. Expand Sales.
6. Expand the node for the order or invoice form that you modified.
7. Click Microsoft Dynamics GP (Modified).
   Note A check mark appears next to the name.
8. Click Save.

Method 2: Use Advanced Security in Microsoft Dynamics GP 9.0

1. On the Tools menu, point to Setup, point to System, and then click Advanced Security. If you are prompted, type the system password.
2. Click View, and then click by Alternate, Modified and Custom.
3. Expand Microsoft Dynamics GP, expand Reports, expand Sales, and then expand the node for the quote, order, or invoice form that you modified.
4. Click Microsoft Dynamics GP (Modified).
5. Click Apply, and then click OK.

Note By default, the current user and company are selected when you start Advanced Security. Any changes that you make are for the current user and company. However, you can select additional users and companies in the Company area and in the User area of the Advanced Security window.

Method 3: Use Microsoft Dynamics GP security in Microsoft Dynamics GP 9.0

1. On the Tools menu, point to Setup, point to System, and then click Security. If you are prompted, type the system password.
2. In the User ID list, select the ID of the user who will access the report.
3. In the Type list, click Modified Reports.
4. In the Series list, click Sales.
5. In the Access List box, double-click the order or invoice form that you modified, and then click OK.
   Note An asterisk appears next to the report name.