Drilling Back to Source Data in Dynamics GP 2013 using Dashboards

Exclusive offer: get 50% off this eBook here
Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013

Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 — Save 50%

Easily build powerful dashboards with Microsoft Dynamics GP 2013 and Excel 2013 book and ebook

$29.99    $15.00
by Mark Polino | May 2013 | Enterprise Articles Microsoft

In this article by Mark Polino, author of Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013, will walk you through the deployment of a dashboard. Once you deploy a dashboard, it's inevitable that someone will want more information. They won't believe a number and will want to know the detail that makes up a balance. A great way to manage this is to build a couple of dashboards. Usually, this looks like a primary dashboard with secondary dashboards that break out more information about sales, cash, or departments. We've done this on a very simple level with our Revenue and Net Income tabs. They provide additional detail to expand on the main dashboard numbers.

Another great way to deal with the need for detail, and to take your dashboard beyond what everyone else is doing, is to allow users to drill down into specific transactions or accounts in Microsoft Dynamics GP 2013. In this article, we will look at drill down options including:

  • Hyperlinks

  • Using drill downs present in GP 2013

  • The structure of a drill down

  • Building your own links with Drill Down Builder

  • Drill downs in complex environments

(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.

Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 Easily build powerful dashboards with Microsoft Dynamics GP 2013 and Excel 2013 book and ebook
Published: March 2013
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

Drill Down Builder

Drill Down Builder is part of the optional SmartList Builder product for Microsoft Dynamics GP 2013. SmartList Builder includes:

  • SmartList Builder

  • Excel Report Builder

  • Navigation List Builder

  • Drill Down Builder

Drill Down Builder is used to build Drill Down URLs like the ones that we've been using. If you need a Drill Down URL that is not already in an Office Data Connector, Drill Down Builder can be used to create it. With Drill Down Builder, you can even build drill downs back to SmartLists, not just GP 2013 windows.

However, there is one big problem. Drill Down Builder is broken in the initial release of Dynamics GP 2013. We know how it's supposed to work, because it works in Dynamics GP 2010. It broke when Microsoft changed the link format for GP 2013. Microsoft has promised a fix via a service pack, but the problem hasn't been resolved as of this writing.

Once the problem is fixed, there is actually a really good section on Drill Down Builder and the required security in the SmartList Builder User Guide available with Dynamics GP 2013.

The user guide is really unclear on one key point. When you build a drill down with Drill Down Builder, nothing happens when you are done. You have to build an Excel report using Excel Report builder, and add a drill down to the Excel report with the Drill Down button. After that, you need to publish at least the data connection from the Excel report and open it to see the drill down URL.

It's a lot of steps, especially, if you just need to grab a URL for a dashboard, but there's no other way to get a custom drill down URL.

Complex drill downs

As soon as I show someone drill downs, they get excited, and then the complex scenarios come out. I'll address them here as best I can, but I don't have all the answers yet.

Drilling down with GP 2013 and Excel 2013 on Citrix or Terminal Server

If GP 2013 and Excel 2013 are both running on the same Citrix or Terminal Server instance, drill back from GP 2013 to Excel, as Excel will work as expected. Clicking a drill back link from Excel on Terminal Server or Citrix will open a window in GP 2013 with the appropriate transaction information.

Drilling down to GP 2013 on Citrix with Excel 2013 installed locally

When I originally posted information about drill downs on my blog at DynamicAccounting.net, a user wrote back that they managed to get drill downs working with Dynamics GP running on Citrix and Excel installed on the local machine. I can't promise that this will work, but I will offer it here for you as an option to try:

  1. Publish the GP Protocol Handler as an application; you must grant permissions to all the same users (and running on the same machines) as GP. Session sharing must be enabled. The command location to publish the GP Protocol Handler is C:\Program Files (x86)\Common Files\microsoft shared\Dexterity\Microsoft.Dynamics.GP.ProtocolHandler.exe" "%** (the %** is important, and you have to add it manually).

  2. On the client machines, you need to create some registry changes. Always back up the registry before making changes and proceed carefully. You'll need to make the following registry entries:

    [HKEY_CLASSES_ROOT\dgpp]
    @="URL: DGPP"
    "URL Protocol"=""
    [HKEY_CLASSES_ROOT\dgpp\shell]
    [HKEY_CLASSES_ROOT\dgpp\shell\open]
    [HKEY_CLASSES_ROOT\dgpp\shell\open\command]
    @="\"C:\\Program Files\\Citrix\\ICA Client\\pnagent.exe\" /qlaunch
    \"[Farm Name]:[Published App Name]\" /param:\"%1\""
    [HKEY_CLASSES_ROOT\dgpp\shell\open\command\ctxvalue]
    @="\"C:\\Program Files\\Citrix\\ICA Client\\pnagent.exe\" /qlaunch
    \"[Farm Name]:[Published App Name]\" /param:\"%1\""

  3. [Farm Name] is the name of your XenApp Farm and [Published App Name] is the name you gave to the GP Protocol Handler when you published it in step 1.

Other complex drill down scenarios

I don't have a great answer for this same scenario with Terminal Server, but I suspect that there is an answer for Terminal Server configuration similar to the Citrix setup.

Also, when running GP 2013 as a published app on Citrix or Terminal Server, the setup would be different and finding the answer to that is a work in progress.

As a reminder, drilling back from Excel 2013 to the Dynamics GP 2013 Web Client is not yet supported. This is expected to be added in an upcoming service pack.

Finally, if you restore a production company to a test environment, the server information has already been embedded, even if Excel reports have not been deployed. This means that drill backs will fail, because they are trying to access the production version of Dynamics GP that won't be open. To fix this, you'll need to rebuild the functions, stored procedures, and views in the test company. Fortunately, this isn't as hard as it sounds. To fix drill downs for a copied test company, follow these steps:

  1. Log everyone out of Dynamics GP 2013.

  2. Access the Database Maintenance Utility with Start All Programs | Microsoft Dynamics | GP2013 | Database Maintenance.

  3. In the Database Maintenance Utility, key the SQL server name for the test environment.

  4. Check the box next to the company DB that you need to recreate views and procedures for, and click on Next.

  5. Click on Mark All to reload database objects for all products, and click on Next.

  6. Check the boxes next to both Functions and Stored Procedures and Views, and click on Next.

  7. Hit Next on the confirmation window.

  8. A final screen will pop up indicating success. After that you should be able to play with drill downs in your test environment.

Our dashboard is just about done.

Summary

Despite the problems with Drill Down Builder in the initial release of Dynamics GP 2013, drill down functionality is extremely powerful. Being able to drill back into the information source adds credibility to the dashboard. If users have questions, they can drill back for answers. Hyperlinks provide connectivity to pull supporting data together to enhance the functionality of a dashboard.

Resources for Article :


Further resources on this subject:


Building Dashboards with Microsoft Dynamics GP 2013 and Excel 2013 Easily build powerful dashboards with Microsoft Dynamics GP 2013 and Excel 2013 book and ebook
Published: March 2013
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Mark Polino

Mark Polino is a Microsoft MVP for Dynamics GP, a Certified Public Accountant, and a Microsoft Certified Business Management Solutions Professional. He is the author of the premier Dynamics GP related blog, DynamicAccounting.net, and the creator and presenter of the successful series Getting More Out of Microsoft Dynamics GP: 50 Tips in 50 minutes. Mark has worked with Dynamics GP and its predecessor, Great Plains, for more than 10 years.

He works as a Principal Consultant with I.B.I.S., Inc. and spends his days helping clients implement Microsoft Dynamics GP.

Books From Packt


Microsoft Dynamics CRM 2011: Dashboards Cookbook
Microsoft Dynamics CRM 2011: Dashboards Cookbook

SAP BusinessObjects Dashboards 4.0 Cookbook
SAP BusinessObjects Dashboards 4.0 Cookbook

Microsoft Dynamics AX 2012 Development Cookbook
Microsoft Dynamics AX 2012 Development Cookbook

Microsoft Dynamics NAV 2009 Programming Cookbook
Microsoft Dynamics NAV 2009 Programming Cookbook

Microsoft Dynamics NAV 2009: Professional Reporting
Microsoft Dynamics NAV 2009: Professional Reporting

Microsoft Dynamics GP 2010 Implementation
Microsoft Dynamics GP 2010 Implementation

Microsoft Dynamics CRM 2011 New Features
Microsoft Dynamics CRM 2011 New Features

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
3
H
n
q
m
t
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software