Dynamics GP - Report Writer Description fields not showing all characters on reports.

Notes:
Report Writer WILL wrap text if you stretch the field downward, but the default field only shows 50 characters.
Report Writer WILL NOT wrap text if there are no spaces in the data, even if you stretch the field downward.

Issue:
GP Item Description can take 100 characters.
Report Writer Fields are limited to 50 Characters (this is 80 in newer version of gp)
Report Writer Calculated Fields are limited to 40 Characters  (this is 80 in newer versions of gp)

This applies to a number of different fields.

Solution 1: Report Writer functions
Create Multiple calculated fields to chop the description into 40,80,100, and pull all three fields onto the report.
This will cause hard cuts, and may cut a word in the middle

Use the RW_Substring function under user-defined functions

• The format is 
• Functions>User-defined>System
• RW_Substring add
• Item Description add
• start position constant add
• substring length constant add

Do not remove any characters, do not add any other fields or characters

So for example, if i wanted to chop the item description start at character 81, and i wanted my string to be 20 characters long, it would look like

RW_Substring Item Description 81 20

in the report writer editor

For the Check Formats, you can split the PM10300.STRGA255 to replace the STRGA255 and STRGB255 fields on the report. (does not work, cuts cents badly)

Create two calculated string fields with formulas that will look like

• Function Script(RW_Substring PM_Payment_Work.STRGA255 1 60)
• Function Script(RW_Substring PM_Payment_Work.STRGA255 61 60)

Or use the Purchasing>RW_Splitwordamt function

• Create 3 calculated fields
• Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 1)
• Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 2)
• Function Script(RW_Splitwordamt PM_Payment_Work.STRGA255 PM_Payment_Work.STRGB255 75 3)

Or use the RW_ConvertToWordsAndNumbersParse (use this method)
https://blogs.msdn.microsoft.com/developingfordynamicsgp/2010/05/23/announcing-report-writer-function-rw_converttowordsandnumbersparse/

• FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse  cyPrintTotal   (c) Currency ID  0   80   1)
• FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse  cyPrintTotal   (c) Currency ID  0   80   2)
• FUNCTION_SCRIPT(RW_ConvertToWordsAndNumbersParse  cyPrintTotal   (c) Currency ID  0   80   3)

Related links:
http://support.microsoft.com/kb/950780


Solution 2: Report Writer with VBA
Add VB code to connect directly to table, get the full description, then cut the fields  up based on spaces between the words.

I would just pass this to a label field instead to prevent the need to cut based on spaces in the words, but there is also a limit on the label field.

Related links:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/05/25/showing-the-full-item-description-on-invoices-example.aspx


ADO Sample
-----------------------------------------------------
Option Explicit
Dim objRec
 Dim objConn
 Dim cmdString
'Dim cn As New ADODB.Connection



Private Sub Report_BeforeBody(SuppressBand As Boolean)
End Sub
Private Sub report_start()
 Set objRec = CreateObject("ADODB.Recordset")
 Set objConn = CreateObject("ADODB.Connection")
 objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DYNGP;Initial Catalog=TWO;User Id=sa;Password="

 'cn = UserInfoGet.CreateADOConnection
 ' cn.CursorLocation = 3
 '  cn.DefaultDatabase = UserInfoGet.IntercompanyID
 ' cn.Open
 
 objConn.Open
End Sub
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)

 cmdString = "select Source_Document_Number from pop10110 where PONumber = '" + RTrim(PONumber) + "' and itemnmbr = '" + RTrim(ItemNumber) + "'"
 Set objRec = objConn.Execute(cmdString)
 If objRec.EOF = True Then
 xReqNo = ""
 Else
 xReqNo.Value = objRec!Source_Document_Number
 End If
End Sub

Private Sub report_end()
 objConn.Close
End Sub

Ensure that you add all fields you need to VB
Ensure you run GP as administrator or the VB will not save
Call this in the body to execute for each record
ManufacturingOrderReceiptEn.CurrencyM105 is an empty calculated field
You must put the calculated field in the BODY or the appropriate section where the VB code is running