Most of us in the accounting industry are familiar with Microsoft Excel, and use it regularly for various reports. Exporting reports from SmartList, or other reports, then formatting them, only to find that someone just posted a transaction and you have to export the report again, is frustrating. I have found that many Dynamics GP users are not tapping into the power of Excel Refreshable Reports. These reports are easy to deploy, and are a fantastic BI tool just waiting to be utilized.
What is a refreshable report? It’s an Excel file that will automatically update either when refreshed or opened with new data from Dynamics GP. There are many reports that are standard with Dynamics GP, and with the ease of modifying these reports, many different options of reporting are right at your fingertips.
How do you deploy the Excel reports? Use the Reporting Tools Setup (Administration>>Setup>>System>>Reporting Tools Setup) window to deploy Excel reports and data connections to a shared network location or to a Microsoft SharePoint site. During this process, ODC files are created and saved to the designated location. Excel reports and data connections that are stored in the locations that you specify in the Reporting Tools Setup window will also be detailed in the Excel Reports lists. (View the Help documentation for detailed instructions on deploying the reports.)
You can then open the list of available reports on the Navigation Pane for each module. There you will see two types of reports. The Data Connections, and the Reports (you can see this in the Options column). To open one of the reports mark only the report you want to see and click the View button on the ribbon.
Since you are working in Excel, you have options including but not limited to: creating pivot tables, charts, and graphs, changing columns, adding filters, adding conditional formatting, as well as changing the report all together by changing the SQL query it is using.
What are some of the benefits to using Excel refreshable reports?
- Most user are already familiar with Excel, giving your users the ability to create and modify new reports with very little IT support.
- There is no need for users to keep re-running SmartList over and over just to get new data. Data can be automatically updated by opening or refreshing the report.
- SmartList with several lines can take a considerable amount of time to run, whereas an Excel report will open and update much quicker than a SmartList.
- Microsoft 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.
If you would like more information on how to deploy and use the Excel refreshable reports, pleased contact us at 801-436-6636 or at
Article by: Dave Bollard - Head of Marketing | 801-436-6636
JourneyTEAM is an award-winning consulting firm with proven technology and measurable results. They take Microsoft products; Dynamics 365, SharePoint intranet, Office 365, Azure, CRM, GP, NAV, SL, AX, and modify them to work for you. The team has expert level, Microsoft Gold certified consultants that dive deep into the dynamics of your organization and solve complex issues. They have solutions for sales, marketing, productivity, collaboration, analytics, accounting, security and more.