12 min read

(For more resources related to this topic, see here.)

Recap

Throughout the book we’ve been building a dashboard that looks like the following screenshot:

We’re done with the hard parts, and now we’re connecting the pieces and cleaning things up.

Learning about hyperlinks

Hyperlinks are a feature of Excel 2013 that have been in the product for a while. Links can be built via the interface or with a formula. They provide a great way to link sheets together for the user.

In the previous chapters, we added additional information to our Revenue and Net Income tabs. We’ll start by linking these tabs to our dashboard. To build our hyperlinks:

  1. Open the Dashboard.xlsx file of GP 2013 that we’ve been working with.

  2. On the Dashboard tab, select cell D7. This should be the Revenue label.

  3. Click on Insert | Hyperlink on the Excel ribbon.

  4. In the Link to: section, on the left-hand side, select Place in This Document.

  5. In the center section, under Or select a place in this document:, pick Revenue, as shown in the following screenshot:

  6. Click on OK. The Revenue label will turn blue and be underlined indicating a hyperlink, as shown in the following screenshot:

  7. Click the new Revenue link to drill down to the Revenue tab. We

We need to do the same thing for the net Income line. To link to additional net income information, follow these steps:

  1. Click on the Dashboard tab.

  2. On the Dashboard tab, select cell E7. This should be the Net Income label.

  3. Click on Insert | Hyperlink on the Excel ribbon.

  4. In the Link to: section, on the left-hand side, select Place in This Document.

  5. In the center section under Or select a place in this Document, pick ‘Net Income‘ and click on OK.

  6. Save the file.

  7. Hyperlinks don’t have to link back to another Excel sheet. They can also link to more information on the Web or to a location in SharePoint, for example. Finally, we can link them back to a transaction in Dynamics GP 2013. That’s up next.

Using drill downs in GP 2013

At its simplest, a drill down is a hyperlink that links back into Dynamics GP. When the user clicks the hyperlink, the focus changes to Microsoft Dynamics GP 2013, and the linked window opens in GP with the appropriate data. In the real world, a dashboard might display cash balances for each bank account, or checkbook in GP terms. The operating checkbook would have a hyperlink attached on the dashboard. Clicking on the link would cause the checkbook register inquiry window to open in Dynamics GP and display information from the operating checkbook.

Drill down background

There are limited training resources available around drill downs. Drill Down Builder gets only a few pages in the SmartList Builder User Guide. Other books on the market that cover SmartList Builder skip Drill Down Builder altogether. When I pushed Microsoft for a list of pre-built drill downs, it couldn’t supply one. Also, Microsoft inconsistently uses the terms drill down and drillback interchangeably. For our purposes, they are the same thing.

A drill down link can work for inquiries and transactions throughout GP. Since these drill downs are both poorly documented and numerous, you would think that they would be hard to use, except that Microsoft gave us a huge shortcut. The Office Data Connector files that we’ve been using for our dashboard contain drill down links. Each ODC file has one or more columns that link back into Dynamics GP. If you can’t find the link you are looking for, you can even build your own with the optional Drill Down Builder module from Microsoft.

In this article, we’re going to build some links, explain how they work, and add some to the dashboard.

Before we get rolling, there are few things that you need to know:

  • The user must have Dynamics GP 2013 open and be logged in to the company they are drilling into for the drill down to work. The hyperlink will not open Dynamics GP 2013 for you. This arrangement also makes licensing and security straightforward, since it’s controlled by the GP 2013 interface.

  • The user must have permission in Dynamics GP 2013 to open the window that they are trying to drill back into. For example, if a user doesn’t have access to payroll inquiry via GP, we certainly don’t want them to be able to drill down into that data via Excel.

  • Drill Down Builder is not required to drill down into Dynamics GP 2013. Drill Down Builder is a part of the optional SmartList Builder product available from Microsoft at an additional cost. Drill Down Builder is used to create drill downs and is covered later in the article.

  • It is possible to drill down from a local instance of Excel to Microsoft Dynamics GP 2013 on a Citrix server. I didn’t say it was easy, but it can be done. We’ll look at options at the end of the article.

  • At the release of Microsoft Dynamics GP 2013, drilling down from Excel to GP via the new web client was not available. It may be made available later via a service pack.

Now that we have all the background out of the way, let’s drill down!

Using drill downs

