Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

SmartList Export Solutions

While SmartList exports are great for sending Dynamics GP data to Excel for analysis, they aren't an ideal solution for a dashboard. SmartList sends data to a new Excel file each time. It's a lot of work to export data and rebuild a dashboard every month. An improved option is to use a SmartList Export Solution.

SmartList Export Solutions let you export GP data to a saved Excel workbook. They also provide the option to run an Excel macro before and/or after the data populates in Excel. As an example, we will format the header automatically after exporting financial summary information.

Getting ready

We have a little setup work to do for this one first. Since these exports are typically repetitive, the setup is worth the effort. Here is how it's done:

  1. Select the Microsoft Dynamics GP menu from the top and click on SmartList.
  2. Select Financial | Account Summary on the left to generate a SmartList.
  3. Click on the Excel button to send the SmartList to Excel.
  4. Next, we need to turn on the Developer Ribbon in Excel:
    • In Excel 2013, select File | Options | Customize Ribbon
    • Check the box next to Developer on the right-hand side
    • Click OK
    Getting ready

Creating macros

A SmartList Export Solution allows you to run an Excel macro before or after the data arrives to format or manipulate the information so you only have to do it once. Let's record our Excel macro.

  1. Click on the Developer tab and select Record Macro. Accept the default name of Macro1 and click OK.
    Creating macros
  2. In Excel 2013, highlight Rows 1-5, right-click, and select Insert.
  3. Bold the titles in cells A6-F6 by highlighting them and clicking the B icon on the Home ribbon.
  4. In cell A1 enter Sample Excel Solution.
  5. From the Developer tab, select Stop Recording.
  6. Highlight and delete all the rows.
  7. Save the blank file containing just the macro on the C: with the name as AccountSummary.xlsm.

Creating an export solution

Now that we've prepared our Excel 2013 workbook to receive a SmartList, we need to set up and run the SmartList Export Solution:

  1. In Dynamics GP, select Microsoft Dynamics GP and then select SmartList.
  2. Select Financial | Account Summary in the left pane to generate a SmartList.
  3. Click on Favorites. Name the favorite Export Solution and click Add | Add Favorite. The favorite can be named anything. I'm using Export Solution for our example:
    Creating an export solution
  4. Back on the SmartList window, select SmartList | Export Solutions. Name the solution as Export Solution. Set the path to C:\AccountSummary.xlsm and the completion macro to Macro1.

    Tip

    There is a length limit of eighty (80) characters for the document name and path. This can be a little on the short side, so it can be difficult to point an export solution to a file deep in a network file tree.

  5. Check the box next to the SmartList favorite under Account Summary named Export Solution:
    Creating an export solution
  6. Click Save and close the window.
  7. Back in the SmartList window, select the Export Solution favorite under Account Summary and click on the Excel button.
  8. Instead of immediately opening Excel, there are now two options. The Quick Export option performs a typical Excel export. We want the second option. Click on the Export Solutions option. This will open the Excel file named AccountSummary.xlsm, export the data, and run the macro named Macro1, all with one click.
    Creating an export solution
  9. Click on the Export Solution option and watch the file open and the macro execute:
    Creating an export solution

Navigation List export

Dynamics GP includes a feature called Navigation Lists. These lists provide centralized information views and access to tasks around common areas. For example, the Account Transactions List includes the ability to review journal entries, drill back to additional information, and enter transactions, all from a single screen.

Navigation Lists don't have a large Excel button like SmartLists, but they do include the ability to export to Excel.

To export a Navigation List to Excel:

  1. Select Financial from the Navigation Pane.
  2. At the top of the Navigation Pane, select Accounts to open up the Accounts list.
  3. Check the white box in the header next to Account Number to select all the accounts.
    Navigation List export
  4. On the ribbon at the top, select Go To | Send to Excel.

    Tip

    Like the Dex.ini switch that can improve exports to Excel from SmartLists, there is a similar, unsupported switch to speed Navigation List exports to Excel. To activate this switch, add the following line to the Dex.ini file and restart Dynamics GP 2013. The same caveats apply; test this on a test server first.

    ListsFastExcelExport=TRUE
本周热推：
Hands-On Kubernetes on AzureLearning AWS IoTLearning Einstein AnalyticsGetting Started with SimulinkExcel 2013在会计与财务管理日常工作中的应用
上一章目录下一章