Solved

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

Posted on 2004-10-10
5
6,414 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
ID: 12274236
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
ID: 12283019
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
ID: 12283041
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
ID: 12283528
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
ID: 12283647
Glad i could help

mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports with Yardi error 800a004b 2 134
Ignore parameter if no value entered 22 67
Crystal Reports - Trim Data 7 64
Supress a section of a field 6 19
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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