Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6854
  • Last Modified:

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.strGroupName} & ", "   
Wend

strAliases = Trim(strAliases)
'kill the final comma:
If Asc(Right(strAliases,1) = 44 Then 'last character is a comma
    strAliases = Left(strAliases,(Len(strAliases) -1))
End If

strFinal = "My Colors are: (" & strAliases & ")"

===========================

Thanks VERY MUCH, in advance, for any help. Oh, and 500 great big points...

-Dennis
0
dtleahy
Asked:
dtleahy
  • 3
  • 2
1 Solution
 
mlmccCommented:
You cannot loop through the recordset.

Try this

In the report header insert a formula
Name - DeclVars
shared stringvar MyString := "";

In the detail section add another formula
Name - ConcatStrings
shared stringvar MyString;
if MyString = "" then
  Mystring := {Table.FieldWithString};
else
  Mystring := MyString & ", " & {Table.FieldWithString};
""

In the Report Footer add another formula
Name - PrintLine
shared stringvar MyString;
if MyString = "" then
  "No recordset Returned"
else
  "My Colors are: (" & MyString & ")"

mlmcc
0
 
dtleahyAuthor Commented:
Thanks to mlmcc for the reply.

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.strGroupName}; 'here I get an error when I check: "The remaining text does not appear to be part of the formula"
else
  Mystring := MyString & ", " & {qryAllAliasNamesByGID.strGroupName};
""
=========================

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
0
 
dtleahyAuthor Commented:
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...
0
 
dtleahyAuthor Commented:
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!
0
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now