Tech Tuesday: Using Drill Down Builder outside of Excel Report Builder
Tuesday, May 20, 2014
Drill Down Builder has been a part of SmartList Builder for a few releases now. It can be used to give you the ability to drill back from an outside application to the GP windows. Of course, it does require that you have security to the window that you are trying to drill back to and that you are logged into GP and into the company that the data is for.
To get started, you must setup the Drill Down in Drill Down Builder, defining the parameters and window you want to drill back to. Once you have that defined, the setup from Drill Down Builder can be added in Excel Report Builder and SmartList Builder. It can also be added to other locations such as Web Sites, e-mails, and SQL Reporting Services reports.
When you add that drill down to the external application, it is going to need some information from you so that it knows how to drill back and with what data. Below is the basic syntax for the drill down.
dgpp://DGPB/?Db=SQL_Instance&Srv=Server_ name
&Cmp=Database_Name&Prod=3830&Act=OPEN&Func=Drill_Down_ID&Param_Name=Value
The variables in the drill down break down as so:
Db: Equal to the Instance name of your SQL Server. If you do not have a SQL instance name, leave it equal to nothing.
Srv: Equal to the Server name of the SQL Server.
Cmp: Equal to the database ID of the company database to drill back to.
Prod: Equal to the product the drill down is from. If you are using Drill Down Builder, this would be 3830
Act: Equal to the Action Type which is generally going to be OPEN.
Func: Equal to the Drill Down Function you are calling. If you are using Drill Down Builder, it would be equal to the Drill Down ID.
Param_Name: Param_Name is to be replaced by the Parameter you are calling and set equal to the value to pass to that parameter.
Here are a couple of examples:
1. In this example, we are using the default Microsoft Dynamics GP drill down functionality.
dgpp://DGPB/?Db=SQL2012&Srv=EONE&Cmp=TWO&Prod=0&Act=OPEN&Func=OpenAcctIndx&ACTINDX=1
The SQL Server is an instance called EONESQL2012, the Company is TWO, it is using one of the built in drill downs in GP so the Prod is 0, the function being called is OpenAcctIndx, and the Parameter being passed is the Account Index (ACTINDX) with the value of 1.
2. In this second example, we are using a Drill Down Builder setup.
dgpp://DGPB/?Db=&Srv=EONE&Cmp=TWODM&Prod=3830&Act=OPEN&Func=RMDOC&FromRMNUMBE=
STDINV2060&ToRMNUMBE=STDINV2060
The SQL Server is the default instance on the EONE server, the Company is TWODM, it is using a Drill Down Builder built setup so the Prod is 3830, the function being called is RM, and the Parameters being passed are the FromRMNUMBE and ToRMNUMBE, both with the value of STDINV2060.
To help with this example, here is the setup in Drill Down Builder.
To get started, you must setup the Drill Down in Drill Down Builder, defining the parameters and window you want to drill back to. Once you have that defined, the setup from Drill Down Builder can be added in Excel Report Builder and SmartList Builder. It can also be added to other locations such as Web Sites, e-mails, and SQL Reporting Services reports.
When you add that drill down to the external application, it is going to need some information from you so that it knows how to drill back and with what data. Below is the basic syntax for the drill down.
dgpp://DGPB/?Db=SQL_Instance&Srv=Server_ name
&Cmp=Database_Name&Prod=3830&Act=OPEN&Func=Drill_Down_ID&Param_Name=Value
The variables in the drill down break down as so:
Db: Equal to the Instance name of your SQL Server. If you do not have a SQL instance name, leave it equal to nothing.
Srv: Equal to the Server name of the SQL Server.
Cmp: Equal to the database ID of the company database to drill back to.
Prod: Equal to the product the drill down is from. If you are using Drill Down Builder, this would be 3830
Act: Equal to the Action Type which is generally going to be OPEN.
Func: Equal to the Drill Down Function you are calling. If you are using Drill Down Builder, it would be equal to the Drill Down ID.
Param_Name: Param_Name is to be replaced by the Parameter you are calling and set equal to the value to pass to that parameter.
Here are a couple of examples:
1. In this example, we are using the default Microsoft Dynamics GP drill down functionality.
dgpp://DGPB/?Db=SQL2012&Srv=EONE&Cmp=TWO&Prod=0&Act=OPEN&Func=OpenAcctIndx&ACTINDX=1
The SQL Server is an instance called EONESQL2012, the Company is TWO, it is using one of the built in drill downs in GP so the Prod is 0, the function being called is OpenAcctIndx, and the Parameter being passed is the Account Index (ACTINDX) with the value of 1.
2. In this second example, we are using a Drill Down Builder setup.
dgpp://DGPB/?Db=&Srv=EONE&Cmp=TWODM&Prod=3830&Act=OPEN&Func=RMDOC&FromRMNUMBE=
STDINV2060&ToRMNUMBE=STDINV2060
The SQL Server is the default instance on the EONE server, the Company is TWODM, it is using a Drill Down Builder built setup so the Prod is 3830, the function being called is RM, and the Parameters being passed are the FromRMNUMBE and ToRMNUMBE, both with the value of STDINV2060.
To help with this example, here is the setup in Drill Down Builder.
One easy way to find the syntax of a drill down that you have built with Drill Down Builder is to add it to an Excel Report Builder report. After publishing, open the file and unhide the hidden columns. You will see a column with the drill down for each record where you can find the syntax that is being used.
Interested in SmartList Builder? Sign up for an 11am Wednesday public demo here or email sales@eonesolutions.com for more information!
