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.

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