Using VBA with Report Writer
Most 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:
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.
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.
