how do i get multiple rows output as comma separated items on one line?

q123 · Post by **q123** » Mon Mar 05, 2012 12:23 pm

Hi,
I was after some help getting something like this working....

If I have the following tables....

Parent
^^^^^
int Id
string ParentName

Child
^^^
int ParentId
int ChildName

with the following values...

Parent
ID ParentName
^^^^ ^^^^^^^^
1 John
2 Jane

Child
ParentId ChildName
^^^^^^ ^^^^^^^
1 Jenny
1 Jake
1 Suzy
2 Nancy
2 Mick

And I want to get the report to look like this....

======== Page 1 ==========
Parent Name: John
Children: Jenny, Jake, Suzy
======== End of page 1======
======== Page 2 ==========
Parent Name: Jane
Children: Nancy, Mick
======== End of page 2======

This is easy enough to get working if the children are printed in a table but as I have to replace existing reports I cannot do this. I need to get the children to print as a comma separated list on one line.

Any help would be appreciated.

Post by **Alex K.** » Tue Mar 06, 2012 5:01 am

Hello,

As a way, you can use the Cross-Data component.

Thank you.

q123 · Post by **q123** » Tue Mar 06, 2012 1:27 pm

Hi Aleksey,
This solves the issue but the solution causes another problem. The header band now no longer renders. Any suggestions?
Thanks
Martin

q123 · Post by **q123** » Tue Mar 06, 2012 5:55 pm

Hi Aleksey,
Noticed this solution only partially works. If there are too many items to fit into the container it does not wrap around to a new line. Do you have another suggestion?
Thanks
Martin

Post by **Alex K.** » Wed Mar 07, 2012 2:45 am

Hello,

Another way, you can use addition code in events to prepare a string value in which all values will be contained.

Thank you.

JorisWils · Post by **JorisWils** » Wed Mar 07, 2012 3:54 am

We use Aleksey's suggestion for 3 years now and it always worked perfectly.

Create 1 string variable
Create 2 databands ( no relation )

In databand 1 loop though the data and in the beforeprint-event fill in the string+previous value of that string.
In databand 2 print the variable (and in the afterprint-event make it empty again).

We use this to create a full text out of separate sentences.

In your example you would have the "parents band"
underneat that you would have the "children band" with relation and master component set to the "parents band"
And underneat that "children band" you make a band to print the variable ( set the master component to the "parents band", no relation or datasource)

q123 · Post by **q123** » Wed Mar 07, 2012 7:26 pm

Hi,
I now have this working on my report. One concern I have is that I do not feel that our support team will enjoy having to repeat these instructions to our customers when they want to create a report with a similar layout. Is there any chance of you adding a component that could surface this functionality without the extra complexity. We are obviously not the only user of your product who would appreciate it. Anyway my problem is now resolved.

Thanks to both of you for your help.

Martin

Post by **Alex K.** » Fri Mar 09, 2012 2:19 am

Hello,

We have added your request to our to-do list. Try to add new function for this task in next release in March.

Thank you.

Post by **Alex K.** » Fri Mar 09, 2012 3:54 am

Hello,

We made some improvements in that direction. Solution will be available in release in March.
You can use the following expression:

Code: Select all

{Func.EngineHelper.JoinColumnContent(DataSourceName, "ColumnName", delimiter)}

example:
{Func.EngineHelper.JoinColumnContent(Categories, "CategoryName", ", ")}

Thank you.

JorisWils · Post by **JorisWils** » Fri Mar 09, 2012 6:42 am

Hi Aleksey

That's great news.

Additionally, I'd like to add something.

We concatinate sentences using the method I described above.
BUT... Based on a certain value in a certain column we use different separators.
Sometimes we use a dot, sometimes we use a comma and sometimes we use ";"

Are there any plans on including conditions in this functionality?