Overview of the ODC (Office Data Connector) for Dynamics GP

The ODC allows you to view Dynamics GP data in an Excel spreadsheet – without needing access to Dynamics GP itself.  This functionality is included with the base licensing of Dynamics GP.  Excel Reports can be deployed to Microsoft Office SharePoint Server (MOSS).  This requires additional licensing.

By default, the data connections that are automatically deployed when you set up the ODC are the pre-defined SmartList favourites.  You have the option of using the default Excel reports as they are or connecting to the pre-defined ODC files from new spreadsheets.  If the report is stored in a MOSS document library, you can choose to open the report in a web browser.

The setup and use of the ODC is very straight forward.  It takes only a few minutes to do the initial deploy.  However, the security to the data needs to be set.  Users need extra privileges in SQL Server before they can view the data.

The deployment of the ODC also means that users can easily create new reports by using the existing data connections.  They simply open a new worksheet, use the Existing Connections link and navigate to the relevant .odc file.  They will get all the available columns in the workbook.  Unwanted columns can be deleted and the file saved.  This is then refreshed whenever the workbook is opened.

Deploying the Reports

For details on how to deploy the reports, see this post.

Security Setup

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 shared 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_."

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

1. Create a Windows logon for the user or group for whom you want to set up security.  For example, create a Windows logon for domainalias or for the group name.
2. 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.

Excel Reports that correspond to the SQL Server database roles

To finalise the deployment, you need to know which reports relate to which roles.  This will be important when planning.  This document will give you all the details you need for deciding which roles to assign to which people.

Heather Roggeveen is a MS Dynamics GP Consultant with Olympic Software. After 15 years of working with the end user all the way from designing the solution to user training, she has become a Dynamics GP expert. Heather regularly shares her knowledge, including tips and tricks for end users in her blog articles. Follow her on Twitter @HRoggeveen to be notified of her latest articles. You can also like Olympic Software on Facebook or follow us on LinkedIn or on Twitter @OlympicSoftware. For more information about Dynamics GP and how it could benefit your business, view the Dynamics GP page on our website or give us a call, 09-357 0022.