dtleahy
asked on
Formula to concatenate field items into a string? CR 8.5, Crystal Reports Formula Editor
Hi,
Re: Crystal Reports Formula Editor
I have created a subreport on a Crystal Reports 8.5 report, based on a "stored procedure" (MS Access Proc query) "qryAllAliasNamesByGID". The query returns records that have only 1 field each: a string that is the name of a color.
For example, let's say I returned 3 records, yielding the following 3 strings:
Crimson
Ruby
Burgundy
What I want on the report would be this concatenated string, as a single line of text:
"My colors are: (Crimson, Ruby, Burgundy)"
========================== ========== =
1.) I don't know how to step through all the returned records.
2.) I don't know if any of this Crystal Reports Formula Editor (basic) syntax is correct:
Dim strFinal as string
Dim strAliases as string
While NOT({qryAllAliasNamesByGID }.EOF) 'The formula checker throws an error at this line, says: "This field name is not known"
strAliases = {qryAllAliasNamesByGID.str GroupName} & ", "
Wend
strAliases = Trim(strAliases)
'kill the final comma:
If Asc(Right(strAliases,1) = 44 Then 'last character is a comma
strAliases = Left(strAliases,(Len(strAl iases) -1))
End If
strFinal = "My Colors are: (" & strAliases & ")"
========================== =
Thanks VERY MUCH, in advance, for any help. Oh, and 500 great big points...
-Dennis
Re: Crystal Reports Formula Editor
I have created a subreport on a Crystal Reports 8.5 report, based on a "stored procedure" (MS Access Proc query) "qryAllAliasNamesByGID". The query returns records that have only 1 field each: a string that is the name of a color.
For example, let's say I returned 3 records, yielding the following 3 strings:
Crimson
Ruby
Burgundy
What I want on the report would be this concatenated string, as a single line of text:
"My colors are: (Crimson, Ruby, Burgundy)"
==========================
1.) I don't know how to step through all the returned records.
2.) I don't know if any of this Crystal Reports Formula Editor (basic) syntax is correct:
Dim strFinal as string
Dim strAliases as string
While NOT({qryAllAliasNamesByGID
strAliases = {qryAllAliasNamesByGID.str
Wend
strAliases = Trim(strAliases)
'kill the final comma:
If Asc(Right(strAliases,1) = 44 Then 'last character is a comma
strAliases = Left(strAliases,(Len(strAl
End If
strFinal = "My Colors are: (" & strAliases & ")"
==========================
Thanks VERY MUCH, in advance, for any help. Oh, and 500 great big points...
-Dennis
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Addendum:
I also tried Step 1 above, with the Main report open, creating the formula in the main report. I tried to create the formula in step 2 both in the main report, and in the subreport...
Same error...
I also tried Step 1 above, with the Main report open, creating the formula in the main report. I tried to create the formula in step 2 both in the main report, and in the subreport...
Same error...
ASKER
OK, I got it. Your answer was off by a single semicolon.
Should have been:
In the detail section add another formula
Name - ConcatStrings
shared stringvar MyString;
if MyString = "" then
Mystring := {Table.FieldWithString}
else
Mystring := MyString & ", " & {Table.FieldWithString};
""
========================== =
Thank you very much for your help. 500 points!
Should have been:
In the detail section add another formula
Name - ConcatStrings
shared stringvar MyString;
if MyString = "" then
Mystring := {Table.FieldWithString}
else
Mystring := MyString & ", " & {Table.FieldWithString};
""
==========================
Thank you very much for your help. 500 points!
Glad i could help
mlmcc
mlmcc
ASKER
I can't get your solution to work...
Step 1: with my subreport open, created the DeclVars formula, placed an instance in the report header of my subreport
Step 2: tried to create the ConcatStrings formula,as follows, but got an error while checking syntax:
=========================
shared stringvar MyString;
if MyString = "" then
Mystring := {qryAllAliasNamesByGID.str
else
Mystring := MyString & ", " & {qryAllAliasNamesByGID.str
""
=========================
Note:
I have an existing report (staying with the example of colors) that returns "Red" with a full report about all the fields related to Red, and a photo of Red. This data came from a query that includes multiple "Red Items", where each record returned has data for an additional item on a detail line of the main report.
I am using a query to return records for the report. It was working fine, displaying all the data regarding "Red". Now I want to enhance the report, and add a list of the aliases of Red to my report. To get the list of aliases to Red (which are individual records in a table), I figured I had to leave my existing report alone, and add a subreport to show the concatenated string from a different query. In terms of formatting on the report, a subreport will work out fine. (In other words, I don't need that alias data to show up in preexisting detail lines.)
Can you tell me where I'm going wrong?
TIA,
Dennis