Frequently asked questions about the new Excel Report Integration in Microsoft Dynamics GP 10.0

General questions and answers

Q1: What is Excel Report Integration in Microsoft Dynamics GP 10.0?
A1: By default, Excel reports are created based on existing SmartList favorites. These reports use an Office data connection (ODC) to update data at any time.

Q2: Is Excel Report Integration available to all customers?
A2: Excel Report Integration is available for Business Essentials and for Advanced Management Business Ready Licensing. There is no additional charge for Excel Report Integration. If you want to deploy Excel Report Integration by using Microsoft Office SharePoint Server 2007, there is an additional charge to do this.

Q3: What version of the Microsoft Office system is supported with Excel Report Integration?
A3: The following versions of the Office system are supported:

• The 2007 Microsoft Office system

• Microsoft Office 2003

• Microsoft Office XP

The Excel reports that you deploy should be 2007 Microsoft Office system reports (*.xlsx). To view the reports in Office 2003 and in Office XP, you can download and install the Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats.

Questions and answers about deployment

Q1: Where can I deploy the Excel reports for Microsoft Dynamics GP?
A1: To deploy the Excel reports, use one of the following methods:

• Deploy the reports to a folder on a shared network location.

• Deploy the reports to SharePoint Server 2007 Report Center.

Q2: How can I deploy the Excel reports to a folder on a shared network location?
A2: To deploy the Excel reports to a network share, follow these steps.

Note This procedure is also available in Chapter 30, "Report Deployment," of the SystemSetup.pdf file. Chapter 30 begins on page 189.

1. In Windows Explorer, create a folder on the network. For example, create an "Excel Reports" folder.
   
   Note If you want to deploy the Excel reports and the data connections to different folders, create each folder in the root directory, or create subfolders in a folder.

2. Right-click the folder that you created, and then click
   Sharing and Security.

3. On the Sharing tab, click Share this folder.

4. Click Permissions.

5. Click Add.

6. In the "Select Users, Computers, or Groups" window, enter the group or the users who you want to have access to the shared reports.

7. Click OK.

8. Click to select the Allow check box for the Change permission, and then click OK. The Change permission is the minimum required permission.

9. Click the Security tab.

10. In the Groups or user names area, click
    Add.

11. In the "Select Users, Computers, or Groups" window, enter the group or the users who you want to have access to the shared reports.

12. Click OK.

13. In the Groups or user names area, click each group or user, and then click the permission that you want the group or the user to have. The minimum permission is the Read permission.

14. Click OK.

15. Use the sa user name and password to log on to Microsoft Dynamics GP.

16. On the Microsoft Dynamics GP menu, click
    Tools, click Setup, click
    System, and then click Reporting Tools Setup.

17. Click the Data Connections tab, and then enter the path of the shared folder in the System Level Data Connections field. The following path is an example:

    \\computername\Excel reports\Data Connections Note The "Data Connections" folder must also be created in step 1. The deployment does not create the "Data Connections" folder.

18. In the Data Connections Deployment area, click to select the Enable data connection deployment check box.

19. If you want to set up all the companies, click to select the Deploy data connections for all existing companies check box.

20. Click Run Deployment.
    
    Note The data connections must be deployed before you deploy the Excel reports.

21. Click Yes when you receive the following message:

    Deployment is complete. Do you want to view the deployment report? Then, verify that the deployment is successful.

22. Click the Reports Library tab.

23. In the System Level Reports area, enter the path of the shared folder. The following path is an example:

    \\computername\Excel Reports\Reports Note The Reports folder must also be created in step 1. The deployment does not create the Reports folder.

24. In the Excel Report Deployment area, click to select the Enable Excel report deployment check box.

25. If you want to set up all the companies, click to select the Deploy Excel Reports for all existing companies check box.

26. Click Run Deployment.

27. Click Yes when you receive the following message:

    Deployment is complete. Do you want to view the deployment report? Then, verify that the deployment is successful.

28. Configure security for the users.
    
    Note To do this, see the "Questions and answers about security" section later in this article.

