Microsoft Dynamics Partners providing comparisons and opinions to professionals in the ERP/Accounting software selection process since 2009

 
 

Prophet Business Group

Microsoft Dynamics GP – Refreshable Excel Reports


    Email | Print

    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 a two files 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 adding conditional formatting based on the data sheet in the workbook
    • Most users are already familiar with Excel, thus you don’t need IT 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.

    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.

    Ask This Expert a Question / Leave a Comment