Drill Down to Microsoft Dynamics GP windows from Excel

  • Article

Did you know that you can drill down to a Microsoft Dynamics GP window from a Microsoft Dynamics GP Excel report to see more information on that data?

Microsoft Dynamics GP 2010 has a new feature called Drill Downs.  This is somewhat similar to the Office SmartTags product that was available in previous releases.  This functionality allows you to create drill downs from an Excel Report, SSRS report, etc. that will open a Microsoft Dynamics GP window for you with the data you are drilling back on populated. By default, there are many of them setup, but you can also create your own if you have the Drill Down Builder product that is available in the package with SmartList Builder and Drill Down Builder.

The drill downs aren't in place by default in the Excel Report files, but are in the Data Connections that those reports use so that you can pull them into the Excel Reports.

Let's take a look at how to get one added to an Excel Report. 

Note:  For this example, I am going to work with the Accounts Excel Report that is published when you publish the Microsoft Dynamics GP default Excel reports and will be using the Fabrikam, Inc. company

Browse out to where you have published the Excel Reports to.  This could be a shared network location or a SharePoint site.

  1. Open the Data Connections folder/library.
  2. Open the TWO folder and then open the Financial Folder.
  3. So that we don't accidentally make any changes to the default file, make a copy of the "TWO Accounts.odc" file.
  4. Open the copy of the file you made in step 4.  It should open in Excel, but if prompted, select Excel to open it with.
  5. We need to insert a column between the Account Number and the Account Description.  To do this, right click on the "B" in the column label for Account Description and select insert.
  6. Change the Account Number column heading in Column A to "Account Number Default"
  7. Change the "Column1" heading in column B to "Account Number".
  8. In Cell B2, "type =HYPERLINK(AJ2, A2)" and hit Enter.  When you hit Enter, it should fill in the Account Number for all cells in column B and should match the Account Number next to it in Column A.  If this doesn't work, verify that in column AJ, you see the "Account Index for DrillBack" column.  It may be in a different column and if this is the case, change AJ2 to the appropriate column. 
  9. Now we are ready to pick our columns that we want included in our report.  You can hide column A and AJ as well as any others that you don't want included in your Excel Report by right clicking on the column label and selecting "Hide".
  10. Once you have all of the columns that you want, we are ready to save the file to a new Excel document.  You can go to "File" and select "Save As".  If it doesn't default to the location you want, browse to the correct location to save it.  In my case, I am going to want to save it in my Excel Reports folder under the  reports\TWO\Financial folder.  You are going to need to type on a file name for it and the Save as type should be "Excel Workbook (*.xlsx)".

  

 

Now that we have the drill down added to the file, we can use it to link back to GP.

  1. Make sure you are logged into GP on the workstation you are going to launch the Excel file on.  You will need to log into the company you set the report up for as a user who will have access to the window it opens.  In this example, I would need to be logged into the Fabrikam, Inc company as a user who has access to the Account Maintenance window.
  2. Open the Excel file you saved in step 10 of the steps above.
  3. Click on any of the Account Numbers in the Account Number column.  If you are prompted with the Microsoft Excel Security Notice, click Yes to continue.
  4. The Account Maintenance window should open up with the account number you clicked on entered in the window.  The window may be minimized, depending on the machine, so you may need to maximize it from your taskbar.

 

 

Things to keep in mind with the drill downs:

  1. You have to be logged into the company the data is for on the same machine as you open the Excel document.
  2. Your Microsoft Dynamics GP user has to have security access to the Window it is trying to open.
  3. Not all windows are setup, but many of the main transaction entry and maintenance windows are. 
  4. You can use Drill Down Builder to create your own drill downs.

 

Hope you are ready to have fun with Drill Downs!

Nicole