Parsing Long String fields in Extender for using with Report Writer
A few days aback I came across a Partner Forum question where the partner was attempting to add an Extender field to a Report Writer report, not without his share set of challenges.
In their infinite wisdom, the folks at eOne added a trigger to the rw_TableHeaderString Report Writer function which allows them to expose data to Report Writer without having to create alternate versions of a report in their application. In turn, with a few steps outlined in the Extender manual, users can invoke the rw_TableHeaderString as a user-defined function in a string calculated field to retrieve the piece of data needed from an Extender table by passing in the Window ID, the key fields, and the position of the field to retrieve on the Extender window. This is an example from such call:
This is all good! But here comes the issue... Extender Long String fields are 255 characters long and Report Writer string calculated fields support up to 80 characters. The partner tried to use the rw_ParseString Report Writer function to parse the Extender string in various lines as follows:
Of course, when the report was executed it threw an "Error in Equation" error as Report Writer does not support nesting of user-defined function scripts.
At this point, the only option available in order to be able to retrieve a long string and print it on the report is VBA, ADO, and a SQL Server view. The following is the process with references to articles that will help you with each step:
1. Create an Extender view of your data. You may start by reviewing Creating SQL Views of Extender Data over at Developing for Dynamics GP to get an understanding of this process. David Musgrave also outlines a sample view to get you started.
2. Create string calculated fields on your report that will be used to parse the Extender Long String field.
3. Add your report to VBA and add the string calculated fields created in step 2 to the VBA project. Also, add any key fields on the report needed to retrieve the data, i.e., SOP Number.
4. Use ADO to query the view for the information stored and store the data in the calculated fields. You may want to review Using ADO with VBA with Report Writer over at Developing for Dynamics GP for samples on the technique.
While the workaround might seem a bit lengthy, the results will speak for themselves, so don't give up on Report Writer just yet :).
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
In their infinite wisdom, the folks at eOne added a trigger to the rw_TableHeaderString Report Writer function which allows them to expose data to Report Writer without having to create alternate versions of a report in their application. In turn, with a few steps outlined in the Extender manual, users can invoke the rw_TableHeaderString as a user-defined function in a string calculated field to retrieve the piece of data needed from an Extender table by passing in the Window ID, the key fields, and the position of the field to retrieve on the Extender window. This is an example from such call:
Calculated Field: EXTENDER_KEY
Expression Type: Calculated
Result Type: String
Expression: STRIP( SOP_HDR_WORK.SOP Number )
Calculated Field: (C) AdditionalShippingInfo
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 )
This is all good! But here comes the issue... Extender Long String fields are 255 characters long and Report Writer string calculated fields support up to 80 characters. The partner tried to use the rw_ParseString Report Writer function to parse the Extender string in various lines as follows:
Calculated Field: (C) AdditionalShippingInfo_Line1
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 1)
Calculated Field: (C) AdditionalShippingInfo_Line2
Expression Type: Calculated
Result Type: String
Expression: FUNCTION_SCRIPT( rw_ParseString FUNCTION_SCRIPT( rw_TableHeaderString 3107 “EXTRA_SOP_INFO” EXTENDER_KEY 0 1 ) 50 2)
Of course, when the report was executed it threw an "Error in Equation" error as Report Writer does not support nesting of user-defined function scripts.
At this point, the only option available in order to be able to retrieve a long string and print it on the report is VBA, ADO, and a SQL Server view. The following is the process with references to articles that will help you with each step:
1. Create an Extender view of your data. You may start by reviewing Creating SQL Views of Extender Data over at Developing for Dynamics GP to get an understanding of this process. David Musgrave also outlines a sample view to get you started.
2. Create string calculated fields on your report that will be used to parse the Extender Long String field.
3. Add your report to VBA and add the string calculated fields created in step 2 to the VBA project. Also, add any key fields on the report needed to retrieve the data, i.e., SOP Number.
4. Use ADO to query the view for the information stored and store the data in the calculated fields. You may want to review Using ADO with VBA with Report Writer over at Developing for Dynamics GP for samples on the technique.
While the workaround might seem a bit lengthy, the results will speak for themselves, so don't give up on Report Writer just yet :).
Until next post!
MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/
Comments
I had been going through the post on your blog on parsing long Extender fields. I have an Extender field that I am using VBA to bring into the Report Footer. The field is a long string and I wanted to print it in full in the report.
I had created a calculated field called as StmtText1, with type Constant String with value "". I used VBA to move the value to this field.
But, now if I want to add the RW_PARSESTRING function, do I add that function to the same field, i.e. StmtText1 or I have to create another calculated field to Parse StmtText1?
I am confuse because the 1st parameter for RW_ParseString is the field name. Is it possible to have the same field name as the calculated field I am defining?
And for the 2nd parameter, do I put in 255 as the extender field can hold up to 255 characters?
What about the 3rd parameter?
Your kind advise is greatly appreciated.
May
You will need a calculated field for each segment parsed. Unfortunately, there's no easy way to this. So, if your main string is parsed into 4 strings, then you will need 4 calculated fields.
MG.-
Mariano Gomez, MVP
Mark
Certainly another valid option. It just sucks that something this trivial needs a workaround to begin with, because of product limitations.
MG.-
Np logro usar la función que indicas en tu blog. por mi parte yo he creado una función escalar que devuelve cadena de 3 caracteres, tenia la esperanza de poder verla desde el editor de reportes.
esta es la función que cree, podrÃas ayudarme con esto? el ambiente es GP2018
CREATE FUNCTION getTipoOC (@OCNumber as varchar(15))
RETURNS VARCHAR(3)
BEGIN
DECLARE @TIPO AS VARCHAR(3)
SELECT @TIPO = RTRIM(C.STRNG132) FROM TSTST.DBO.EXT01100 A INNER JOIN TSTST.DBO.EXT01103 B
ON A.Extender_RECORD_ID = B.Extender_RECORD_ID
INNER JOIN TSTST.DBO.EXT20021 C ON B.TOTAL = C.LNITMSEQ
WHERE A.Extender_WINDOW_ID = 'OP_COMPRAS'
AND C.EXTENDER_LIST_ID = 29
AND A.Extender_key_Values_1 = @OCNumber
RETURN @TIPO
END