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 are fields whose values derive directly from a Microsoft Dynamics GP table. You can return only the value of table 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.
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.
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
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
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