Q3: Do the *.odc files have to be in a shared folder separate from the *.xlsx Excel reports?
A3: The *.odc files do not have to be in a separate folder. However, separate folders are ideal for organizational purposes.

Q4: How do I deploy the Excel reports to SharePoint Server 2007 Report Center?
A4: To deploy the Excel reports to Report Center, follow these steps.

Note The Web server must be a 32-bit server. Excel report deployment is not supported on a 64-bit Web server.

1. Verify that you can start SharePoint Server 2007 Report Center. The default address of Report Center is as follows:

   http://servername:port/reports Notes

   • Replace servername with the name of the Web server.

   • Replace port with the port number of the SharePoint site.

   • If the site runs on port 80, you do not have to specify a port in the address.

2. On the Web server, insert the Microsoft Dynamics GP 10.0 CD 2 into the CD drive.

3. Double-click the Setup.exe file.

4. Under Additional Products, click
   Microsoft Office SharePoint Server Wizard, and then click
   Install.

5. Click to select the I accept the terms in the License Agreement check box, and then click Next.

6. Enter the installation location for the SharePoint Server wizard, and then click Next.

7. Click Install.

8. Click Start, point to
   Programs, point to Microsoft Dynamics GP, point to Business Intelligence, and then click Microsoft Office SharePoint Server Wizard.

9. Click to select the Create Data Connections for Microsoft Dynamics GP check box. Then, click to select the Create Excel Reports for Microsoft Dynamics GP check box, and then click
   Next.

10. Enter the name of the server that is running SQL Server and on which the Microsoft Dynamics GP 10.0 databases are stored. Then, enter the sa username and password, and then click Next.

11. Select the company databases for which you want to deploy the Excel reports, and then click Next.

12. In the Target URL box, enter the path of Reports Center. The address for Reports Center is as follows:

    http://servername:port/reports/ Notes

    • Replace servername with the name of the Web server.

    • Replace port with the port number of the SharePoint site.

    • If the site runs on port 80, you do not have to specify a port in the address.

13. Click Finish.

14. Configure security for the users.
    
    Note To do this, see the "Questions and answers about security" section later in this article.

Q5: What is the difference between deploying the reports and the data connections to a System Level location instead of to a User Level location?
A5: A System Level location is used if you want to deploy the reports and the data connections to a network share. A User Level location is used only for individual user retrieval purposes. The user keeps the files locally in their profile to retrieve.

Q6: When the deployment process is complete, what is actually deployed?
A6: the Excel reports and the ODC files are deployed. The Excel reports have an .xlsx file name extension, and the ODC files have an .odc file name extension.

Q7: What is the difference between the *.xlsx files and the *.odc files?
A7: The *. xlsx files are the Excel workbooks. The *.odc files are the Office data connections to the Microsoft Dynamics GP databases. The *.odc files are used by the *.xlsx files to populate the workbooks with Microsoft Dynamics GP information.

Q8: Where do I view the Excel reports and the ODC files in SharePoint Server Report Center?
A8: Use the following link to start Report Center, and then click Reports or Data Connections:

http://servername:port/reports/ReportsLibrary

Notes

• Replace servername with the name of the Web server.

• Replace port with the port number of the SharePoint site.

• If the site runs on port 80, you do not have to specify a port in the address.

Q9: How many Excel reports are deployed?
A9: This number depends on the modules that are registered. If all the modules are registered, approximately 190 Excel reports are deployed, and approximately 295 data connections are deployed.

Q10: Can I view the Excel reports in Microsoft Dynamics GP?
A10: Yes. If you deployed the Excel reports and the data connections to a network share, you can view the reports in the Administration navigation pane under Other Reports. To configure the link from Microsoft Dynamics GP to the Excel workbooks and to the ODC files in SharePoint Server 2007, follow these steps:

1. Start Microsoft Dynamics GP.

2. Click Microsoft Dynamics GP, point to
   Tools, point to Setup, point to
   System, and then click Reporting Tools Setup.

