Did you know that there are built in Excel Reports in Microsoft Dynamics for all the modules: Sales, Financial, Purchasing Inventory and more.

You have the option to open each as either a report or a data connection.  The best thing about these reports is that you set them to automatically refresh on a time schedule. So if a change is made in Dynamics GP, your Excel report will be updated.

Normally, data that you export to an Excel file is just a snapshot in time and quickly out of date. But with a live refreshable Excel report from Microsoft Dynamics GP you and your team always have current information.

And once a report is in Excel there are all kinds of fun things you can do with it – set up pivot tables, charts, time slice….let your imagination run wild. (Or take some online Excel training to get some new ideas).

 

Video Transcription:

This is Amber Bell from CAL Business Solutions.  Today’s topic is, “Working With Live Excel Reports from Microsoft Dynamics GP.”

To access the Excel report, you would log into GP, and you can find these reports for all of the modules of GP:  Sales, Financial, Purchasing, Inventory and more

So as you see, I logged into GP under Sales. And under Sales in my navigation pane, there’s an option that says Excel Reports.  Now, over on the side, you’ll see it refreshed, and I have all different reports.  I’m going to go ahead and scroll over here.  You can see there is a lot of built-in reports.  Now, if I click on one of these reports, I have an option to open it as a report or a data connection.

Both have the same information.  The difference is the report will open with automatically being named, and it’s in the same location that it’s set up.  It’s down here in that location.  Whereas the data connection, you could save as any name you want.  It opens just in the Excel Book 1 or whatever newest book you have.  We’re going to go ahead and open one of these as a report, and then we’ll open another as a data connection just to show the difference.

So as I click on this, you’ll see that it opens in Excel.  Now, based on my security, it automatically pulled that information, and I can see here are all of my customers, and I can see all of their balances.

What works really nicely with these reports is that if I get another payment from those customers, I can simply go into data and refresh, and the information will refresh.  I can also go under Connections, Properties and set this to automatically refresh.  Maybe I want it to refresh every 15 minutes, or maybe I want to refresh every 5 minutes.  All I do is make that change and then close, and when I save it, it’ll save that change.

Now, that’s a very simple way to view what’s going on in the system.  If we go back into GP, you can run some really nice reports.  I can come down in here and say I want to look at receivables transactions by customer.  So we’re gonna do a data connection for that one and hit enable.  Now because it’s a data connection and because I have a little more data, I have some extra options.

You will see what this did automatically in Excel is it went to the design and I can summarize with the pivot table.  If you don’t jump there automatically, you can simply hit insert if you don’t see the design and just hit pivot table.  It’s going to select the whole data, and you can drag and drop some fields into these sidebars. So watch if I click, pull in customer name, I can pull sales amount in, and then what I might want to do is see how much of their amount is per document type, so I simply pull that into rows and pull that into columns.  And I can come in here and put in some extra options that come from Excel.

I have the newest version of Excel.  I’m running 2013 for Excel, so I actually have a built-in time slicer.  I can do all kinds of really cool reports.  So that’s a very, very basic way to pull in information, but as you can see, if I want to, I can refresh the data and pull that information outside of GP.

If you have not had those implemented or turned on in your system, you can have your system administrator or your partner go under Administration, and then under Setup, there will be an option for setting up reports.  So we’ll go ahead and go into the Reporting Tool Setup, Excel Reports.  You put the locations.  In your case, it would probably be a server location, and then they would choose the companies to deploy and hit deploy reports.  The most complicated part in getting those working properly is your security in SQL, and you can contact our office to help you with that.

As you can see, as I click through the different modules of GP, there are Excel reports for financial.  I can get my account transactions information there.  There are Excel reports for my inventory, which is a really nice Excel report because you can give people access to see quantities on hand that maybe don’t have access to GP.  Simply click on there, enable.  Go ahead and click okay, and now we’re going to see our items with their quantities.  So there’s a lot of information that you can pull right from inside of GP and run different reporting.

For more great Dynamics GP tips, visit www.calszone.com/tips and sign up for our e‑newsletter.

By Amber Bell, CAL Business Solutions , www.calszone.com