How to Modify an Office Data Connection in Dynamics GP
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.
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.
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.
Click the "Properties" button, then click the "Definition" tab.
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.
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.
The good news is the new file will automatically appear in the Excel Reports list.
Now you can create new Excel Reports using the modified or new ODC; you also can simply select it from within Excel.
Good luck! If you have any questions, contact us now!