Microsoft Dynamics GP Actions

Microsoft Dynamics GP Actions

By using Mercury "Actions" in a report based on a Dynamics GP database, you can:

  1. Open an underlying GP form associated with the data (a drillback)
  2. Open an attached document.

Each of these features is discussed in detail below. For a general overview of Actions in Mercury, see the article, Introduction to Actions.

Dynamics GP Drillbacks in Mercury Reports

This article assumes that you are aware of a Dynamics GP feature that lets you open a GP form or card from an outside application. This is typically called a "drill back" or a "drill down".

For the technically inclined, GP drillbacks use a Windows feature called a "protocol handler". A link, similar to a web address, directs Windows to open a specific GP form. For example, a link to open a particular GP vendor card might look like this:

dgpp://DGPB/?Db=&Srv=CORPGP&Cmp=ACME&Prod=0&Act=OPEN&Func=OpenVendorID&VENDORID=WILE01

Here are a couple of web articles on GP drillbacks.

Here's what's required to use GP Drillbacks in Mercury

  1. For the Query author (DBA, Developer, or whoever writes your queries) 

    The query that retrieves data for a report must return one or more columns containing drillback links as part of its results; one drillback link type per column. Reports differ in that multiple drillbacks can be returned in a single column. For example, you could have drillbacks for a vendor card, a PO, and a specific GP payables document all in the same report column. But in the Query result, each drillback link type is a separate result column.

    For hints on how to get the drillback links, see Creating Drillback Links below.

For more on data retrieval, see Data Retrieval Guidelines and Stored Procedure Guidelines.

  1. Mercury report creator

    The report definition in Mercury designates one of the report columns as an "Action" column. Instead of displaying a data value, an Action column offers a drop-down list of, well, Actions.

    When you create or edit a report based on data containing drillbacks, you attach the drillback links to your designated Action column.

    For more on creating reports that use Actions, see Introduction to Actions and Reports > Actions.

As of this writing, Actions are only available for Table reports.

  1. Mercury end-user

    When the report is run, the action items – in this case, the drillbacks, are shown in a drop-down list; the user simply clicks on the drillback desired. This automatically opens the associated form in GP.


For GP Drillbacks to work, GP must be running, and the GP user must be logged in to the company referenced by the drillback link, with appropriate privileges to the underlying GP feature(s).

If the report in question is a multi-company report, only the drillback links relating to the currently logged-in company will work. The user would have to log into a different GP company before other drillbacks would work.

This is a consequence of the way GP and Windows operate; it is not specific to Mercury.

There are a few ways to create the drillback links in the stored procedure that provides data to the Mercury report.

  1. The easiest way: use a built-in GP database view

    GP provides a number of built-in SQL Views that include complete drillback links. Base your Mercury report on such a view (or incorporate the view in your stored procedure or statement-query) and chances are you'll find exactly the drillback link you need.

    For example:

    dbo.PayablesTransactions - contains 18 drillbacks
    dbo.ReceivablesTransactions - contains 13 drillbacks

    The sample GP reports that ship with Mercury use the GP built-in views to retrieve drillback links.

  2. Or if you understand the components of the drillback link, you can manually assemble the link as part of your database query. Unfortunately, there is limited GP documentation available to help you with this.

    1. GP itself (beginning with 2013) includes a "Drill Down Builder", which helps you create the "links" that invoke a GP drillback from an outside application.

    2. This article, Drilling Back to Source Data in Dynamics GP 2013 using Dashboards by Packt has an informative section titled "Drill down link structure".

  3. GP provides a number of database functions (all with names starting like "dbo.dgpp …") that return mostly assembled drillback links. You can make use of these functions in your own queries, but you still need to assemble the beginning part of the drillback link, which is a constant for a given server and company-database. 

    For example, this query:

    SELECT dbo.dgppVendorID(1,'WILEE01')

    Returns:

    &Act=OPEN&Func=OpenVendorID&VENDORID=WILEE01

    The complete drillback link would be something like (depending on your SQL server and company database):

          
    dgpp://DGPB/?Db=&Srv=CORPGP&Cmp=ACME&Prod=0&Act=OPEN&Func=OpenVendorID&VENDORID=WILEE01

Enough, Already! Show Me How to Create a Drillback Report

Outside of Mercury

  1. OPTIONAL: The DBA/Developer creates a stored procedure or view, or creates an equivalent query-statement, that includes drillback links in its results.  If you simply want to use an existing built-in GP View as your data source, this step is not necessary.
The following example assumes that a stored procedure exists that includes data from the built-in GP database view dbo.ReceivablesTransactions. This view provides a ton of payables-related data, including many drillbacks (our example uses only a couple).

For this example, our stored procedure lives in the Fabrikam / TWO database and is called dbo.pv_GPReceivablesDrillbackDemo_TWO

Inside Mercury

  1. Start creating a new report as usual. This example assumes you know how to do that. If not, see the article, Reports for instruction. Here's a look at some of the fields that Mercury obtains from the stored procedure.  

The drillback field names were left alone; they're taken directly from the built-in GP Database View. They're a bit long and wordy, but as you probably know it's easy to change the captions within Mercury.

