Among the various reporting options from
SmartLists is a standard querying tool for Microsoft Dynamics GP which allows end-users to instantly create sophisticated queries to find detailed information about customers, vendors, inventory items, and employee records, as well as general ledger accounts.
Excel Reports provides more than 220 built-in, refreshable reports which let business users work in the familiar Microsoft Office user interface. Even though Excel Reports comes standard with Microsoft GP 10 and higher many end-users are not aware of its existence or just do not use it. Also, a user does not need to be logged into Dynamics GP, or even have access to Dynamics GP, to be able to view real-time data for his company.
Let’s say you are working in Microsoft Dynamics GP and decide to create a Smart List. You click on one of your favorites and in a matter of seconds you get the first 1000 records. You think to yourself this is pretty cool. You then decide to increase the record count, because you have a large amount of data in your system and 1000 records does not give you the detail you need. So you increase the record count to 5000 records and run the query again. The query starts chugging away returning some of your data pretty quickly. But then all of a sudden the results start slowing down and eventually you get the data you want (but it takes a few minutes to get the data). You think to yourself, this is still pretty cool, even though it took longer to get the results. Having the results in the Smart List view, you decide to export the data to Excel. You click on the Excel button and then proceed to watch your screen as the records start exporting. You watch and wait and then watch and wait some more as the records count down from 5000 to zero. Eventually the records all appear in Excel. You now think to yourself, Smart Lists may be cool but I'm not sure how useful it will be since it takes so long to get the records into Excel. Further you think, if it took this long for 5000 records, how long would it take for 10000 or maybe 50000 records. Then you start wondering – if the data can get queried on and eventually exported to Excel, why can’t the Dynamics GP data be extracted directly into Excel.
The answer is – It Can.
By default, Excel Reports are created based on existing SmartList favorites. These reports will reflect up to date data from Dynamics GP when generated. Essentially, the data you would query on and then export to Excel using a Smart List can be automatically exported directly to Excel. The big kicker is that these Excel Reports will return the extracted data in a matter of Seconds. That’s right – seconds, not minutes or hours.
I recently had a client who needed a list of the checks that had been generated during their fiscal year. The list had to be in Excel so they created a Smart List which had over 70000 records in it. It took about 10 minutes to generate the Smart List results. But then it took another hour to export the results to Excel. They suffered through the ordeal figuring it was a once a year request and they could deal with the lengthy time. Until, they found out that they had generated the Smart List before the last check run of the period they needed. So now they had to redo the entire process again. Not wanting to have to waste another hour or more on this request, my client reached out to me to find out if there was any other way to get the needed results. I mentioned to the client about using the Excel Report Integration. I created an Excel Report for my client and they were able to get the needed results in a few seconds. They also had a new report which could be used in the future for similar requests.
The number of Excel Reports available depends on the modules you have registered. But essentially for any Smart List you have available, you will also have an Excel Report available. Aside from the default Excel Reports (equivalent to the default Smart List favorites), you also will have Excel Reports where you can view all the columns that can be in the report (equivalent to viewing the Columns in a Smart List favorite). You can then keep only the columns needed and save the report in Excel.
Deploying Excel Reports is very easy. There are some security settings to be taken into consideration when implementing Excel Reports. Once set up, you will be able to immediately utilize the standard reports as another method of extracting data out of Dynamics GP.
So in the case of reporting with Microsoft Dynamics GP, the answer is yes, you can. From the built-in, standard reports to the advanced reports created by a
By: Jeffrey Bloch, RSM McGladrey, Inc., Microsoft Dynamics Partner - NY Metro