Excel refreshable reports via SmartList Designer

Since the release of the original version of this book, which focused on Dynamics GP 2013, SmartList has received a few updates. The Designer, which is now part of SmartList, allows for the creation of Excel refreshable reports based on custom SmartList objects that were originated in the Designer. The Designer can easily create both a SmartList object and an Excel refreshable report using a SQL view (which we discussed in Chapter 1, Getting Data from Dynamics GP 2016 to Excel 2016.) Let's create a new SmartList object and a new Excel refreshable report now.

Create a new SmartList object

Let's create a new SmartList object that will display the bank accounts and their balances, from the Bank Reconciliation module. Microsoft has a view already built to do this very thing, named CM00500, which is stored in each company's SQL database. Let's take a look at the following steps to create a new object:

1. Open SmartList as you would to pull up accounts.
2. On the Actions part of the SmartList ribbon, select New:
   
3. In the List Name, enter Bank Balance and in Series, select Financial from the drop-down list.
   
4. On the left Database View pane, scroll down and click on the plus (+) Views | plus (+) Company. All the views for the database for the company for which you are currently logged in to GP will be displayed.
5. Scroll through the views for Company, find CM00500 and click on the plus (+) display the fields (columns) in that view.
6. Select the fields CHEKBKID, DSCRIPTN, CURNCYID, INACTIVE, and CURRBLNC. The fields will then appear in the Selected Fields pane:
   
7. Click on Execute Query on the ribbon, and you'll see the data displayed in Result's Preview:
   
8. If the results look like what you expect, then click on the OK button on the ribbon. This will create the new SmartList object.
9. Since we put this new object in the Financial Series, you'll now see a new folder called Bank Balance that holds our new object:
   

Tip

You've been given the non-Excel gift of "How to create a new SmartList object in SmartList Designer." And yes, you can use these steps to create other SmartList objects, regardless of whether or not you create an Excel refreshable report from them.

Publish to Excel

Now that we've created our object, let's create the Excel refreshable report using these steps:

1. Open SmartList and highlight the new object we just created.
2. On the SmartList Ribbon, click on Publish:
   
3. You'll receive confirmation that your Excel report was created:
   
4. Go to the Excel Reports for Financials, that is, go to Financials | Excel Reports. Our new report is now in the list and can be used:
   
5. Double-click on the Excel report version and your bank balances will appear in Excel: