Working with report fields

You can use the Value property to return the value of a table field, a Microsoft Dynamics GP calculated field, or a user-defined calculated field. You can also use the Value property to set the value of a user-defined calculated field. For any field, the report event you use must correspond to the report band containing the field.

Table fields

Table fields are fields whose values derive directly from a Microsoft Dynamics GP table. You can return only the value of table fields.

Calculated fields

Calculated fields are defined using table fields, constants and operators. Microsoft Dynamics GP typically uses calculated fields to conditionally print data on a report. You can only return the value of a Microsoft Dynamics GP calculated field. You can set or return the value of a calculated field you added to the report using Report Writer.

Accumulator fields

You can return the values of “accumulator” fields in a report. You can also set the value of calculated fields you add to the report that function as accumulator fields. An accumulator field is a table field or calculated field that performs a specific function in the report, and whose field type is not DATA. The field type for an accumulator field determines its function. For instance, an accumulator field can count the number of occurrences of a field (a COUNT field type), or calculate the sum total of each occurrence of a numeric field (a SUM field type). Use the Report Writer’s Report Field Options window to determine the field type for a selected field.

Field formats

With the exception of composite fields, VBA returns report field values without any formatting. For instance, VBA returns the phone number (701) 555-0100 Ext. 1772 as 70155501001772. Likewise, VBA returns the currency value $10.56 as 10.56000.

To format a report field value, use VBA’s Format function. The Format function allows you to define a format string for a specific field type (string or numeric). The following example uses the Format function to format the fax number string field:

MsgBox Fax
'Displays 70155501001772
'Use the format function to format the string
Format(Fax, "(@@@) @@@-@@@@  Ext. @@@@")
MsgBox Fax
'Displays (701) 555-0100 Ext. 1772

Hiding and showing report fields

Use the field object’s Visible property (field) to hide and show report fields. You can hide any table field or calculated field on a report. However, you can only show fields you’ve hidden using VBA. If invisible, a field’s value is still available to VBA, but will not appear in the report.

The following example uses the Visible property to hide the Total Sales YTD field in the RM Customer Report if the Territory ID field is TERRITORY 1:

Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As _ Boolean)
	If SalesTerritory = "TERRITORY 1" Then
		'Hide the YTD sales amount
		TotalSalesYTD.Visible = False
	Else
		TotalSalesYTD.Visible = True
	End If
End Sub

Returning fields from multiple bands

Since you only have access to field values as the field’s band prints, you’ll likely find it necessary to use module-level variables to store report information from multiple bands. As each band prints, you can store selected values from the band using the module-level variables.

The following example exports data from the Microsoft Dynamics GP Item Price List to a text file. The procedure declares several module-level variables to store report data. It then uses VBA events (the BeforeAH and BeforeBody events) to return field values from their respective bands and set module-level variables:

'Declare module-level variables
Private QTY As Integer
Private ItmDesc, ItmNum, VenName As String
--------------------------------------------------------------------
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand _
As Boolean)
	If CInt(QTYOnHand) < 10 Then
		'Set the module-level variables for fields in this band
		ItmDesc = ItemDescription
		ItmNum = ItemNumber
		QTY = QTYOnHand
	Else
		QTY = 0
	End If
End Sub
--------------------------------------------------------------------
Private Sub Report_BeforeBody(SuppressBand As Boolean)
	'Check the value of the QTY variable
	If QTY <> 0 Then
		'Set the module-level variable for the field in this band
		VenName = VendorName
		'Create a text file. You could also export these to Excel
		Open "ItemQuantities.TXT" For Append As #1
		Print #1, "Item Number: " + ItmNum
		Print #1, "Item Description: " + ItmDesc
		Print #1, "Avail. QTY: " + Str(QTY)
		Print #1, "Vendor Name: " + VenName
		Print #1, ""
		Close #1
	End If
End Sub