How to concatenate values from rows to a comma separated list

[phantastes]

Hi,

Would anyone know how to achieve the following in iReport:

 

Row                Value

1                       11111

2                      22222

3                       33333

4                       44444

Group Total: 11111,22222,33333,44444

 I need to take the value in each record and concatenate this value to the previous value, creating a comma separated list that will be displayed at the bottom of the group. Is this even possible. I'm guessing I need to handle this with a Variable somehow.

Any ideas or suggestions are very welcome!

Thank you

[cbarlow3]

I had a little success with creating a string variable that concatenates a string value from each detail.  Here's my mostly useless SQL command I used to generate the test data without having to worry about connecting to any real data in my database:

WITH sampleValues (value) AS (Values ('11111'), ('22222'), ('33333'), ('44444'))
SELECT
  sampleValues.value as value
FROM
  sampleValues

I then created a variable called $V{AllDetails} as data type String with a value expression of $V{AllDetails}+$F{VALUE}+','

I dragged that variable onto the summary band and got:

11111,22222,33333,44444,

The final comma is annoying, of course, but my first few attempts to get rid of the final comma were unsuccessful.  I thought of a few approaches, but can't get them to work without more tinkering.

Carl

[phantastes]

Hi Carl,

Thank you for that quick reply!

Would you be able to expand a bit on what you set the other Variable properties to, such as the calculation etc.

All the best,

Magnus

[cbarlow3]

Sorry, I was originally going to post the JRXML, but that might be overkill in this case.  I set Variable class to java.lang.String, Calculation to Nothing, Rest type to Report, Increment type to None, Variable Expression to $V{AllDetails}+$F{VALUE}+',', and Initial Expression to ""   (two double quotes next to each other with no space in between).  I find that if I don't initialize it, it outputs:

null11111,22222,33333,44444,

Carl

[phantastes]

Thanks,

I'll give that at try after this meeting.

[phantastes]

That's awesome! It worked.

I also added a check to make sure not to add duplicate numbers, plus making sure there is no comma at the end:

 

Thank you so much for you help!

Magnus

 

Code:

$V{idList}.equalsIgnoreCase("")?$V{idList}.contains($F{id}+"") ? $V{idList}:$V{idList}+$F{id}:$V{idList}.contains($F{id}+"") ? $V{idList}:$V{idList}+","+$F{id}

[srkr]

Used the below code.. it will elimates the duplicates..

($F{Id} == null || $F{Id}.equalsIgnoreCase("")) ? $V{Idlist} :

$V{Idlist}.equalsIgnoreCase("") ? $V{Idlist}+$F{Id}+"," :

($V{Idlist}.contains($F{Id}+","))? $V{Idlist} : ($V{Idlist}+$F{Id}+",")

 

Also to remove comma at end we can change the Text Field expression to

$V{Idlist}.substring( 0, $V{Idlist}.length() -1)