Things I've learned from years of working with Microsoft Dynamics GP.
Using Microsoft Excel to create a Dynamics GP auto refreshing report (Video)
Last week, a Microsoft Dynamics GP User (Glenda) asked about making an Excel Refreshable Report refreshable. She not only wanted the table to refresh, but she wanted the associated PivotTable to refresh as well.
Last week, a Microsoft Dynamics GP User (Glenda) asked about making an Excel Refreshable Report refreshable. She not only wanted the table to refresh, but she wanted the associated PivotTable to refresh as well.
After some research, she found that she could attach some VBA code to the Excel file to make the PivotTable auto refresh. The problem being, Microsoft Dynamics GP doesn’t recognize Macro Enabled Excel worksheets.
That attached video shows how to use Query Editor in Get & Transform (formerly Power Query) to achieve Glenda’s desired results.
It’s about a 12-minute video, but I walk users through connecting to data, modeling the data, storing the file so it’s accessible within GP and making it refresh upon opening.
Hope this helps!
Note: If using Excel 2013, download Power Query HERE!
Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 24 years.
Belinda began implementing ERP systems so long ago that Windows was not an operating system, but an application. Larger businesses used old Main Frames with monitors that were Green and Black and smaller business did accounting by hand. Having seen the evolution that has taken place over the years from pieces of paper to analytics, Belinda still gets excited every time she helps a business improve.
Belinda is also well known for her Blog www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.”
In her time off, Belinda enjoys sailing, woodturning, crochet/knitting, sewing/quilting, reading and playing the ukulele (Belinda often participates in Ukulele Jams in NYC.)
View all posts by Belinda Allen
Belinda Allen, Microsoft MVP Business Solutions 