3. Click the Data Connections tab. In the System Level Data Connections field, enter the following link to the data connections:

   http://servername:port/reports/Data Connections Notes

   • Replace servername with the name of the Web server.

   • Replace port with the port number of the SharePoint site.

   • If the site runs on port 80, you do not have to specify a port in the address.

4. Click the Reports Library tab. In the System Level Reports field, enter the following link to Reports Library:

   http://servername:port/reports/ReportsLibrary Notes

   • Replace servername with the name of the Web server.

   • Replace port with the port number of the SharePoint site.

   • If the site runs on port 80, you do not have to specify a port in the address.

5. Click OK.

6. In the navigation pane, click Administration, and then click Other Reports.
   
   Note By default, only the users who have administrative credentials on the server that is running SQL Server can view the Excel reports. All other users do not have access to the databases after the deployment. For more information, see the "Questions and answers about security" section.

Q11: Can I change the existing Excel Reports?
A11: Yes, you can change an existing Excel report, and you can save the report by using the same name or by using a new name.

Q12: Can I create a new Excel report?
A12: Yes. You can use the data connection that is provided to create a unique Excel report for your requirements. By using the data connection, you can view all the columns that can be in the report. You can then remove the columns that you do not want to use. When the file is saved, it is saved as an *.xlsx file.

Q13: Can the reports and the data connections be deployed on a 64-bit server?
A13: If you deploy the reports and the data connections to a network share, the server can be a 64-bit server. If you deploy the reports and the data connections to SharePoint Server 2007, the computer that is running SharePoint Server 2007 cannot be a 64-bit server.

Q14: Can the Excel Reports be deployed to Outlook from SharePoint and viewed in Outlook?
A14: Yes, you can deploy the Excel Reports and view them in Outlook by using the following steps:

1. Access the top-level SharePoint Home page or site.
   

2. Click the Reports tab to access the Report Center site.
   

3. In the left-hand navigation menu, click the Reports link.
   

4. On the Reports Library page, click Actions, and then select the Connect to Outlook option.
   

5. In the Connect this SharePoint Document Library to Outlook message, click Yes.
   
   Note When the Outlook Send/Receive Progress box finishes, it will close.
   

6. Under the Mail Folders in Outlook, you will see a SharePoint Lists main folder that has a Reports - Reports Library subfolder under it.
   

7. On the Reports Library page, click Settings, and then click Document Library Settings.
   

8. Under General Settings, click Advanced Settings.
   

9. Next to the Browser-enabled Documents area, verify that the Open in the client application option is selected, and then click OK to save the changes.
   

10. In Outlook, under Mail Folders, locate and expand SharePoint Lists.
    

11. Expand Reports - Reports Library.
    
    You should see a list of company folders. Each company folder will hold the different module folders that hold the Excel Reports for the specific module. If you click one of the modules, you will see a list of Excel Reports under that module folder, and you should be able to select and view Excel Reports in Outlook.
    

Note Microsoft Office SharePoint Server 2007 is required because you need to use the Report Center site. The Report Center site is not available in Windows SharePoint Services 3.0.

Questions and answers about security

Q1: Which users have access to the deployed Excel reports and to the deployed data connections?
A1: By default, when you deploy the Excel reports and the data connections to a network share, users can view the reports and the data connections only if the users have the following access:

• Local administrative credentials on the server that is running SQL Server

• Access to the network share

By default, when you deploy the Excel reports to SharePoint Server 2007 Report Center, users can view the reports only if the users are SharePoint administrators, and if the users have local administrative credentials on the server that is running SQL Server.

Q2: How do I set up security access to the Excel reports and to the data connections if they are deployed to a network share?
A2: Note By default, users can view the Excel reports and the data connections only if the users have administrative credentials on the server that is running SQL Server, and if the users have access to the network share.

There are two components to the security setup:

• Security of the share folder.

• Security at the database level

Security of the shared folder

1. Open Windows Explorer, and then locate the network share that you created. For example, locate the Excel Reports network share.

2. Right-click the folder, and then click Sharing and Security.

3. Click Permissions. The users or the groups who you want to have access must have a minimum of the Change permission.

