Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-10
5
Medium Priority
?
6,783 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 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 101

Expert Comment

by:mlmcc
ID: 12283647
Glad i could help

mlmcc
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

604 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