Joining a list 2

[ganjass]

Hi All,

I have a report where i have to display a list of names

X
Y
Z
in the details, as X,Y,Z in the group header, will i have to create an array for the elements and then do a join?

thanks in advance

 

[synapsevampire]

The data isn't available in the group header unless you either use a SQL Expression (this is based on the database used) or a subreport.

Your post didn't include anything technical, such as:

Crystal version
Database/connectvity used
Example data
Expected output

An array would be filled AFTER the details displayed, so the report header wouldn't have them available.

If you created a shared array variable in a subreport within a group header section (right click the group header and select insert section below) then you could pass that array for each group to the subsequent group header section.

A SQL Expression creating a subquery would be more efficient.

-k

 

[ganjass]

Ok sorry

Cr8
Sql2000

Ok the data would be as so in the details section

Acme
Xcon
Argon
etc

I want to present the data as Acme,Xcon,Argon etc so that i can format it for documuments.

If i use the group footer i take it i can pass the array elements to that?

Thanks

 

[synapsevampire]

Yes.

In the group header use something like:

Group Header:
Whileprintingrecords;
Stringvar TheNames:="";

Details section formula:
Whileprintingrecords;
Stringvar TheNames:=TheNames++chr(13);

Group Footer (for display):
Whileprintingrecords;
Stringvar TheNames

You don't need an array, however output from a formula is limited to a total of 154 chars in CR 8.5 or less (you thought I was just being a pain about the version mattering didn't you?).

If it exceeds 254 you may want a couple of formulas, let's hear, it gets tricky then.

Since you're using SQL Server 2000, you might also create a View on the database side to concatenate this in advance at the group level, which would prove faster, but it's even trickier.

-k

 

[lbass]

To add to SV's response, I think the detail level formula should be:

Details section formula:
Whileprintingrecords;
Stringvar TheNames:=TheNames+{table.name} + ", ";

...assuming you want a comma after each name but the last. Then the display formula should be:

Whileprintingrecords;
Stringvar TheNames;
left(TheNames,len(TheNames)-2)

-LB

 

[ganjass]

Thanks guys, top notch

 

[ganjass]

One last thing, in excess on 254 characters is almost a certain occurence, how would you split the list between 2 formulas?

Thanks in advance

 

[lbass]

Here's a formula that will give you the first 508 characters or so, and then ignore the rest--so if you have more characters than that, you will need to adapt this further. The code is only slightly adapted from a formula that another contributor here, Naith, developed in a much earlier thread:

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar firstst;
stringvar secondst;

if instr(firstst,{table.name}) = 0 then
if instr(secondst,{table.name}) = 0 then
if secondst = "" then
if len(firstst) + len({table.name} + ", ") > 254 then
secondst := secondst + {table.name} + ", " else
firstst := firstst + {table.name} + ", "
else
if len(secondst) + len({table.name} + ", ") > 254 then
secondst := secondst else
secondst := secondst + {table.name} + ", "
else secondst := secondst
else firstst := firstst;

I changed the variable names, so now you have two display formulas:

//{@firstst}:
whileprintingrecords;
stringvar firstst;
left(firstst,len(firstst)-2);

//{@secondst}:
whileprintingrecords;
stringvar secondst;
left(secondst,len(secondst)-2)

Insert a text box in the report footer, and add {@firstst}, followed by ", " and then add {@secondst}. Format the text box (format->text->common) to "can grow."

-LB