Using Variables to list on one line 1

[spcc07]

Crystal 8.5
SQL Server

I have one person who may have many scholarships:
Person_ID Scholarship Amount
1111 Scholar1 1000
1111 Scholar2 1500
2222 Scholar1 1000
2222 Scholar2 1500
2222 Scholar3 2000
3333 Scholar1 1000

What I am trying to do:
I have grouped on the Person_ID and am trying to put each person_ID on the report once and show each of the scholarships.

I have
{@ScholarInit}GH1
Whileprintingrecords;
Shared stringvar scholar1;
Shared stringvar scholar2;
Shared stringvar scholar3;
Shared currencyvar amount1;
shared currencyvar amount2;
shared currencyvar amount3;
scholar1 = 'NONE' ;
scholar2 = 'NONE';
scholar3 = 'NONE';
amount1 = 0 ;
amount2 = 0;
amount3 = 0;

{@scholarSET} Details
Whileprintingrecords;
Shared stringvar scholar1;
Shared stringvar scholar2;
Shared stringvar scholar3;
Shared currencyvar amount1;
shared currencyvar amount2;
shared currencyvar amount3;

If not isnull({scholarshipType.Scholarship_Description}) then

If scholar1 = "" then
( scholar1 = {ScholarshipType.Scholarship_Description} ;
amount1 = {Scholarship.Scholarship_Amount}

else if scholar1 <> {ScholarshipType.Scholarship_Description} and scholar2 = "NONE" then
( scholar2 = {ScholarshipType.Scholarship_Description};
amount2 = {Scholarship.Scholarship_Amount}

else if scholar1 <> {ScholarshipType.Scholarship_Description} and scholar2 <> {ScholarshipType.Scholarship_Description} and scholar3 = "NONE" then
( scholar3 = {ScholarshipType.Scholarship_Description};
amount3 = {Scholarship.Scholarship_Amount}

{@ScholarDisplay} on GroupFooter
Whileprintingrecords;
Shared stringvar scholar1;
scholar1

I am getting NONE when {@ScholarDisplay} is on the GF2 line and I am getting False when I have put it on the details.

 

[lbass]

What do you want the resulting display to look like?

-LB

 

[spcc07]

Student ID Scholar1 Amount1 Scholar2 Amount2
1111 Sch_Name1 1000 SCh_Name2 1500
2222 Sch_name1 1000 Sch_name2 1500

At the far right up top :
Scholar3 Amount 3
2222 SCH_name3 2000

 

[lbass]

You could more simply just insert a crosstab in the report header or footer where you add student ID as the row, Scholarship Name as the column and amount as the summary. If you really want the name to repeat per row, you can add that as your first summary field, using a maximum, while still keeping it as your column field. Then you could go to the customize style tab and choose "horizontal summaries" and show labels.

-LB

 

[spcc07]

I don't want to use the crosstab. The last scholarship should have been 3333, it should not repeat.

Why do my formulas not work to populate the variables and diplay on the report?

 

[lbass]

You're making way more complicated than necessary--why do you not want to use the crosstab?

Anyway, for one thing, assuming this is CR syntax, you have mistakenly used = instead of := when establishing the values.

-LB

 

[lbass]

PS. You could instead create conditional formulas like:

if {table.description} = "Scholarship1" then {table.description} + chr(9)+ {table.amount}

Repeat for the other two. Place the formulas in the detail section and insert maximums on them at the student ID level.

-LB

 

[spcc07]

The scholarships are random. One person would not have any more than 3 scholarships. I understand inserting maximums, but what is my condition to make sure that I get three seperate listed scholarships on my report when I don't know if it is scholarship1 or scholarship2?

 

[lbass]

I'm not sure I follow. Are you saying that the name of scholarship1 might be different for different people? Does the scholarship1 just refer to a sequence?

-LB

 

[spcc07]

Scholarship 1 could be any of 5 or 6 scholarships. It is just blindly getting the first scholarship and amount, then the second one etc and putting them on the report.

I tried the crosstab and it works great, but I am trying to add these scholarships to a rather extensive report.

Thanks,

 

[lbass]

Okay, try this. Create formulas like this:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar array desc := "";
numbervar array amt := 0;
numbervar i := 0;
numbervar j := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar array desc;
numbervar array amt;
numbervar i := i + 1;
numbervar j := count({table.person_ID},{table.person_ID});

if i < j + 1 then (
redim preserve desc[j];
redim preserve amt[j];
desc := {ScholarshipType.Scholarship_Description};
amt := {Scholarship.Scholarship_Amount}
);

Then in the group footer use a set of two display formulas, one for each scholarship instance, that are set up like this:

//{@schol3}:
whileprintingrecords;
stringvar array desc;
numbervar i;
numbervar j;
local stringvar displ := "";

for i := 1 to j do (
if i = 3 then //change 3 to schol #
displ := desc
);
displ

//{@amt3}:
whileprintingrecords;
numbervar array amt;
numbervar i;
numbervar j;
local stringvar displ := "";

for i := 1 to j do (
if i = 3 then //change 3 to schol #
displ := amt
);
displ

-LB

 

[spcc07]

LBass,

Thank you for your help.