MS Excel files in GP v10 - refreshing indeed

Let me offer my friend & the host here, Mark Polino, a ‘hat tip’ on the front end of this blog posting instead of at the end as I did last month during my inaugural post on this GP site of his. Recently, he was kind enough to invite me to think out loud on these pages, so I'm taking him up on his offer again. Being a periodic guest blogger here is quite flattering to say the least, so thanks again, Mark.

When I have the opportunity to discuss all things GP with GPUG members or others I don’t officially call customers, I like to inquire about industry, current code version, likes, dislikes (if any), etc. Invariably, if the version response is ‘v10’, I ask about the usage of the refreshable Excel sheets that debuted with this code release…and sometimes, I get a blank stare.

In case you hadn’t heard by now, the GP v10 code release contains lots of new features, not the least of which are the nearly 200 refreshable, real-time MS Excel worksheets – and if you’re someone who works in a GP shop and you’re constantly receiving requests for back office data from those outside the department who only wished they had a GP User ID at their fingertips, then this post is for you! Real-time data is no longer just a phone call or email away, and enabling these secured files, regardless of the number of entities, is nearly effortless.

From the ‘Administration’ area page (with system password credentials, of course), simply find the Reporting Tools Setup window. Once here, real-time Excel workbooks and their Data Connection partners are two tabs away from full deployment (the first tab listed, ‘SQL Reporting Services’, is not applicable in this particular case). The 2nd of 3 tabs here, labeled ‘Data Connections’, deploys the *odc files that retrieve real-time data from your database(s) – run these first, please. The 3rd tab, labeled ‘Reports Library’, deploys the Excel files themselves, and should be run after the data connections. Both tabs offer system level or user level deployment (the former obviously reaches out to others much better), and formal deployment of either is as easy as checking the ‘enable’ checkbox near the bottom just prior to punching the ‘Run Deployment’ button. Deployment of these files can be to someplace fancy like Microsoft Office SharePoint Server 2007 or as simple as a network share. There’s even a checkbox on both tabs to deploy all out-of-the-box files for all companies simultaneously, and if you’re managing numerous entities, this is a nice time saver to say the least. Honestly, deployment of these files is just that quick.

When SmartList users open the various folders within, they find beaucoup, pre-defined queries that loaded with the application, but only those with GP clients can leverage SmartList, which leaves a lot of others in an organization out in the virtual cold. With GP v10 however, refreshable MS Excel workbooks mirror these same built-in SmartList queries, which means those without a GP client can query data easily now, too.

If your organization is licensed for SmartList Builder, you have even more reasons to enable these refreshable Excel files:

*Data Connections can be utilized within SmartList Builder and Excel Report Builder just like a native GP table or SQL Server object.

**Excel Report Builder, a free add-on to SmartList Builder if you’re running GP v10 SP2 or higher, allows you to supplement the out-of-the-box refreshable Excel sheets you’ve (hopefully) deploying soon just as SmartList Builder helps to expand your usage of SmartList – especially with 3rd party modules.

***GP 2010 is bringing even more ‘builder’ tools to the table as Navigation List Builder and Drill Down Builder are added to the mix, the latter of which will take refreshable Excel files to the next level with pop-up windows that help reveal record details! Personally, I’m anxious to get my arms around this new functionality.

So, if you haven’t rolled these out-of-the-box, no assembly required, refreshable MS Excel sheets out for your colleagues yet, what are you waiting for?! In the time it took to read & digest this blog posting, you could’ve had them deployed by now...but I’m glad you stuck around to the end.

Good luck with your deployment - not that you’ll need any!


Bob McAdam is a Manager at Tribridge in Tampa, spending the majority of his time working in the GP delivery area with new & existing customers. He is also an active contributor to the Dynamics GP User Group (GPUG). Bob has been fortunate enough to be involved with Dynamics GP as both partner & customer since 1998. Thanks again to friend & former colleague Mark Polino for all his blogging encouragement, which has obviously generated some interest on these pages this year.