If you’re using SmartLists on a regular basis to report on information in Dynamics GP, you’ll want to learn how to use the Export Solutions functionality within SmartLists to add charts, pivot tables, calculated fields, and additional formatting to your data.
Export Solutions allows you to run an Excel spreadsheet macro on the data that SmartLists exports to Excel.
These are the basic steps to set up the macro:
- Export a SmartList to Excel
- Start and name a new macro in Excel
- Perform the formatting you want in the Excel spreadsheet
- Stop the macro
- Delete any worksheets you may have added during formatting
- Delete the data that was initially exported to Excel
- Save the Excel spreadsheet as a template
These are the steps to attach the spreadsheet with macro to SmartLists:
- Navigate from SmartList: SmartList >> Export Solutions
- Document: Select the Excel spreadsheet that contains the macro you wish to execute
- Works for Favorites: Select the SmartList(s) you want the Export Solution to be available with.
Once the Export Solution has been attached to the SmartList(s), you can use it when you wish. Just make sure to select it from the menu drop down, and not “Quick Export”:
I’ve created a companion video to this on YouTube.