subreports - comma delim output 2

[ksnpc]

Hi, I was wondering if it's possible to have the output of a subreport be returned as a comma delimited list instead of a column. Any ideas on how to do this??

I am using CR 9 with an ODBC database.

Thanks!

 

[bdreed35]

I can think of 2 ways to do this:

1.Add a text object to your detail section of your subreport.
Add each of your fields to the text object, seperating the fields with a comma.

2.Create a formula that will return a concatenated, comma delimited string in your detail section. You will need to use the ToText function to convert any non character fields to string.
Something like this:

{table.stringfield1} + "," +
ToText(table.numberfield,0,"") + "," +
{table.stringfield2}

~Brian

 

[ksnpc]

Brian, thanks for the suggestions. But I actually only have one field being returned. For example, the subreport below is County and can return multiple values for each management site:

Management Site Acres County
Rock River Corridor 14254 Pulaski
Rockcastle
Jackson

I would rather have the return look like:

Management Site Acres County
Rock River Corridor 14254 Pulaski, Rockcastle, Jackson

In this example it's not a big deal, but sometimes I have subreports that return many more values and it would be really helpful if I could display them more like a paragraph than a column.

 

[synapsevampire]

Brian's suggestion holds, but since you have multiple values, concatenate them at the group level in the subreport and then pass them back:

In the subreport:

Group Header 1 Formula:
whileprintingrecords;
shared stringvar TheCounties:="";

Detail level:
Group Header 1 Formula:
whileprintingrecords;
shared stringvar TheCounties;
TheCounties := TheCounties +{table.county}+",";

Back in the main report AFTER the subreport has fired for this group level you could display using:

Display Formula:
whileprintingrecords;
shared stringvar TheCounties;
left(TheCounties,len(TheCounties)-1)

-k

 

[ksnpc]

Ah, I think it's finally sinking in. That should take care of my problem. Thanks to both of you!

 

[ksnpc]

Just wanted to post another solution that was suggested by T. Howard (Using CR 8.5, but it also works with 9).


In the subreport, make a formula like this:
global stringvar FirstCounty;
global stringvar CountyList1;
global stringvar CountyList2;
global stringvar LastCounty;

// first put the first and last Counties in its own string so commas come out right
if onfirstrecord = true then FirstCounty := {D_COUNTY.COUNTY_NAME}
else if onlastrecord = true then LastCounty := {D_COUNTY.COUNTY_NAME}
else
// start adding up the Counties
//first make sure the string isn't too long - otherwise an error will occur
if length(CountyList1)< 210 then
CountyList1 := {D_COUNTY.COUNTY_NAME} + ", " + CountyList1
else
//if the first string is long, then start building a second string
if length(CountyList2)<210 then
CountyList2 := {D_COUNTY.COUNTY_NAME}+ ", " + CountyList2
;

if length(LastCounty) > 0 then
if length(CountyList2) > 0 then
FirstCounty + ", " + CountyList1
else
if length(CountyList1) > 0 then
FirstCounty + ", " + CountyList1 + "and " + LastCounty
else
FirstCounty + " and " + LastCounty
else
FirstCounty
;

Then in the section expert suppress -> onlastrecord = false
Also in section expert check 'suppress blank section'