Weekly Dynamic: Portable Excel Reports, the Best of Both Worlds Part 1

With Convergence 2010 going on this seemed an appropriate time to tackle a really technical post.

• Dynamics GP provides refreshable, Excel reports based on the native SQL connection. They use a portable data connector making them easy to share in the organization. To limit records you have to use filters or change the underlying SQL.
• Excel provides an MS Query tool that allows Crystal Reports style reporting in Excel. It uses a wizard to pick the tables AND allows user changeable parameters within the report. When a user changes a cell, the report updates with the latest data. These are based on ODBC and not very portable making them hard to share.
• Excel 2010 allows you to create an MS Query based report in Excel with parameters. Once you are done, you can save the connection to a file making it possible to have portable reports with user controllable parameters.

As an Example:

• Open Excel and type Date in cell A1 then type the date 4/12/17 in cell A2. This will be a parameter we use to control the report.
• In Excel and pick Data | From Other Sources | From MS Query
• Pick Dynamics GP as the source and check the box to use a trusted connection
• Find the GL20000 and use the double arrows to add all the fields

• Click Next until you get to the finish screen and choose to View data or Edit in Microsoft Query
• Click View | Criteria
• In the Criteria Field put TRXDATE and in Value put [GLDATE] with the brackets. This is the name of the parameter.

• Excel will ask for a value for the parameter. If using Fabrikam, put in 4/12/17
• Click the little door icon to send this to Excel.
• GO SLOW HERE
• When asked, set the data location to cell A5 in the same worksheet as your date info at the top.

• Click Properties | Definition | Parameters
• Check the box to get the value from a cell. Pick cell B2 and check the box to automatically refresh.

• Click OK all the way through to put data in Excel.
• Change the date in cell B2 hit tab. The data will refresh in Excel.

Save this file and we have a refreshable, live Excel report with parameters to control the data. If you already have SQL code, you can skip through the wizard and paste in your code. Be aware that parameters won’t always work with complex code so you may have to roll everything into a view to simplify it.

Now that we have this, next week we will look at making this file portable.