Reporting on any Dynamics GP data with direct Excel connections

In an earlier recipe, we looked at deploying and using the Excel reports contained in Dynamics GP. For all of the power of those dynamic reports, one thing is missing, the ability to modify the type of data being returned from within Excel. Excel reports allow filtering. However, if a user only needs a subset of data, using filters can make it difficult to work with only the filtered data. Also, Excel reports bring in all of the available rows, creating a much larger data set to work with and possibly overwhelming Excel.

Fortunately, there is another option. The MS Query tool included with Excel can work with Open Database Connectivity (ODBC) to connect to live data in Dynamics GP. This process is as fast as Excel reports, allows user-changeable parameters from Excel, and can be refreshed just like Excel reports. However, there are no pre-built reports that use ODBC connections, so users have to build these from scratch.

To demonstrate...