Using VBA with Report Writer


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 (56)

  1. As a follow on to the Using VBA with Report Writer post, I would like to discuss using ActiveX Data Objects

  2. Developing for Dynamics GP is full of new stuff today including Using VBA with Report Writer , Using

  3. The question in more detail is whether it is possible to print "Page: Y/X" or "Page: Y

  4. The question in more detail is whether it is possible to print "Page: Y/X" or "Page: Y of X" on a Report

  5. When the inventory module of Microsoft Dynamics GP was first written the Item Description field was 60

  6. Modifying a Report says:

    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!

  7. David Musgrave says:

    Hi there

    You are adding the empty string constant correctly.  The two double quotes represents an empty string.

    David

  8. Modifying a Report says:

    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!

  9. Tang Yoke May says:

    David,

    Is there a limit to the additional Header or Footer that can be added to a Report?

    I had added extra 3 footers to the standard SOP Invoice Form (Existing Form = 5 footers), making a total of 8.

    I added VBA codes under Report_BeforeAF. When debug, I find that I can only see up to Level 6 and can never get Level 7 and 8 which I had added. Both Level 6 and Level 7 are using the same "Print when field change" which is Item Line Sequence. By right, whenever an item line change, all levels 6, 7 and 8 should be activated.

    Appeciate your advice.

    Thanks

    Yoke May

  10. David Musgrave says:

    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

  11. Tang Yoke May says:

    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

  12. David Musgrave says:

    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

  13. Mat B. says:

    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.

  14. David Musgrave says:

    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

  15. David Musgrave says:

    Posting by Steve Endow from Dynamics GP Land

    dynamicsgpland.blogspot.com/…/from-blog-archives-using-vba-in-report.html

  16. Hugh says:

    Can you call a report from the VBA code?

  17. Patrick Roth [MSFT] says:

    Hugh,

    No – only Dexterity can directly invoke a report.

  18. David Musgrave says:

    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

  19. Hi David,

    I need to put two tables in the PO report which are sales transaction work and extender … could you advice.

    Thanks

  20. David Musgrave says:

    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

  21. Ken Yong says:

    Very helpful article. Really appreciate it.

    Thank you!!! 🙂

  22. Thai Hodac says:

    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

  23. Patrick Roth [MSFT] says:

    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.

  24. Jawad Rizvi says:

    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

    1. Hi Jawad

      Then why don’t you use the Report Footer instead of the Page Footer.

      David

      1. Jawad Rizvi says:

        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

        1. Hi Jawad

          You could use VBA to supress the Page Footer section until the current line number reaches the maximum line number for the document. You would need code to run on the Report Header to read the table and store the line number in a variable. Then you can set a flag once the number is reached to stop the page footer being suppressed.

          David

          1. Jawad Rizvi says:

            please can you help with the code i am banging my head past couple of days and i have to deliver it to client by end of today. i ll really appreiciate . thanks

          2. Hi Jawad

            There is an example on reading data from SQL. So just use that to get the last line item sequence number and store the result in a variable declared at the top of the object (not in an event). Set a second Boolean variable to suppress the PF section.

            Once the body reaches the stored line item sequence, clear the Boolean variable and the PF should print.

            David

          3. Jawad Rizvi says:

            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

          4. Jawad Rizvi says:

            Thanks David so this code will be written not in a report event basically? it ll be written under command button object because i am displaying my report from there. when you say line item sequence its bof and eof?
            please let me know if i am right? thanks

        2. Jawad Rizvi says:

          hi David one thing also i am using VB6 & not a visual studio. thanks

          1. Hi Jawad

            This method is for VBA. Visual Studio tools cannot access GP report events.

            You will need events on Report Start and Report End to open and close the connection to SQL.

            Event on Report Header to execute a SQL query to get the max(Line Item Sequence) and store it in a variable. Also to set the suppression Boolean variable.

            Event on body to compare current line with maximum value and if it is greater than or equal to, clear the suppression variable.

            Event on Page Footer to supress based on suppression variable.

            Look at example http://blogs.msdn.com/developingfordynamicsgp/archive/2008/07/17/vba-accessing-any-sql-data-from-a-report-example.aspx

            David

  25. Hi Jawad

    You need to go into the report writer and add the report to VBA and code the events against the report.

    David

    1. Jawad Rizvi says:

      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

      1. Hi Jawad

        Please read the blog article, it explains how to add the report to VBA.

        Once a report has been added, an event is available for each section of the report.

        You will need add the fields to VBA so you can get the header key fields for the SQL query and to obtain the Line Item Sequence to compare against.

        David

        1. Jawad Rizvi says:

          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

          1. Jawad Rizvi says:

            please just a sample code. thanks

    1. Jawad Rizvi says:

      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

      1. Hi Jawad

        You can load the sample files for Manual Payments via Customization Maintenance and then view the sample code in that example.

        There is also code on the post https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/10/29/using-ado-with-vba-with-report-writer/ which is also linked to this article.

        Use the v10.0 code for any version from v10.0 onwards.

        David

        1. Jawad Rizvi says:

          i tried this code but it does not trigger at all even i tried through F8 (line by line). i created a new project and added one dataenvironment and one datarport, added one connection and one command linked directly to a table. created one button on main form to show datareport. it shows 3 pages report and print footer on every page. please help

  26. Hi Jawad

    This 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

    1. Jawad Rizvi says:

      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?

      1. Hi Jawad

        You 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

        1. Jawad Rizvi says:

          oh ok so if i do this in VBA can i implement or access this in visual basic 6 into my existng project?

          1. Hi Jawad

            If 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

        2. Jawad Rizvi says:

          ok that sounds good
          but 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

          1. Hi Jawad

            I 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

        3. Jawad Rizvi says:

          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?

          1. Hi Jawad

            There 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

          2. Jawad Rizvi says:

            Hi David One last thing if i use crystal reporting would that help???

          3. Hi Jawad

            If 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

  27. Ravin Sankardayal says:

    Hi David,
    In GP 2016, can you still use VBA in Report Writer?

    1. Hi Ravin

      Yes 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

Skip to main content