Details and sums in same row question

[KHerm]

I don't know if this can be done in Crystal Reports 8 or not. A request has been made for a report to appear with both details and sums in one row. This is for a financial aid report where it is important to know individual monetary award amounts and starting and remaining financial needs. I'll see if the output can be displayed here:

person award1 award2 award3 total_awards beg_need end_need
person award1 award2 total_awards beg_need end_need

total_awards, beg_need and end_need are sums per person

award1, award2 and award3 are individual amounts whose total will be total_awards.

Is there a way to do this is Crystal Reports? Or would it be easier to create a view in the database instead? I'd like to not have to add more objects to the database if possible.

Thanks!

KH

 

[lupins46]

Are you saying that the data source has all three fields award1 award2 and award3 as separate named fields?

I assume total awards is the sum of the 3 award fields but how do you calculate beg_need and end_need?

 

[Madawc]

I'd do it by grouping by 'person' and then just showing the group footer. You could use running totals to accumulate the various award fields.

It is also possible to have several sections in the group footer, suppressing all but one depending on conditions you have tested for.



Madawc Williams (East Anglia, UK)

 

[KHerm]

I see my scenario forgot one important fact. There are muliple rows per person for the award amounts (award1, award2, award3). I can't figure out how to get the list of awards to be listed in column arrangement rather than in rows.

KH

 

[lbass]

If you group on {table.person}, then you can accumulate the awards by using formulas like:

//{@reset} to be placed in the person group header:
whileprintingrecords;
stringvar awards;

//{@accum} to be placed in the details section:
whileprintingrecords;
stringvar awards := awards + totext({table.award},0,"") + ", ";

//{@display} to be placed in the person group footer:
whileprintingrecords;
stringvar awards;
left(awards,len(awards)-2)

The total awards you can get by inserting a summary on {table.award} in the detail section. Not sure about the last two fields, since you didn't explain how they appear in the records.

-LB

 

[Madawc]

If the fields award1, award2 and award3 each have just one non-zero entry, then sum them for the group and show the running total.

You can also sum these running totals for the overall award total.

Madawc Williams (East Anglia, UK)

 

[KHerm]

LB, what you suggested is great, and I'm almost there. I have been able to get the across display I need. However, the data is all in one variable and is comma separated, as the coding is set up. Is there a way to get each value in it's own variable? I've already got a routine to grab the award title and the award amount, but I'd like to be able to make them look just like the other totals with their headings (total_awards, beg_need, end_need) that already have titles (labels) and amounts. Those totals were working great, it was the detailed records listed across that was the problem and is almost there.

Right now on the last record of detail, the amount variable looks like this:

1000,2000,1500,10416,3000,1754

and the column heading variable looks like this:

Govt,Schol,Schol,Loan,Schol,Schol

I need to line up the column headings and amounts.

I'm still trying to figure out how your code works, and I can't figure out how to break them out into their own variables.

Thanks!

 

[lbass]

Well, you could replace the ", " with spaces of a certain number, e.g., 5-> " ". Then you would change the display formula to use a "-5" instead of "-2". Or you could use the split function:

if ubound(split({@display},", ")) > N then
split({@display},", ")[N]//replace the N with the number of the element in the comma-separated array.

-LB