Freigeben über

Using VBA with Report Writer

  • Artikel

David MeegoMost people are aware that you can use Visual Basic for Applications (VBA) with Microsoft Dynamics GP forms and the Modifier, but not everyone is aware that VBA can be used with the Report Writer as well.

In fact using VBA with the Report Writer can allow us to bypass many of the limitations of the Report Writer.  I have already provided examples on some of these techniques and will include the links where appropriate.

 

Getting Started

The first point to make is that adding reports and report fields to VBA is handled while in the Report Writer layout mode and not in the application itself.  Testing of the report can be from the Report Writer (if there are no temporary tables used) or back in Microsoft Dynamics GP itself.  This is the same as if you were working with just the Report Writer.

When you add a report to VBA (use Tools >> Add Report to Visual Basic) you will have access to the following events (in execution order):

  • Report_Start()
  • Report_BeforeRH()
  • Report_BeforePH()
  • Report_BeforeAH()
  • Report_BeforeBody()
  • Report_BeforeAF()
  • Report_BeforePF()
  • Report_BeforeRF()
  • Report_End()

The Report_Start() and Report_End() events are executed before and after the report itself and can be used to perform any setup and cleanup needed.  The most common use for these two sections is to open and close an ActiveX Data Objects (ADO) connection to SQL Server.

All the other events are executed just before a report section/band is printed.  The Report Writer has completed generating the report section and now allows VBA to modify the data or suppress the section/band. 

Note: Once the Report Writer has finished generating the section and passed control to VBA, control will not return to the Report Writer for that section.  This means that if you are using VBA to populate data in fields, those fields cannot be used by Report Writer calculated fields.  If you need the data to be used in further calculations, those calculations must also be handled in the VBA code.

 

Adding Fields

To add one or more fields on a report to Visual Basic, select the fields (use Shift or Control to multi-select) and then select Tools >> Add Fields to Visual Basic. The Tools menu is shown below:

Report Writer Tools Menu

If you wish to have VBA display data back to the report, you need to create a calculated field with an empty constant value of the same data type as the result type of the field.  For example:

  • Result Type: String, Value = empty string constant
  • Result Type: Integer, Value = zero value integer constant 

You can place the field on the report and then add it to VBA. Once exposed to VBA you can set the value back into the field on the report from the VBA code. These fields are just placeholders for the data to be displayed in.

Note: Returning Currency values often has issues with decimal places being interpreted incorrectly.  So 3.80 shows up as 38 and 3.75 shows up as 375.  To bypass this issue use a string calculated field in Report Writer and return the value from VBA using the FormatCurrency() or FormatNumber() functions. For more information on this method see the following posts:

 

Capturing of Values from other Sections

There may be times where you need access to data that is not included in the same report section that your code exists in.  Sometimes you can drag the field out into your section as a hidden field and add that hidden field to VBA.  This would ensure you had access to the data.

In the situation where it is calculated data or it is not a direct table value, you might need to use VBA code to capture the value from one section of the report so it can be used in another section of the report. The sample code below shows how fields from a SOP Document Report Header section can be stored as VBA variables so that we can refer to them from the body section.

Option Explicit

Dim l_SOPType As Integer
Dim l_RMType As Integer
Dim l_SOPNumber As String
Dim l_CustomerPONumber As String
Dim l_CustomerNumber As String

Private Sub Report_BeforeRH(SuppressBand As Boolean)
    ' Capture values from Header
    Select Case DocType
        Case "Invoice":
            l_SOPType = 3
            l_RMType = 1
        Case "Return":
            l_SOPType = 4
            l_RMType = 8
        Case Else
            l_SOPType = 0
            l_RMType = 0
    End Select
    l_SOPNumber = Trim(SOPNumber)
    l_CustomerPONumber = Trim(CustomerPONumber)
    l_CustomerNumber = Trim(CustomerNumber)
End Sub

Private Sub Report_BeforeBody(SuppressBand As Boolean)
    ' Code in the body section and use any of the variables
    ' captured from the report header section
End Sub

Note:  This example also shows a method to handle the differences between the SOP document types and the Receivables document types with a Select Case statement.

 

Additional Headers and Footers by Level

When using Additional Header and Footers in the Report Writer, the sections are labeled as H1, H2, H3, .... and F1, F2, F3, .... etc.  VBA uses a single event for all Additional Headers and a single event for all Additional Footers. It passes an integer Level parameter to specify exactly which header or footer triggered the event. 

The code sample below shows a simple method of using the Level parameter to ensure that the code for the correct header or footer is executed.

