RSMUS.comThe typical scenario goes something like this: The auditors have requested that you give them an excel file that contains all the journal entries for the previous year. You try to use SmartList to export the records, but SmartList chokes on the volume of the data.
Here are your options:
- Use SmartView
- SQL Script – query results to file
- SQL Server Import and Export Wizard
- SmartConnect export to Microsoft Excel
Let’s assume you don’t own SmartView so option 1 is out of the question. Options 2 and 3 require SQL scripting skills. You could ask your RSM consultant to extract the data in SQL Server Management Studio, but that would take time and money.
However, if you own SmartConnect, you have a great solution at your fingertips. With SmartConnect you can create an export map that you can use over and over again to extract large volumes of data to Excel, or other file formats. Here’s how:
Figure 1
Figure 1 shows an example of the source setup.
The Data Source is set to ODBC Connection and the Connection String references the DSN you already have setup for Dynamics GP.
This query is a modified version of a query originally found on victoriayudin.com. I modified it to allow you to set a start date and an end date. I would be happy to provide this script to you if you contact me at dave.funk@rsmus.com.
Figure 2
Figure 2 shows the Destination setup.
The Destination type is Export to file and the File Type is EXCEL 2013. Other versions of Excel are available in the drop down, as are other file formats such as comma delimited and tab delimited. The field mapping (not shown) is a simple one to one map of all of the fields in the source query.
Finally, Figure 3 shows a snippet of the Excel output file that results from running the SmartConnect Map.
Figure 3
Note: a SmartConnect to Excel map is still bound by the limits of Excel. Those limits for Excel 2013 and Excel 2016 are 1,048,576 rows and 16,384 columns. If you hit these limits you will still have to split your data in to quarterly or monthly files.
Now you have a SmartConnect map that can be used over and over again when the auditor’s request the data. SmartConnect is a powerful tool that puts data integration, data extraction and data conversion in reach of the non-technical financial executives, accountants and analysts.
To learn more about how you can take advantage of this and other Dynamics GP features, visit RSM’s Microsoft Dynamics GP resource. To make sure you stay up to date with the Microsoft Dynamics Community, subscribe to our Microsoft Dynamics Community Newsletter.
For more information on Microsoft Dynamics 365, contact us.
By: David Funk
