How to Utilize Refreshable Excel Reports
We all know the number one accounting tool is Excel. When trying to analyze GP data most users will export data from a Smartlist in GP and then slice and dice it to get it in the format they need. This can be a time-consuming process month after month.
Did you know there has been a solution to this issue since version 10 of Dynamics GP? It’s called Refreshable Excel Reports and is an Excel file that will automatically update when refreshed or opened with new data from Dynamics GP.
Excel reports have been deployed for most of you already. When deployed there two files are generated, the ODC file and the actual Excel report. In most environments, they can be found under GPShare\Reports\Reports\Company\Module. You will then see reports broken out by module, just like Smartlist in GP.
The main advantages when using these Excel reports include:
- In Excel you can create pivot tables, charts, graphs, slicers, and add conditional formatting based on the datasheet in the workbook
- Most users are already familiar with Excel, thus you don’t need IT to support
- Automatically updated when opening or refreshing the report
- SmartList with a lot of lines can take a considerable amount of time to run, whereas an Excel report will open and update much quicker than a Smartlist
- Excel Reports do not use a Dynamics GP user to view the reports, so you can send reports to anyone in your company who has the required SQL credentials to view the report
Some of the great things you can do with Excel reports is create pivot tables of the data and then add slicers to the report, so you can select just the data you want.
You can take the output of the basic Smartlist below and then turn it into a dashboard that you can use over and over.
Cut Down on Repetitive Tasks
You can also create your own Smartlist designer reports off a SQL view and then you can publish the report. Once published you will be able to open the report in Excel.
If you have Smartlist builder from Eone Solutions you can use Excel report builder, which allows for more functionality than Smartlist designer and also allows you to publish custom Smartlist and Excel reports.
You can also create your own ODC file using your own SQL query if you only want certain columns or want to combine multiple tables in a query.
Excel refreshable reports can be a very valuable tool that you can use in your accounting toolbox to help you analyze your GP data and cut down on the repetitive tasks that you are doing month after month.
Questions?
If you would like to get started or have questions, contact the GP Support Team and take advantage of one of the great tools you already have.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.