The stored procedure’s author provided a dedicated field for use as the Action field, and even named it "Actions". This is NOT REQUIRED – you can use any field you like – but it's handy. It doesn't matter what's in the field, because you're going to use it for the Action field. Instead of displaying its own data-value, it's going to contain a set of drillback links.

  1. In the Fields tab, designate one of the report fields as the Action field by choosing "Action" type as the field’s Format String. In this example of course we will use the field that the developer created just for that purpose: the field called "Actions".  Below are the Fields after editing: 


  1. As you work through creating the report, you’ll visit the Actions tab.  This is where you will add an action item and associate it a drillback data field.


  1. The action-type is "GP Drill Down".  The display-text should match the drillback link you're adding. In this example, we're using customer# and document# drillbacks, and we'll enter display-text accordingly.

  1. After you click OK, you'll see a new item with the name you entered above in the Actions tab – in this case, "GP Customer Drillback". Expand that item and you see the additional information that Mercury needs – in this case, there's only one, the name of the source field containing your drillback link. Choose it from the drop-down list, as shown.



  1. Add another Action. Repeat the steps above but this time use the value, DocNo Drillback and name it, GP Document Drillback.

That's it for this example, but if your report has more drillbacks, add an Action item for each one. In our case the Actions tab ends up like this:


  1. Choose which action you’d like to be the default by clicking its radio button in the “Is Default” column. 


  1. When the report is run, the field that was formatted as the "Action" field appears in the report as a button containing a drop-down list of actions – in our case, the two drillback actions we defined.


  1. Assuming that Dynamics GP is open and on the proper Company, choosing an action brings up the associated GP form.

Dynamics GP Attachments in Mercury reports

Beginning with Dynamics GP 2013, external documents can be attached to various GP objects.  This feature is configured in GP via Tools > Setup > Company > Document Attachment Setup. Once enabled, any file (PDF, Excel workbook, etc.) that is available on the system can be used as an attachment in GP. When the document is attached, GP stores a copy of the file internally in the SQL company database. This copy is available through GP even if the original file is moved, renamed, or deleted.

These documents can also be accessed through Mercury Table-reports. The data comprising the report simply must include the information needed to identify the document within the SQL company database. That's easily done by incorporating the appropriate GP "attached documents" table(s) in the query that retrieves the data.


Here's what's required to open GP Attachments within Mercury

DBA / Developer (stored procedure) -

The stored procedure that retrieves data for the report must include in its results:
  1. A GP "Attachment_ID"- this is a unique value used by GP to identify attachment records in a company database. The most commonly used tables for this purpose are CO00102 and CO00105. In addition to a GP Attachment_ID, these tables have other information that may be helpful to the user, such as the original filename of the attached document.
The internal structure of the Dynamics GP attachment-tables and the way that GP data (payables, invoices, etc.) are joined to attachment-tables can be confusing. The stored procedures used with the sample Mercury reports featuring GP attachments may be help illustrate these data relationships.

  1. A GP database-name (company) - Because Mercury reports can be multi-company, you must tell Mercury the company/database to which a given attachment belongs.
Because a single GP entity (a sales invoice, a payables invoice, a journal entry transaction, etc.) can have multiple attached documents, in Mercury these attachments are typically presented as part of a master/detail (parent/child) report. The master is the GP entity (for example, the SOP document); the detail/children represent the collection of attached documents – perhaps a customer's purchase order, a copy of the invoice document, and a shipping receipt. Each attached document is represented by a single detail/child record.
Even though the attachment content (i.e., a copy of the original attached document) lives inside the company database, you should not return this content as part of the query – some documents are large, and there's no need to gather the content before the user asks for it. Identifying the particular database record is enough; Mercury will do the rest when the report is run. In fact, even if you do return the attachment content in the query, Mercury will ignore it. It uses the company database-name and the attachment-id to retrieve the content when the user requests it.

For more on stored procedures and data retrieval in general, see article, Stored Procedure Guidelines.

For specific examples, see the procedures that are used for the Mercury sample reports incorporating attachments.

Mercury report creator - 

The report definition in Mercury must designate one of the report columns as an "Action" column. Instead of displaying a data-value, this column is used to present a set of Actions – for example, opening an attachment. 
As described above in the section on stored procedures, an internal GP Attachment_ID is used to identify the attachment within the GP database. Typically, it is this Attachment_ID column that is designated as the action-column in the report. If you do not see such a column when you are creating your Actions, check with the author of the stored procedure. Even if you want to use a different column as the Action column, you still need a GP attachment-ID in order to have Mercury display the attachment.
For more on creating reports that use Actions, see Introduction to Actions.  
As of this writing, Actions are only available for Table reports.

Mercury end-user - 

When the report is run, the action-items are shown in a drop-down list; the end-user simply clicks on the desired attachment. The document then opens in the associated application (PDF viewer, Excel, etc.).


An appropriate application must be available on the computer running Mercury; otherwise, Windows will not be able to open the attachment.

    • Related Articles

    • Introduction to Actions

      A Mercury Action provides a way, from within a Mercury report, to “do something” based on a particular data row or cell. An Action might consist of opening an external document attached to your data – say, a spec sheet on one of your products, or a ...

    • Evaluating Mercury with your Own GP Data

      This article assumes that you have already installed Mercury. Mercury comes with a handful of sample reports, including several that use Dynamics GP data from our Test-and-Demo server on the web. Redirecting these reports to use your own GP server is ...

    • Stored Procedure Guidelines

      Stored Procedure Guidelines for Data Retrieval This document contains information applicable to stored procedures in most database platforms.   It also includes some tips relating specifically to Microsoft SQL Server and Microsoft Dynamics GP and ...

    • Reports

      Manage Reports The Manage Reports form lets you create and edit reports, assign User accesses, and perform other maintenance tasks. (Note: Mercury allows users who run the report to modify the layout and save as a Favorite, so you don't have to worry ...

    • Quick Start Guide for Administrators

      So you’ve just set up a trial, or purchased Mercury for your company. Thank you! Now what? Connect to Your Corporate Database(s) To use Mercury with your corporate data, you need to: Create one or more Connections to your corporate data sources. Each ...