Solved

Formula to concatenate field items into a string? CR 8.5, Crystal Reports Formula Editor

Posted on 2004-10-10
5
6,213 Views
Last Modified: 2011-10-03
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
Comment
Question by:dtleahy
  • 3
  • 2
5 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
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
 

Author Comment

by:dtleahy
Comment Utility
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
 

Author Comment

by:dtleahy
Comment Utility
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
 

Author Comment

by:dtleahy
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Glad i could help

mlmcc
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now