I've always had a great reaction when demoing the new Dynamics GP10.0 Microsoft Excel reports and Office Data Connections (ODC). However this often leads to many questions such as how do they work so I wanted to add some clarity to this.

Dynamics GP 10.0 and Microsoft Office provide an integration method called an Office Data Connection that allows us to view Dynamics GP data in Excel worksheets. When the data connections are deployed, ODC files are created and saved to a server location. Each ODC file links to a company or system database and this ensures that the most recent data is displayed whenever an ODC file is opened.

Each ODC file corresponds to a SmartList favourite so the ODC file called Customers Customer Balance contains the same data as the Customer Balance SmartList object.

You can also deploy Excel reports to a shared network location or if you have Office SharePoint Server 2007 in your enterprise then they can be reports library. Each Excel report connects to a single ODC file to display data therefore the Excel report called Customers Customer Balance connects to the ODC file Customer Customer Balance. Simple really!

To open these reports you need to have access rights within SQL Server to view data as well as access to the folder share (if you're using shared network drives) or access to the SharePoint site.

Some of you may now be thinking OK that's seems pretty good but why do we want a different way to access SmartList data....and there are a couple of reasons that jump out at me as to how and why this is useful.

Let me put in context. In the old days we would create a SmartList, download it to Excel, muck around with the data maybe add a couple of calculations to the data, reformat it and then save it to a folder share and email it to someone who doesn't have access to Dynamics GP. We now have a couple of issues. Firstly there is more than one copy of the data in the enterprise and secondly the data is already out of date. 

Now in the new world the process now would involve giving the non GP user access to the Excel report. This means that they can then run it whenever they want and see the latest data removing the reliance from Dynamics GP users to produce reports for other people. If they want to change to report - add some calculations, add a chart perhaps and some formatting, they can do this but they only need to do this once and not everytime they receive the data. The formatted report can then be saved and the ODC is stored with it. This means that whenever they want to view their customised Excel report based on current GP data they just have to open the Excel file up from the shared location and hey presto a customised, formatted spreadsheet looking at live data! Extrapolating this further, other data sources can be combined on the one spreadsheet and we can start to build up dashboard views of our Dynamics GP data, combining SmartLists alongside our Dynamics GP Analysis Cubes for example.

This new method of delivering business intelligence means there is less reliance on Dynamics GP users to keep producing reports for others which gives users back time and increasing personal productivity, and non GP users can now access data that previously they have been relying on other people to provide allowing greater insight into the data that they want and also they have access to it whenever they want.

Can you see the interest in the reports now? If not speak to someone who has to produce these reports for other users in your organistions....

Posted by Tom Brookes