Option Explicit

Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
    Select Case Level
        Case 1:
            ' Code for H1 goes here
        Case 2:
            ' Code for H2 goes here
        Case 3:
            ' Code for H3 goes here
        Case Else
    End Select
End Sub

Private Sub Report_BeforeAF(ByVal Level As Integer, SuppressBand As Boolean)
    Select Case Level
        Case 1:
            ' Code for F1 goes here
        Case 2:
            ' Code for F2 goes here
        Case 3:
            ' Code for F3 goes here
        Case Else
    End Select
End Sub

 

Using the DUOS

With VBA, you can add additional fields to forms and store the data in the Dynamic User Object Store (DUOS) table.  This data can be read from the DUOS and displayed in blank calculated "placeholder" fields. Below is some example code:

Option Explicit

Private Sub Report_BeforeBody(SuppressBand As Boolean)
    ' Retrieve DUOS Fields
    Dim CustomerCollection As DUOSObjects
    Dim CustomerObject As DUOSObject
   
    Set CustomerCollection = DUOSObjectsGet("Customer Information")
    Set CustomerObject = CustomerCollection(CustomerNumber)
   
    MothersMaidenName = CustomObject.Properties("Mother's Maiden Name")
End Sub

This example post shows how additional fields can be added to a window and a report:

Modifier - Sales Prospect DUOS Example

 

Reports with Temporary Tables

Most people believe it is not possible to modify reports which use temporary tables.  While it is definately harder it is not impossible.  The following post explains some of the techniques you can use:

Modifying Reports which use Temporary Tables

 

Accessing Data from other Tables 

When you need to access data that is in a table that you cannot create a table relationship to, you can use ActiveX Data Objects (ADO). ADO will allow you to log into your SQL Server database and then execute an appropriately constructed select statement to read the required data.  As long as you have access to enough data to be able to generate the SQL where clause needed to obtain your data, you can write any select statement you wish.

Some reasons why it might not be possible to create the table relationship needed by Report Writer can include:

  • The table is in another Product Dictionary
  • There is already a different relationship between the tables
  • There are no suitable indexes
  • You cannot match all the fields in the index
  • You need to use a constant value for a field in the index
  • You can only create a one to many link and there is already a one to many link at that level in the report table hierarchy

The Using ADO with VBA with Report Writer post goes into the specifics of using ADO with Report Writer and the post below gives an example using ADO with a report which uses temporary tables:

RW - Accessing any SQL data from a Report Example 

 

Hybrid Code Examples

The following examples show an unsupported method of the using Continuum Integration Library to execute pass through Dexterity sanScript.  They show how we can run custom reports using VBA or how we can bypass the 80 character limitation on string calculated fields.

Hybrid - Purchasing Terms & Conditions Example

Hybrid - Cheque Amount in Words Example 

 

As you can see there are plenty options available for using VBA with the Report Writer.  I hope this post helps you get started.

David

03-Apr-2009: Added note about returning Currency values using FomatCurrency() and FormatNumber() .

17-Jun-2009: Added menu navigation for adding a report to Visual Basic.

09-Aug-2010: Added links for articles on returning Currency values.

