Grouping issues 1

[damonkimble]

Using Crystal 10 connecting to MS SQL 2000 via ODBC.

I could do the following with subreports but would prefer to not deal with the complexity and the performence hit involved.

The desired group hierarchy is as follows:
child
|
|--appts
|
|--parties
|
|--presents


with incoming data:
ID,child_name,child_birth,doc_name,doc_loc,appt_dt,child_name_bd,child_party_dt,child_pres
2,Billy,7/19/1997,Dr. Smith,125 Main,12/15/2005,Sally,1/3/2006,Books
2,Billy,7/19/1997,Dr. Smith,125 Main,12/15/2005,Sally,1/3/2006,Clothes
2,Billy,7/19/1997,Dr. Smith,125 Main,12/15/2005,George,1/30/2006,Books
2,Billy,7/19/1997,Dr. Jones,811 First,1/17/2006,Sally,1/3/2006,Books
2,Billy,7/19/1997,Dr. Jones,811 First,1/17/2006,Sally,1/3/2006,Clothes
2,Billy,7/19/1997,Dr. Jones,811 First,1/17/2006,George,1/30/2006,Books


with the results in the form:

Billy   7/19/1997

Appts                                          Parties
--------------------------------------         ----------------------------------------
12/15/2005  Dr. Smith     125 Main              01/03/2006  Sally
01/17/2006  Dr. Jones     811 First                Books
                                                   Clothes
                                                01/30/2006  George
                                                   Books

The first group is obviously based on child (I use child_name & ID), but I'm not really sure where to go next. Any and all help would be appreciated.

thanks as always,
damon

 

[synapsevampire]

Since you want listings alongside each other, you might need to preprocess the data in an SP or View to provide the output required.

Within Crystal you might fill arrays with the columnar data, and then display them. An example might be for the parties:

Group header formula:
whileprintingrecords;
stringvar MyAppts1:="";
stringvar MyAppts2:="";
stringvar MyAppts3:="";

Details formula:
whileprintingrecords;
stringvar MyAppts1;
stringvar MyAppts2;
stringvar MyAppts3;
if not({table.doc_name} in MyAppts) then
(
MyAppts1:=MyAppts1&{table.appt_date} & chr(13);
MyAppts2:=MyAppts2&{table.doc_name} & chr(13);
MyAppts3:=MyAppts3&{table.doc_loc} & chr(13);
// do the same for MyParties
// This appends in the data for each line

Group footer formula (this is where you display:
//The doc addresses
whileprintingrecords;
stringvar MyAppts1;
left(MyAppts1,len(MyAppts1)-1);

The doc names:
whileprintingrecords;
stringvar MyAppts2;
left(MyAppts2,len(MyAppts2)-1);

you get the idea...

You might do this with arrays as well.

-k

 

[damonkimble]

I'd definately be willing to not have the data side by side, but rather have it in a linear fashion.

thanks,
damon

 

[damonkimble]

FYI, I ended up using subreports as it ended up being less complex than using arrays. Also, I'm using a command parameter to choose the person, so it limits the scope of how many times the subreports are done. Thanks to Synapse for the array idea, even though I didn't end up using it.

damon