You, Me and Dynamics GP
Dynamics GP provides an option to drill back into the application via a special hyperlink. So you can drill back into Dynamics GP data from Microsoft Excel, SQL Server Reporting Services, a web page, SharePoint, etc.
- You do NOT have to own Drill Down Builder to make this work. More about that in a minute.
- You DO have to have Dynamics GP open and the logged in user has to have access to the window you are drilling back to.
The basic format looks like this:
dgpp://DynamicsGPDrillBack/?DatabaseInstance=SQL_Instance&ServerName=Server_
name&CompanyID=Database_Name&ProductID=3830&ActionType=OPEN&FunctionName=Drill_Down_ID&Param_Name=Value
Here is a quick example:
I want to open the item inquiry window from an Excel file. I have an Excel file with item number ACCS-HDS-1EAR in cell A2.
I add a formula in cell C2 to do the lookup. It looks like this:
="dgpp://DynamicsGPDrillBack/?DatabaseInstance=&ServerName=mpolino2011&CompanyID=TWO&ProductID=0&ActionType=OPEN&FunctionName=OpenItemNumber&ITEMNMBR="&A2&"&LOCNCODE=WAREHOUSE&CallerID=0&FocusWindow=true"
Before you freak out, lets break it down.
- ="dgpp://DynamicsGPDrillBack/? – this is building an Excel formula (= sign and quotes) with the base drill back URL
- DatabaseInstance= – This is the server instance. If you are not using an instance, nothing goes after the equal sign like you see here.
- ServerName=mpolino2011 – this is the server name
- CompanyID=TWO – this is the company ID. In this case it’s the Fabrikam test company.
- ProductID=0 – This is the product ID from the Dynamics Set file. 0 is Dynamics GP. This would be different if you were drilling down into Project Accounting or something else.
- ActionType=OPEN – This is the action we want to take. It looks like OPEN is the only option.
- FunctionName=OpenItemNumber – This is the function. We want the Open Item Number function.
- ITEMNMBR="&A2&" – Here we get our item number from cell A2.
- LOCNCODE=WAREHOUSE – in optional parameters we set which Warehouse we want to use.
- CallerID=0 – I have no idea what Caller ID does
- FocusWindow=true" – This activated the window but for some reason the window may still be hidden down on the taskbar under the GP icon.
- & – this is used to string the elements together.
dgpp://DynamicsGPDrillBack/?DatabaseInstance=&ServerName=mpolino2011&CompanyID=TWO&ProductID=0&ActionType=OPEN&FunctionName=OpenItemNumber&ITEMNMBR=ACCS-HDS-1EAR&LOCNCODE=WAREHOUSE&CallerID=0&FocusWindow=true
The Hyperlink function in Excel can be used to tied this link with our description.
=HYPERLINK(C2,A2)
The spreadsheet looks like this:
Clicking the link in cell B2 opens the Item Inquiry window for this item and warehouse.
For example, if accounts and balances were exported to Excel from Management Reporter, a user could grab a copy of a URL that was prebuilt and copy into the Excel sheet. This would provide a live link to recipients to allow them to drill back into GP.
The only documentation I’ve found around this is Appendix A of the Smart List Builder User Guide. It’s literally one page long.Based on piecing things together there are number of pre-built drill drowns. If you want to create drills downs to other forms, you need Drill Down Builder.
Jared Hall was kind enough to point out that the views created when installing Smart List Builder (whether you have a license or not) include the hyperlink as a field.
This provides a great shortcut.
We still need more information around:
- Action Type
- Function Name
- Parameter Name
So I’m looking to build some resources around the dgpp:// hyperlink. If you play with this and have some insights please drop me a note.