Comments

  • 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
    October 30, 2008
    Developing for Dynamics GP is full of new stuff today including Using VBA with Report Writer , Using

  • Anonymous
    October 30, 2008
    Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2008/10/developing-for-dynamics-gp-weekly_30.html

  • Anonymous
    May 12, 2009
    The question in more detail is whether it is possible to print "Page: Y/X" or "Page: Y

  • Anonymous
    May 12, 2009
    The question in more detail is whether it is possible to print "Page: Y/X" or "Page: Y of X" on a Report

  • Anonymous
    May 24, 2009
    When the inventory module of Microsoft Dynamics GP was first written the Item Description field was 60

  • Anonymous
    June 04, 2009
    I need to pass a string value back to a report via VBA.  I'm attempting to create a calculated field with an empty constant string value, but it's not succeeding.  Can you describe how a calculated field is set to an empty constant value?  All I see is the ability to set "Constants" to nothing and it adds double quotes to the Expressions Calculated.  Any help is appreciated!

  • Anonymous
    June 09, 2009
    Hi there You are adding the empty string constant correctly.  The two double quotes represents an empty string. David

  • Anonymous
    June 12, 2009
    Thanks for confirming David.  I have one more question for you, if you have an opportunity to respond.  What is the correct way to assign a report field's value through VBA to a calculated field with an empty constant value?  For instance, if I wanted to add quotes around the vendor check name field data when printing a check?  The project I'm engaged in is more complicated, but this is a simple way to demonstrate what I need to do.  The field vendor check name and additional string info would be assigned to the calculated field how?  I've tried several methods that I could think of, but none are working.  Thanks again David!

  • Anonymous
    January 18, 2010
    The comment has been removed

  • Anonymous
    January 18, 2010
    Hi Yoke May Every additional header or footer needs to break on a different field.  If they are defined with the same field, only the first one will be printed. Hope this helps. David

  • Anonymous
    January 18, 2010
    Thanks David. That explained why the footer does not appear. But, as all the footers I created share the same break field, there would be no way I can create this additional footers. There isn't any other fields I could use. May

  • Anonymous
    January 18, 2010
    Hi Yoke May The only way to have other fields to break on if you have exhausted all the fields in the index is to create a manual sort.  This would need to duplicate the fields in the index and then add some additional fields. I will say that you might not be able to achieve what you need.  If you want 3 additional headers or footers to work at the same time, why don't you just use one and put everything you need onto the single header or footer. Hope this helps David

  • Anonymous
    March 08, 2010
    Hi David, I'm trying to export the report customizations to move the package to my production environment.   When I do it without the VBA everything I can export the report and everything is fine.  But, when I try to do it once it's been added to VBA I'm getting an error message.  I've even tried with no code added to the report in the VBA editor. I'm using a french version of GP10 so the direct translation of the error message would be "Cannot find Form RM Blank Document". Any idea what could be going wrong ? Thanks.

  • Anonymous
    March 08, 2010
    Hi Mat It sounds like there could be some sort of corruption with your custom .DIC files or with the .VBA files. You could try exporting all customisations, renaming/moving the files and importing them back. If you are still having issues, you might want to log a support incident. David

  • Anonymous
    August 08, 2010
    Posting by Steve Endow from Dynamics GP Land dynamicsgpland.blogspot.com/.../from-blog-archives-using-vba-in-report.html

  • Anonymous
    September 06, 2011
    Can you call a report from the VBA code?

  • Anonymous
    September 06, 2011
    Hugh, No - only Dexterity can directly invoke a report.

  • Anonymous
    September 06, 2011
    Hi Hugh While Pat is technically correct, there is nothing saying you can't use the unsupported method of calling Dexterity sanScript from VBA.  See the post below: blogs.msdn.com/.../calling-a-custom-report-using-visual-basic-for-applications.aspx David

  • Anonymous
    September 18, 2011
    Hi David, I need to put two tables in the PO report which are sales transaction work and extender ... could you advice. Thanks

  • Anonymous
    September 18, 2011
    Hi Ahmed Please look at the KB 935385 on how to add Extender Fields to reports and the blog article on using VBA to get data from other tables. mbs.microsoft.com/.../KBDisplay.aspx blogs.msdn.com/.../using-ado-with-vba-with-report-writer.aspx David

  • Anonymous
    March 05, 2012
    Very helpful article. Really appreciate it. Thank you!!! :)

  • Anonymous
    March 14, 2012
    I agree with Ken Yong comment. It's really helpful article. Even my company's partner doesn't know if VBA can be used in Report Writer. Now I have another issue in the Report Writer, the section related to visual basic in the Tools menu of the Report Writer is dimmed. To enable it. do I need to install any add-in for dynamics Great Plains? Thanks, Thai Hodac

  • Anonymous
    March 14, 2012
    Thai, If you are in RW and your VBA items (such as the VBA Editor) is disabled, either this user doesn't have access to the module or it isn't registered in GP.

  • Anonymous
    October 19, 2016
    Hi i would like to know how can i hide page footer on all the pages except last page.Actually i m printing a invoice on pre printed paper on every page there is footer message as well as header like date, invoice number. But at the moment its printing invoice totals on each page. I would like it to print only on last page. Please help

    • Anonymous
      October 19, 2016
      Hi JawadThen why don't you use the Report Footer instead of the Page Footer.David
      • Anonymous
        October 19, 2016
        Hi David thanks for reply. Problem with report footer. That my client is using a pre printed invoice and it got blocks for invoice totals at the bottom if the invoice is 2 pages or 3 it prints totals where data finishes. I would like it to print in those blocks. Thats why i am using page footer. P
        • Anonymous
          October 19, 2016
          The comment has been removed
          • Anonymous
            October 19, 2016
            The comment has been removed
          • Anonymous
            October 19, 2016
            The comment has been removed
          • Anonymous
            October 19, 2016
            Hi David Where this coding needs to be placed? under report event? and if yes under which event.and this solution will hide the page footer until the last page come? sorry to trouble you. Thanks once again
          • Anonymous
            October 19, 2016
            The comment has been removed
        • Anonymous
          October 19, 2016
          hi David one thing also i am using VB6 & not a visual studio. thanks
          • Anonymous
            October 19, 2016
            The comment has been removed

  • Anonymous
    October 19, 2016
    Hi JawadYou need to go into the report writer and add the report to VBA and code the events against the report.David

    • Anonymous
      October 19, 2016
      Hi David i added a report to a project must i double click on report and then there are only 8 or 9 events e,g ACTIVATE, ASYNCPROGRESS, DEACTIVATE, INITIALIZE and etc.what my question is must i write under one of those events?sorry i know i m becoming a nag here
      • Anonymous
        October 19, 2016
        The comment has been removed
        • Anonymous
          October 20, 2016
          Hi David Hope you well.i am working on vb past couple of years i know how to add datareport in project but the problem i like to know is. where must i write a code. there are only few event if you can send me a sample code i would really appreciate so i know where to place a code.i designed a complete point of sale software. i worked with report but i didnt write a a code in report event. please a simple coding. that print 3 pages report from a table and it print a page footer only on last page. i would really appreciate. i am going through a bit of problem with client. thanks
          • Anonymous
            October 20, 2016
            please just a sample code. thanks

  • Anonymous
    October 20, 2016
    Hi JawadThere is sample code on the article linked to this post.http://blogs.msdn.com/developingfordynamicsgp/archive/2008/07/17/vba-accessing-any-sql-data-from-a-report-example.aspxDavid

    • Anonymous
      October 20, 2016
      Hi David hope you well. its a same blog you sent me earlier i downloaded Manual Payment....ZIP file. please can you send me a sample code on my email. or here direct link in a reply. i couldnt find anything in that. thanks

  • Anonymous
    October 20, 2016
    Hi JawadThis does not sound like you are using Report Writer and Visual Basic for Applications built-into Dynamics GP.You should not be creating projects, or adding buttons to forms. I do not understand what you are doing.This is a report writer customisation that must be made from report writer by adding the report to VBA.If you need help on this, look at the training materials available from https://winthropdc.wordpress.com/training/David

    • Anonymous
      October 20, 2016
      i am using a visual basic 6. creating a normal project with form and adding a dataenvironment with connection and command as well as datareport. when you run it brings a form with one button on it. when i click on that button it shows report.Please let me know if you understand?
      • Anonymous
        October 20, 2016
        Hi JawadYou can ONLY use VBA inside Dynamics GP to add code to the report.You cannot make these changes from an external VB application as the report writer events are not exposed to VB or Visual Studio, or even Dexterity. ONLY VBA can do this.You will need to make the customisation to the report inside GP using Report Writer and VBA.David
        • Anonymous
          October 20, 2016
          oh ok so if i do this in VBA can i implement or access this in visual basic 6 into my existng project?
          • Anonymous
            October 20, 2016
            Hi JawadIf you are getting Dynamics GP to print the report from your code, then Dynamics GP can print a modified report with your VBA code.This is a separate modification to your VB code. You just print the report. Dynamics GP will handle the rest.Also, please note that blog comments are not the best location for support or training. I suggest using the forums in future.David
        • Anonymous
          October 20, 2016
          ok that sounds goodbut when i m trying to access your links for dynamic GP or trying to download Dynamics GP. it says contents of this page is locked? any idea. i used my microsoft live account
          • Anonymous
            October 20, 2016
            Hi JawadI don't know what links you are trying to download, but if they are PartnerSource or CustomerSource pages, you will need a login from the partner or customer you are working with.David
        • Anonymous
          October 20, 2016
          so there is no easy way out with standar MICROSOFT VISUAL BASIC 6. that i can come around to hide report footer except last page of report or Report footer to print on last page right at the bottom?
          • Anonymous
            October 20, 2016
            Hi JawadThere is no way you can make any changes to GP Reports from an external VB6 application.The only method is to use VBA in the GP Report Writer. Please note that the VBA in GP used is VBA6.David
          • Anonymous
            October 20, 2016
            Hi David One last thing if i use crystal reporting would that help???
          • Anonymous
            October 20, 2016
            Hi JawadIf you use Crystal Reports to generate the report, then you can do whatever you like and don't have to modify an existing Dynamics GP report. I know nothing about using Crystal though, never had to use it.David

  • Anonymous
    October 16, 2017
    Hi David,In GP 2016, can you still use VBA in Report Writer?

    • Anonymous
      October 16, 2017
      Hi RavinYes VBA still works for desktop Dynamics GP. It does not work for the web client.You might find GP Power Tools and it's custom report writer functions helpful as they can work on the web client.David