The technique that we're going to employ will be to add two Calculated Fields to the report, and then to populate them from database queries at runtime.
For this particular report it might be easier to open the report the long way (from the main Tools > Customize menu); I had difficulties using my usual method of just printing the report and pressing 'Modify'. Something hung up and I had to reload Dynamics. So....
Modify the Report
Click on MicroSoft Dynamics GP > Tools > Customize > Report Writer
When Report Writer opens, click on the Reports button
Locate 'Check With Stub on Bottom - CM' and move it to the right window. Click on it and press the Open button.
In the Report Definition window click on Layout
From the menu across the top, click on Tools > Add Report to Visual Basic
In the design area, click directly on the 'CM Trx Number' field and click Tools > Add Fields to Visual Basic. This will give us the ability to use this field in our code behind.
In the Toolbox window change the dropdown to 'Calculated Fields' and click New. The Calculated Field Definition window will open
In the name field type MemoData, change the Result Type to String.
Choose the 'Constants' tab, change the Type to String and enter 'MemoData' in the Constant field.
Click the Add button, then the OK button.
The new field will now appear in the Toolbox, drag it onto the report. Add a label to identify the field.
Click directly on the new field in the design surface and click Tools > Add Fields to Visual Basic.
Repeat this for the GLData calculated field.
Add VB Code
Click Tools > Visual Basic Editor to switch to the VB Editor
In the Project window, navigate to the CheckWithStubonBottomCM report, double click on it to open it in the editor.
Paste in the code that you see below.
'declare a global variable
Dim strCheckNumber As String
Private Sub Report_BeforeAF(ByVal Level As Integer, SuppressBand As Boolean)
'fires before the 'Additional Footer' (the F1 area)
Dim strGLAccountInfo As String
'simulated data access to look up GL Account info...
strGLAccountInfo = GetGLAccountData(strCheckNumber)
'set the value of a field in the footer
Me.GLDataLASTF1.Value = strGLAccountInfo
End Sub
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
'fires before the 'Additional Header' (the H1 area)
Dim strMemoData As String
strCheckNumber = Me.CMTrxNumber
'do some data access here to look up the memo data...
strMemoData = GetMemoData(strCheckNumber)
Me.MemoData.Value = strMemoData
End Sub
Function GetMemoData(strCheckNumber) As String
'simulate data access
GetMemoData = "data from database"
End Function
Function GetGLAccountData(strCheckNumber) As String
'simulate data access
GetGLAccountData = "Cash account (1000-00-01)"
End Function
View the Report
Switch back to Dynamics and set security to the new report:
Launch the Miscellaneous Check window and admire the new report
