How to Modify an Office Data Connection in Dynamics GP

Thoughtware Article Published: May 29, 2012
Person coding

One of the great areas of functionality of Dynamics GP is Excel Reporting. If you’re not familiar with this functionality, Excel Reporting is a way to view Dynamics GP data using Excel that is live and refreshable—imagine viewing sales transactions and being able to click a button to refresh the data.

In Dynamics GP 2010 R2, click on a navigation button and then the Excel Reports list to see the deployed Excel Reports.

Dynamics GP 2010 R2 - Ex 1

In the above image, notice the circled selection that says “Microsoft Office Data Connection.” When you deploy Excel Reports, Dynamics GP creates two files. One is an Excel file with an .xlsx extension. The other is an Office Data Connection file with an ODC connection. ODC files are used by Excel to connect to the GP data.

ODC is a great technology, and the fact that Microsoft supplies many ODC files is incredibly convenient.

However, sometimes what’s off the shelf is not enough. What if the ODC file doesn’t have everything you want? In reality, the ODC files are really just SQL queries. Can you modify these ODC files? You bet! Here’s one way to do it.

Open Excel and a blank sheet. Then click the "Data" menu and the "Existing Connections" button.

Dynamics GP 2010 R2 - Ex 2

Find the ODC file—check Tools | Setup | System | Reporting Tools Setup for the path—you want to modify.

You should get an "Import Data" window.

Dynamics GP 2010 R2 - Ex 3

Click the "Properties" button, then click the "Definition" tab.

Dynamics GP 2010 R2 - Ex 4

You can click right in the "Command Text" field and modify the query. Prior to this step, you probably want to back up the original ODC file. In this case, I’m adding the "Vendor Name" column to the query.

Dynamics GP 2010 R2 - Ex 5

Click "Export Connection File" to export your modified ODC to a file. You can name it the same as the original or create a new ODC file.

Dynamics GP 2010 R2 - Ex 6

The good news is the new file will automatically appear in the Excel Reports list.

Dynamics GP 2010 R2 - Ex 7

Now you can create new Excel Reports using the modified or new ODC; you also can simply select it from within Excel.

Dynamics GP 2010 R2 - Ex 8

Good luck!  If you have any questions, contact us now!

Related Thoughtware

Kate & Ben — How can we help you? Contact Us!

How can we help you?