ERP Software Logo1

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process


The Resource Group

Power up your Microsoft Dynamics GP Excel Reports with PowerPivot for Excel

Email | Print

One of the best reporting features of Microsoft Dynamics GP is the out of the box Excel Reports which provide a refreshable data connection to your Dynamics GP data directly from Excel.  This enables users to build out Excel spreadsheets with formatting, formulas, and pivot tables all connected to a live refresh of your Dynamics GP data.  With the release of Excel 2010, Microsoft has made a new tool available called Power Pivot for Excel.  This tool takes the Dynamics GP Excel Reports experience to the next level.   

The top three ways that I instantly found how PowerPivot could help with my Excel Reporting experience:

  1. Managing large data sets – Let’s face it.  Excel does not handle nor was it meant to handle large data sets.  Heck, prior to Excel 2007 the number of rows in a worksheet was limited to 65,536.   Excel 2007 & 2010 increased that limit considerably but the price is performance.   With PowerPivot, large data sets are a snap.  Drop millions of rows from your SQL Server, add a formula, and recalculate in just minutes or seconds.   For Dynamics GP that means you can connect to your GL Transactions Excel Report and create a refreshable connection to your entire GL history in a manageable tool connect to Excel!
  2. Filter your data – Dynamics GP Excel Reports unfortunately does not have an easy way to filter your data prior to download.  You could apply loads of Excel filters on your reports, but it still has to download all of the requested information into Excel first which can be time consuming.  With PowerPivot it’s a snap.  Set data filters on any of the fields in the Excel Report which are applied prior to the load of your data.  Quick Tip:  Only want to report on a specific year of GL Transactions?  Just set a filter and your data refresh time reduces dramatically.  
  3. Connect your data – Once you get your data into Excel from, there are limited ways to connect different data sets.  Sure you have the VLOOKUP and HLOOKUP formulas, which come in handy but they aren’t exactly intuitive.   On the other hand this is what PowerPivot is meant to do.   Pull multiple data sets into Power Pivot from multiple sources and if there is a way to create a link, you can do it using an intuitive click through wizard.   For example, connect to your Dynamics GP Sales Transactions Excel Report and then pull your customer data from your CRM system.  Link them together and produce a more insightful report for your sales organization.  


These are just a few ways I saw how PowerPivot could help right away.  This is a great tool and I encourage you to check out the PowerPivot website to find out more.  There are great examples and free training on how to use the tool. 

To learn more, contact The Resource Group at 425.277.4760 or email

 By Neil Smith, Consultant, The Resource Group – Seattle, Washington Microsoft Dynamics GP Partner

One Response to “Power up your Microsoft Dynamics GP Excel Reports with PowerPivot for Excel”

  1. isaac says:

    please help guide me on how to create a connection between excel and dynamics in order to fecth data into excel for reporting