Is there any way to JOIN all values of a field in to a Single row text box

[Former Member]

Hi

I have a requirement to show all values in a data field to show in one single text box.

for eg: I have a date field 'EntreeDate' and it has around 30 values in it. '1-Jan-2009' to '30-Jan-2009' total 30 rows.

I want to join all of them and show them in a single row text box in my report...

Any ideas how to achieve this..

thanks in advance..

[former_member260594 · Accepted solution]

Rahul,

You could roll these values up into a string running total using formulas similar to the following;

In the details section

WhilePrintingRecords;

Stringvar a:= a + totext() + chr(32)

In the footer

WhilePrintingRecords;

Stringvar a

You would not be able to display this at the beginning of the report though as it has to process the details data first.

Another option is to create a crosstab where the data field is inserted for the columns and set to print for each day (in the group options of the crosstab columns). Take out the grid lines and this would print all the dates in a row in a group or report header or footer section.

Edited by: Graham Cunningham on Mar 11, 2009 11:55 AM

[Former Member · Accepted solution]

Thanks for the response..

I did not get the 'string running total' part.

Could you explain how to achieve that.

In runningtotals there are options for counts, max, Nth largest..etc, none worked for me.

I appreciate ur help..

[former_member260594 · Accepted solution]

This would be a manual running total consisting of 2 formulas;

In the details section

WhilePrintingRecords;

Stringvar a:= a + totext() + chr(32)

In the footer

WhilePrintingRecords;

Stringvar a

The first formula in the details section assigns the current datafield value to the variable along with a space.

The second formula is used to display the variable and must be located in a section below the details such as a group footer or report footer.

[Former Member · Accepted solution]

bulls eye...

its working fine Graham.. thanks

one more concern here... if the data range for 'Entreedate' is more.. the second formula is throwing an error saying "string can be atmost 65534" chars long... any soluton for this?

[former_member260594 · Accepted solution]

That would be a limitation of the size of a string variable. You could however put an if statment in there that assigns the values to a new string variable if the length of the first one reaches 64k.

WhilePrintingRecords;

Stringvar a;

Stringvar b;

if length(a) < 64000 then a:= a + field else b:= b + field