The room was packed. Nearly standing room only. Another hot topic for a hot, August day. SmartList in Microsoft Dynamics GP is like Excel in that it can be incredibly powerful, but is rarely utilized to its full potential. In the lunch and learn, we discussed the uses and provided step-by-step instruction for SmartList and Excel Report Builder. This content was so rich, we were only able to briefly touch Business Analyzer. Below, I cover some of the highlights from the meeting.
A SmartList is a flat file data dump of the information we need from the SQL tables. SmartLists can show all records in the system depending on the object selected: Posted, Unposted, Open, History, Active, Inactive and Temporary. You can use SmartLists to:
- Create customer, vendor and employee labels
- Reconcile receivables and payables to the general ledger (GL)
- View GL transactions
- Check the deposits posted to a checkbook for the current day
- Verify item quantities in inventory
- Report on which items are sold by a salesperson
- Create a simple, two column chart of accounts list
Trishia Hogue, the presenter and an ERP Project Manager, demonstrated several examples of how to use SmartLists. You can see some of these examples in the presentation. One of the examples she walked through was creating a SmartList for a Sales Manager. In her example, the Sales Manager wanted to see a list of the Chicago customers’ life-to-date sales with their salesperson. The manager also wanted this list to appear with the best customer first. Below are the steps Trishia took to create this list using SmartList.
- Open the SmartList window (Microsoft Dynamics GP > SmartList).
- Expand the Sales folder and select the default SmartList view for customers.
- Click the Columns button, and then add the Salesperson ID and Total Sales LTD fields.
- Click Search and add the restriction City = Chicago. Click OK.
- To put the best customer first, sort the list by descending Life-To-Date Sales by clicking twice on the Total Sales LTD field title.
- Save the SmartList by clicking the Favorites button on the menu bar, giving the SmartList a name, and clicking the Add button.
Excel Report Builder
Excel Report Builder Functionality
Excel Report Builder is part of the SmartList Builder Series. It was built and is managed by an ISV called eOne. You can use Excel Report Builder to:
- Drill back to GP from within Excel (Everyone was excited about this one. One lady said Trishia was her hero!)
- Establish permissions via Active Directory (AD) rather than GP login
- Create SQL View for each report
- Create worksheets for each company
- Preview of data
- View refreshable Data
Excel Report Builder How-To
You can create an Excel Report out of GP using Excel Report Builder by performing the following steps:
- Open the Excel Report Builder Window (Microsoft Dynamics > Tools > SmartList Builder > Excel Report Builder)
- Define the Report ID (Keep the name without spaces)
- Define Report Name
- Add tables
- Select Fields to Display
- Change the value types of fields
- Set View Name (Be sure to put a vw or other easy identifier here)
- Publish the report to a network share, SharePoint, or on your local computer - remember you are able to drill back to GP from Excel
For those of you that were not able to attend our August Target Return, the presentation can be found at this link for your reference. You can also access additional SmartList information in my other SmartList blogs: SmartLists and Reminders: Bringing Your Data to You and SmartList Designer and SmartList Builder: Bringing Your Data to You (Part II)