다음을 통해 공유

RW - Accessing any SQL data from a Report Example

  • 아티클

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.

Manual Payments Journal Report.zip

Comments

  • Anonymous
    July 24, 2008
    I often get told that it is impossible to customize a report which uses a temporary table, and in particular

  • Anonymous
    August 04, 2008
    Posting from DynamicAccounting.net http://msdynamicsgp.blogspot.com/2008/07/make-sure-you-are-reading-david.html

  • Anonymous
    August 04, 2008
    Posting from Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/07/modifying-reports-that-use-temp-table.html

  • Anonymous
    August 31, 2008
    During my time in the partner channel, I worked with a number of consultants who had a number of problems

  • Anonymous
    October 29, 2008
    Most people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP

  • Anonymous
    October 29, 2008
    As a follow on to the Using VBA with Report Writer post, I would like to discuss using ActiveX Data Objects

  • Anonymous
    December 09, 2008
    This works like a charm have been able to implement for several clients... Only thing is there a RetrievealGP10.dll or do you

  • Anonymous
    December 09, 2008
    Thanks for the note GPNUT. For release 10.0, there is no RetrieveGlobals.dll and you would instead use the built-in UserInfoGet object that is available. For GP 10.00 you will need at least Service Pack 1 to use this sample.

  • Anonymous
    May 17, 2009
    One of biggest issues people have with the Microsoft Dynamics GP Report Writer is not being able to create

  • Anonymous
    October 27, 2009
    I used the example in GP 10 SP 4 everything goes fine but the calc. field wouldn't change value i try to put a code just to change the value in different events without any luck Can you please help?

  • Anonymous
    October 27, 2009
    Hi Alfred Have you put break points in the code to confirm that it is running and to allow you to trace through the scripts. Do you have VBA working elsewhere in the application on this workstation?   The KB below can help with fixing VBA permissions if VBA is not working on this workstation: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;929612 David

  • Anonymous
    October 27, 2009
    Hi David Thanks for your answer, yes i tried break points it all goes fine and even show that the calc. field value is correct, but when the report come out the value was still empty string. It only did work when i changed the code location from Report_BeforeBody event to Report_BeforeRH event i did try to but the code back in Report_BeforeBody and move my calc. field to different report sections but with the same result, nothing. Alfred

  • Anonymous
    October 27, 2009
    Hi Alfred Make sure that the Field Type for the place holder calculated field you have created is of type DATA. Have you looked at the related articles: http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/30/using-vba-with-report-writer.aspx http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/30/using-ado-with-vba-with-report-writer.aspx  David

  • Anonymous
    May 31, 2013
    If I modify my standard report to use vba, will the vba-collected data also appear on my corresponding Word Template?

  • Anonymous
    March 26, 2014
    Hi David, Since vba is not supported in the web client is there now an alternative to this method or will vba eventually be supported? Cheers

  • Anonymous
    March 26, 2014
    Hi Arthur That is a great question. The only alternatives I can offer are Dexterity Modifications or using the Support Debugging Tool. The SDT method is not officially supported, but works well. See the blog post below: blogs.msdn.com/.../how-to-add-item-category-long-descriptions-to-reports-using-the-support-debugging-tool.aspx David

  • Anonymous
    March 27, 2014
    I will give that the SDT method a go, thanks