4. Click the Security tab. The users or the groups who you want to have access must have a minimum of the Read permission.

5. Click OK.

Security at the database level
Users must have access to the appropriate database roles to view the reports. All database roles begin with "rpt_."

Note All users who have local administrator permissions on the server that is running SQL Server automatically have access to all database objects. By default, all users who are not administrators have no access.

1. Open SQL Server Management Studio, expand
   Security, and then expand Logins.

2. Create a Windows logon for the user or group for whom you want to set up security.
   
   For example, create a Windows logon for domain\alias or for the group name.

3. Give the user access to the company database, and then give the user only the database role that the user must have.
   
   For example, if you want the user to print only Payroll in Microsoft Dynamics GP reports, grant the user access to the rpt_payroll role. Refer to question 5 later in this section for a list of roles that correspond to the Excel reports.

Q3: How can I use the User Level option to deploy the Excel reports and the data connections?
A3:

1. The User Level options can be used if a user wants to change their own reports and save the reports locally, and the user wants to have access to the reports on the network share.

2. Enter the following path in the User Level Reports box:

   \\servername\Documents and Settings\%\report share Note % is a wildcard character for the user's alias.

3. Close and restart Microsoft Dynamics GP.

Q4: How do I set up security for the Excel reports when the reports are deployed to a SharePoint Server 2007 server?
A4: There are two components to the security setup.

• Use SharePoint groups or individual users to set up security in SharePoint Server 2007.

• Security at the database level.

Security setup in SharePoint Server 2007

1. Make sure that you log on to the computer as a user who has SharePoint administrator rights.

2. Visit the home page of the SharePoint Web site. The default path is as follows:

   http://servername:port

3. If you are not going to create a SharePoint group to manage security, go to step 13. Click Site Actions, point to
   Site Settings, and then click People and Groups.

4. In the New drop-down list, select
   New Group.

5. In the Name field, enter the name of the group, and then enter a description in the About Me field. For example, you can enter Excel Report Users for both fields.

6. In the Give Group Permission to this Site section, click the permission that you want the group to have. The minimum permission is the Read-Can View only permission.
   
   Note The permissions that are given at the parent site are inherited at the Report Center subsite.

7. Click Create.

8. Click Groups.

9. In the All Groups list, select the group that you created.

10. In the New drop-down list, select
    Add Users.

11. In the Add Users window, enter the users who you want to have access to the Excel reports in the Users/Groups area.

12. Click OK.

13. If you want to set up security by using only users, click Groups.

14. In the New drop-down list, select Add Users.

15. In the Add Users window, enter the users who you want to have access to the Excel reports in the Users/Groups area.

16. In the Give Permission section, click the Give users permission directly option.

17. Click the permission that you want the user to have. The minimum permission is the Read-Can View only permission.

Security at the database level
Users must have access to the appropriate database roles to view the reports. All database roles start with "rpt_."

1. Start SQL Server Management Studio, expand
   Security, and then expand Logins.

2. Create a Windows logon for the user or group for whom you want to set up security. For example, create a Windows logon for domain\alias.

3. Give the user access to the company database, and then give the user only the database role that the user must have. For example, if you want the user to print only Payroll reports, grant the user access to the rpt_payroll role. Refer to question 5 for the list of roles that correspond to the Excel reports.

Q5: Do you have a list of the Excel reports that correspond to the SQL Server database roles that can be used to set up security to the reports?
A5: To obtain the list, click the following link:

https://mbs.microsoft.com/fileexchange/?fileID=e4bb6958-0f07-4451-b72c-f02784e484df

Release Date: March 7, 2008




Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.


Q6: Can other users see the Excel reports that I create?
A6: Yes. If you use a shared Universal Naming Convention (UNC) to deploy the Excel reports, and you put the new reports in the specific folder that users have access to, they can see the reports. If you deploy the Excel reports to Report Center in SharePoint Server 2007, other users can see the new report if they have security access to the location where the report is deployed.

