Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6300 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

Title:Office Data Connections and Excel Reports

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



Category:OFFICE INTEGRATION: Excel with GP
Link Owner:
Date Added:June 17, 2010 06:10:17 PM
Number Hits:11
RatingsAverage rating: (0 votes)

No Reviews Yet.


Thank you for your support for GPWindow. It helps us cover part of the hosting costs for GPWindow.