Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

RW - Accessing any SQL data from a Report Example

RW - Accessing any SQL data from a Report Example

  • Comments 17

The following Report Writer and VBA example demonstrates how data from any SQL table can pulled into a report regardless of the product the table is related to.  This technique can also be used when it is not possible to create the table relationship needed to add the report in the Report Writer. It works as long as you can create a query that will uniquely select the data you need based on the data on the report or from the already linked tables.

This example is based on the Manual Payments Posting Journal and MC Manual Payments Posting Journal reports. When the report starts, it uses VBA to obtain a connection via ActiveX Data Objects (ADO) to SQL Server. This connection remains open until the report ends, when the code closes the connection.

In the body of the report the code takes the voucher number of the applied document and runs a Transact-SQL query to obtain the document number for the same document from the PM_Key_MSTR (PM00400) table.  It then populates this new value into a newly created blank string calculated field.

The example is a bit more complicated because these reports are based on temporary tables which re-use the same fields for multiple purposes.  By looking at the other fields in the temporary table (specifically the Sequence Number field) it is possible to identify which records are of the type we want to modify.  For all other records we just pass through the data (for Field2) without making any changes.

NOTE: The method of opening an ADO connection to SQL Server differs for each version. v8.00 uses the external RetrieveGlobals.dll, v9.00 use the external RetrieveGlobals9.dll and v10.00 uses the built-in UserInfoGet object. v10.00 will need at least Service Pack 1 to use this sample. 

Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.

Please see the "Installation Instructions.txt" file in each version's archive for more information.

23-Feb-2009: Add Link to How to use an ActiveX Data Object (ADO) with VBA on a report in Microsoft Dynamics GP (KB 954619) Secure Link

13-May-2009: Add link to Using ADO with VBA with Report Writer article.

Attachment: Manual Payments Journal Report.zip
Page 1 of 2 (17 items) 12
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post