Q7: Can I redeploy the Excel reports at any time?
A7: Yes. However, if default reports exist, the deployment to the UNC share fails. If existing reports are in SharePoint Server 2007 Reports Library, and you have SharePoint Server 2007 configured for versioning, the reports are deployed and a version is added to the report name. If SharePoint Server Reports Center is configured to overwrite the reports, the existing reports are overwritten.

Q8: If I create a new SmartList favorite in SmartList in Microsoft Dynamics GP, will an Excel report be created automatically?
A8: No. New SmartList favorites are not created automatically as Excel reports. If you want to create an Excel report that has specific columns, begin with the *.odc connection, and then limit the columns that you want in the report.

Q9: Can the Excel reports be deployed by using a mapped drive to a network share, or is a UNC path required?
A9: The Excel reports can be deployed by using a mapped drive or by using a UNC path. However, all workstations must have the same mapped drive for users to be able to see the reports. A UNC path is recommended.

Troubleshooting

Q1: I have deployed the Excel reports and the data connections. But, when I visit Other Reports in Microsoft Dynamics GP, I do not see any reports or any data connections.
A1: This problem may occur for one of the following reasons:

• The paths of Reports Library and of the data connections have not been entered in the Reporting Tools setup. Refer to Question 4 in the "Questions and answers about deployment" section if you deploy the Excel reports and the data connections to SharePoint Server 2007. Refer to Question 2 in the "Questions and answers about deployment" section if you deploy the Excel reports and the data connections to a network share.

• The user does not have permissions to the reports. Refer to Question 3 in the "Questions and answers about security" section if you deploy the Excel reports and the data connections to SharePoint Server 2007. Refer to Question 2 in the "Questions and answers about security" section if you deploy the Excel reports and the data connections to a network share.

• The Office system is not installed on the computer. If you want to view the reports and the data connections, the Office system must be installed on the computer on which Microsoft Dynamics GP is running.

• The reports have not been deployed for that company. You can locate where the reports and the data connections were deployed, and you can verify that the company folder exists. For example, the company folder for Fabrikam, Inc. is TWO.

Q2: When I click the name of the report in Report Center, I receive an error message.
A2: You may receive the following error message:

To work around this problem, click the Excel button next to the report to open the report within the Excel application.
 

Q3: If I click the report in SharePoint Server 2007 Reports Library to open the report in Excel, the report opens in Windows Internet Explorer.
A3: Follow these steps to configure the browser-enabled documents settings for SharePoint Server 2007:

1. Click Reports Library.

2. Click Settings, and then click
   Document Library Settings.

3. In the General Settings area, click
   Advanced Settings.

4. In the Browser-Enabled Documents area, click to select the Open in Client Application check box.
   

5. Click OK.

Q4: When I deploy the Excel reports to a network share, I receive an error message.
A4: You may receive the following error message:

The Microsoft .NET Framework 3.0 is required on the computer that deploys the reports to the network share. When the Excel reports are deployed, the .NET Framework is not required on each client workstation.

Q5: When I deploy the Excel reports to a network share, I receive an error message.
A5: You may receive the following error message

Verify that the UNC path of the network share is valid, and that it is shared correctly. The folder structure that you enter for the path must exist. The deployment does not create any folders.

Q6: When I click the button in Report Center to open the Excel report, I receive an error message.
A6: You may receive the following error message:

To work around this problem, follow these steps:

1. Click Options, click Enable this Content, and then click OK.

2. The Excel report opens. However, the next time that you open an Excel report in Report Center, the same message will appear.

Q7: When I click to view an Excel report in Microsoft Dynamics GP, the report opens in Internet Explorer.
A7: Verify that the .xlsx file in Windows Explorer is associated with Excel.

Q8: When I enter the path of the Data Connections page in SharePoint Server 2007, the deployment option is unavailable.
A8: Verify that the path of the Data Connections page is correct. The path is as follows:

http://servername:port/reports/data connectionsQ9: I have deployed the reports to a network share. When I click to view an Excel report, I receive an error message.
A9: You may receive the following error message:

This error message indicates that the user does not have access to the database objects. To configure database level security for the user, see the "Questions and answers about security" section.