Combining multiple data rows into one string 1

[bee1333]

I would like to show my data in a single field:
The data looks like:

Team Application
A MS Word
B MS Excel
A MS Access
C Outlook
B Internet Explorer

I would like it to look like:
A: MS Word, MS Access
B: MS Excel, Internet Explorer
C: Outlook

Currently I have in a Sub-Report:
Details:
WhilePrintingRecords;
StringVar ConCatApp;
ConCatApp:= ConCatApp + Trim({Application.applicationName}) + ", "

Report Footer:
(WhilePrintingRecords;
StringVar ConcatApp;
{@ConcatApp})

This returns the last applicationName twice so that I get:
A: MS Word, MS Access, MS Access
B: MS Excel, Internet Explorer, Internet Explorer
C: Outlook, Outlook

I'm not so great with Basic, so I'm not quite sure where I am messing this up. Any help?

 

[lbass]

Your report footer formula should be:

//{@display}:
WhilePrintingRecords;
StringVar ConcatApp;
if len(ConcatApp)>1 then
left(ConcatApp,len(ConcatApp)-1)

Not sure why you are using a subreport for this. You could accomplish the same thing by inserting a group on Team, adding a reset formula to the group header:

WhilePrintingRecords;
StringVar ConcatApp;
if not inrepeatedgroupheader then
ConcatApp := "";

Use your detail formula as is, and then add the above display formula to the group footer.

-LB

 

[bee1333]

Thank you LB.

Team is nested in another report, so I'm using it in a sub report that links to application.

When adding the following formula the last value still appears twice. I know for certain that there should be 8 applications for one team but 9 are showing by repeating the last record value. Any suggestions on how to return the string without repeating the last value?

I don't know if it helps to also mention the applications are actually listed in numbers.

//{@display}:
WhilePrintingRecords;
StringVar ConcatApp;
if not inrepeatedgroupheader then
ConcatApp := "";

 

[lbass]

I believe you didn't add the last formula correctly. You cannot reference the detail formula, but must ONLY reference the variable. So did you try the following exactly? Or did you add the formula {@ConcatApp}?

//{@display}:
WhilePrintingRecords;
StringVar ConcatApp;
if len(ConcatApp)>1 then
left(ConcatApp,len(ConcatApp)-1)

Try this formula without the last two lines which just remove the final comma:

//{@display}:
WhilePrintingRecords;
StringVar ConcatApp;

This is all you really need. If you incorrectly reference the formula that accumulates the values, it will add the last value in again.

-LB

 

[bee1333]

I added it exactly as you had it and copied and pasted and I was still seeing the last value twice.

Since I was linking from team to the main report I didn't create a team group in the sub-report. I realized that I needed to add in the formula in the group footer instead of the report footer of the sub-report so I added the team group.

It all works now! Thank you again!

 

[bee1333]

Sorry I lied. I thought it worked... but sadly the last value is still repeating.

like I mentioned before, i copied your formulas exactly as you had them here. Any Suggestions?

 

[lbass]

I'm guessing you have an old version of the formula still in the footer in addition to the new one. Please remove it if you do. Otherwise, please show the exact formulas you are using and identify the sections in which they are located.

-LB

 

[bee1333]

This is what I have in my sub-report which is linked by teams to the main report:

Sub report grouped on Team:

//Details Section
WhilePrintingRecords;
StringVar ConCat;
ConCat:= ConCat + Trim({Supply.supplyId}) + ", "

//GF Section
WhilePrintingRecords;
StringVar ConCat;

 

[lbass]

Did you make sure you didn't have an old copy of the formula somewhere in the subreport?

If you have linked on the team field and have placed the subreport in a team group secton of the main report, you can place the display formula in the report footer.

Please verify the groups in the main report and subreport and the location of the subreport in the main report.

-LB

 

[bee1333]

I deleted the old sub-report and recreated. I found that the issue of repeating last lines showed when I added the following formula to the section suppression:
isnull({@ConCatApp})

When the suppression formula was removed I no longer had the problem. I changed the suppression formula to: isnull({Application.applicationName})
The replication no longer became a problem.

I'm not understanding the reason why the suppression formula had an effect on the concat formula. Do you have an explanation?

Thank you for your time, efforts, and patience in assisting me. You're a great asset to the forums.

 

[lbass]

Because the suppression formula contains a variable that you are having accumulate. Every time you add that, it will cause another accumulation.

Why don't you just remove rows with null applications in the subreport record selection formula?

-LB

 

[bee1333]

That makes sense.

I don't want to remove rows with null applications because I want to display a "No Applications" message.

Is there another way to do it?

 

[lbass]

You must still be seeing a final comma in your display formula, since you didn't use my suggestion. I'm not sure why you need a suppression formula at all. What would be displaying? You could just format the footer to "suppress blank section" as long as you used my display formula:

//{@display}:
WhilePrintingRecords;
StringVar ConcatApp;
if len(ConcatApp)>1 then
left(ConcatApp,len(ConcatApp)-1)

You should then add a reset formula to the subreport report header so that the variable would be set to "" instead of not executing because the field was null:

WhilePrintingRecords;
StringVar ConcatApp := "";

-LB