I have a report I am running against a Progress DB. My goal is to have the items returned by my query show up in a field as comma seperated values.
e.g.
Students Table
ID - Name
1 - Joe
2- Frank
3 - Susan
4 - Mary
In my report I would have an area with a display such as:
"Current Students: Joe, Frank, Susan, Mary"
In SQL Server I see there are some DB functions to merge the select items, but nothing shows up on the Progress side. Suggestions on how I can accomplish this? (ideally DB-neutral solution)?
2 Answers:
Just an idea with a StringBuilder variable:
Create a new variable with name "MyStringJoiner" of Type java.lang.StringBuilder and set calculation type to NOTHING.
As InitalValueExpression you set: new StringBuilder()
As VariableExpression you set: $V{MyStringJoiner}.append(",").append($F{Name})
Then you can create a new TextField in your summary band or any group footer band and set as TextExpression: $V{MyStringJoiner}.length() > 1 ? $V{MyStringJoiner}.substring(1):"<no items found>"
Here I used just a "AntiIndexOutOfBoundsException-Security" Inline-IfThenElse expression for the case that your query returns no names, then you can't subString the first "useless" comma.
So this solution is completely free of any database syntax and uses just JasperReports logic. Of course it iterates through all your resulting records. But you could delete the DetailBand if you just want to display the concated data in summary/groupfooter.
hth + regards from Dresden/Germany
C-Box
If you are directly using JasperReports library, why don't you just execute the sql query and concatenate column name in rows and just pass the concatenated value as a parameter to the report? You'll be able to show the concatenated value anywhere in your report that way.