Drill downs are simplest to explain when we bring the data into Microsoft Excel 2013, so we’ll go down that route with a common example. To build your first drill down, follow these steps:

  1. Open the sample company in Microsoft Dynamics GP 2013.

  2. Select Financial in the navigation list on the left-hand side.

  3. In the pane above, select Excel Reports.

  4. Double-click the selection marked TWO AccountTransactions. The type should be Data Connection.

  5. Scroll all the way to the right-hand side of the resulting Excel file. You should see two columns labeled Account Index for Drillback and Journal Entry for Drillback. These are the two default drill downs URLs for journal entry transactions:

    • Account Index for Drillback: This entry will open the Account Maintenance window for this account. That’s not terribly helpful in most cases since it just lists the account setup.

    • Account Index for Journal: This entry will open the Journal Entry Inquiry window for posted transactions and the Transaction Entry window for unposted entries. Both of these windows then allow drill back into additional detail.

We have the link details, but it’s not yet a link in Dynamics GP. To build a formula-based link in Excel 2013, follow these steps:

  1. In the Excel sheet, insert a column between columns A and B to create a blank column B.

  2. In cell B1, type JE Link.

  3. In cell B2, type =HYPERLINK(DR2,A2). Cell DR2 should be the first cell under Journal Entry for Drillback. Here, we’re building a hyperlink using a formula instead of the interface. Unlike the interface-based link we used for revenue, a formula-based link is dynamic, making it easy to build a link per line.

  4. Column B now contains the Journal Entry number with a link.

  5. Scroll down to journal entry 27 and click on the link.

  6. Click on Yes when the security notice appears.

    There is a way to disable this box using a registry entry, but there are variations based on your version of Windows and Office. You can find out more at http://www.msoutlook. info/question/245. Make sure to back up the registry before making changes.

  7. The Journal Entry Inquiry window will open for journal entry 27. A user can then click on Source Document to continue drilling back into the source of this journal entry.

The reason that we selected journal entry 27 to drill back into is that this is a posted journal entry. If we had selected an unposted journal entry, the Transaction Entry window would have opened. In the Dynamics GP interface, you can’t use an inquiry window to inquire on an unposted journal entry. You get an error message that says that entry hasn’t been posted. Because of this, the drill down created is different for posted and unposted transactions.

Fixing the journal entry drill down problem

In the release to manufacturing (RTM) version of Dynamics GP 2013, drilling back to an unposted journal entry generates the error message, The URL was missing required Dynamics GP Drill Back parameters. There was a change to the way that the URL was structured in GP 2013, and it broke this functionality. There is an “e” in the constant for the action type that shouldn’t be there. A fix is due in an upcoming service pack, but if you don’t want to wait, there is another option. Executing this SQL code for each GP 2013 company will fix the issue:

alter FUNCTION dgppJournalEntry (@action int,
@JRNENTRY int,
@RCTRXSEQ numeric(19, 5),
@DCSTATUS int,
@DOCTYPE int)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @ActionType varchar(15),
@FunctionName varchar(50),
@URIstring varchar(255)
select @FunctionName = 'OpenJournal'
if @action = 1
select @ActionType = 'OPEN'
else
select @ActionType = 'OPEN'
select @URIstring = '&Act=' + @ActionType + '&Func=' + @
FunctionName
+ '&JRNENTRY=' + ltrim(str(@JRNENTRY))
+ '&RCTRXSEQ=' + ltrim(str(@RCTRXSEQ))
+ '&DCSTATUS=' + ltrim(str(@DCSTATUS))
+ '&DOCTYPE=' + ltrim(str(@DOCTYPE))
RETURN( @URIstring )
END

The code is also available at https://www.box.com/s/xutg9wbeb9f531cvuevk (Short link: http://bit.ly/13VEIr8).

Drill down link structure

Since we have so much flexibility with drill downs, it’s worth understanding what the structure of a drill down looks like.

Here is my drill down link for journal entry 27:

dgpp://DGPB/?Db=GP2013&Srv=MPOLINO2011&Cmp=TWO&Prod=0&Act=OPEN&Func=O penJournalInq&JRNENTRY=27&RCTRXSEQ=1&YEAR1=2014&TRXDATE=01/01/2014

That thing is huge! The good news is that it breaks down pretty easily. All the elements are connected by the ampersand (&) symbol. The description of other elements is given in the following table:

Drill Down elements

Description

dgpp://DGPB/?

This is the drill back URL that indicates that

the program to work with is Dynamics GP.

Db=GP2013

This is the database instance. You won’t see

a database instance if your GP installation

uses the base SQL Server instance. The base

instance is more common. In this case, the

instance is named GP 2013.

Srv=MPOLINO2011

This is the server name. In our example

here, the server name is MPOLINO2011.

Cmp=TWO

Cmp represents the database name for the

company to drill back to. Our example uses

TWO, the sample company.

Prod=0

This is the product. Product 0 equates to

Dynamics GP. Other product numbers

might refer to Fixed Assets, Project

Accounting, or an ISV solution. Product

numbers are listed in the Dynamics.set

file.

Act=OPEN

This is the action, where we are going to

open a window.

Func=OpenJournalInq

Func represents the function. The function

we are performing is opening the Journal

Inquiry window.

JRNENTRY=27

This is the first parameter; we want to

return Journal Entry 27.

RCTRXSEQ=1

Recurring Transaction Sequence is the

second parameter and it is set to 1. Since

recurring transactions can have the same

journal entry, this specifies which instance

of a recurring transaction to use.

TRXDATE=01/01/2014

The final parameter is the transaction date,

January 1, 2014.

Drill down links for inventory, sales, or other transactions will be similar. In our case, the links are already built for us and the link elements are static. Since we know the structure, we can also make the link dynamic and let it get values from a cell.

To illustrate this:

  1. Clear column B.

  2. Copy and paste the value from cell DR2 into cell B2. It should look like dgpp://DGPB/?Db=GP2013&Srv=MPOLINO2011&Cmp=TWO&Prod=0&Act=OPE N&Func=OpenJournalInq&JRNENTRY=27&RCTRXSEQ=1&YEAR1=2014&TRXDA TE=01/01/2014.

  3. Enclose the entry in quotes.

  4. Put an equal sign (=) in front of the first quotation mark to make it a formula.

  5. In the &JRNENTRY=27& section, change this to be &JRNENTRY=”&A169&”&.

  6. In the &YEAR1=2014& section, change this to be &YEAR1=”&BH169&”&.

  7. In the &TRXDATE=01/01/2014 section, change this to be &TRXDATE=”&TEXT(D169,”mm/dd/yyyy”)&“.

  8. The final formula should look like =”dgpp://DGPB/?Db=GP2013&Srv=MPOL INO2011&Cmp=TWO&Prod=0&Act=OPEN&Func=OpenJournalInq&JRNENTRY=” &A169&”&RCTRXSEQ=1&YEAR1=”&BH169&”&TRXDATE=”&TEXT(D169,”mm/dd/ yyyy”)&””.

  9. Now we have a dynamic formula that gets the appropriate values from the various cells. Note the double quotes at the end to make it all work.

It’s time to see how we can apply this practically to our dashboard. We’ll take our Top 10 Customers tab and enhance it with a drill back to customer information. To do this:

  1. Make sure that your Dashboard.xlsx file of GP 2013 is open.

  2. Select the Top 10 Customers tab.

  3. Click inside the pivot table. If Field List doesn’t open on the right-hand side, click on the Analyze tab under PivotTable Tools and pick Field List.

  4. In Field List, check the box next to Customer Number

  5. Uncheck the box next to Customer Name.

  6. Ensure that the pivot table still shows the top 10 customers sorted by Document Amount.

  7. In cell C3, next to the pivot table header, type Link.

  8. Save the file.

Okay, everything is prepared. Now, we need to go find the link. To do that, follow these steps:

  1. Open Microsoft Dynamics GP 2013.

  2. Select Sales from the navigation pane on the left-hand side.

  3. Pick Excel Reports from the navigation list above.

  4. In the center, find Data Connector, not report, labeled TWO Customers and double-click on it.

  5. When Excel opens, click on OK to put the data in a table.

  6. Scroll to the right-hand side in the resulting Excel file to find the column labeled Customer Number For Drillback. It should be near column FR.

  7. Select the first row below Customer Number For Drillback.

  8. Right-click and select Copy.

  9. Return to the Top 10 Customers tab in the Dashboard.xlsx file of GP 2013.

  10. Select cell D4.

  11. Right-click and pick Paste.

  12. Click on the link pasted into cell D4.

  13. Put an equal sign (=) at the front.

  14. Place quotation marks (” “) on the front and back of the link, after the equal sign. It should look similar to =”dgpp://DGPB/?Db=GP2013&Srv=MPOLINO2 011&Cmp=TWO&Prod=0&Act=OPEN&Func=OpenCustNmbr&CUSTNMBR=AARONF IT0001″.

  15. At the end of the formula, replace the customer number between the equal sign and the final quote with “&A4&”. The final formula should look similar to =”dgpp://DGPB/?Db=GP2013&Srv=MPOLINO2011&Cmp=TWO&Prod=0&Act =OPEN&Func=OpenCustNmbr&CUSTNMBR=”&A4&””.

  16. Note that there are two sets of quotes at the end.

  17. In cell C4, type the formula, =Hyperlink(D4,”Drillback”).

  18. Copy cells C4 and D4 down through all 10 customers.

  19. Click one of the drill back links. The Customer Maintenance window should open for the customer selected, as shown in the following screenshot:

  20. With a different drill back, we could link to the Customer Inquiry window.

  21. Save the file.

LEAVE A REPLY

Please enter your comment!
Please enter your name here