Help in Grouping

[joeengler]

HI,

I working on a purchase report that will show the lowest cost, current cost and flag field if increase or decrease along with a %change

The data is in SQL 2 tables {inv_list} and {receipts} with the tables being link on 2 key fields {location} and {part_no}

I have created a report and grouped on {receipts}.{location} and {receipts}.{part_no} and I places summary fields to show max and min of cost, my problem comes in when there are multiple receipts and the lowest cost is in the middle

EXAMPLE:

Part_no Date Cost
1234 01-05-2004 1.034
1234 02-10-2004 1.034
1234 04-10-2004 2.021
1234 12-10-2004 1.068

I need to display the differance between the first cost of the year and current and the differance between highest and current, the current cost is always the last entry in the table.

 

[BobSuruncle]

Hi Joe,
I think that the following will work

1.) Create a formula to go in the Group Header for {Part} and suppress it.
@Reset at Part
WhilePrintingRecords ;
NumberVar frst;
NumberVar lst ;
If NOT InRepeatedGroupHeader then
(
frst := 0 ;
lst := 0 ;
);

2.) In the detail line, insert the following formula and then suppress it.
@First&Last
WhilePrintingRecords ;
NumberVar frst;
NumberVar lst ;
If {yourdate} = Minimum({yourdate},{part}) then
frst := {cost} ;

If {yourdate} = Maximum({yourdate},{part}) then
lst := {cost} ;

3.) In the Group Footer for {Part}, create 2 formulas
@Diff-First&Curr
WhilePrintingRecords ;
NumberVar frst ;
NumberVar lst ;
lst - frst

@Diff-Max&Curr
WhilePrintingRecords ;
NumberVar lst ;
Maximum({cost},{part}) - lst

Sorry I can't test this right now, but I think the concept covers what you want.



Bob Suruncle

 

[Madawc]

You could sort your group by cost rather than date. Or you could use a summary total to get the maximum and minimum for the group.

Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total.. Or else use the Field Explorer, the icon that is a grid-like box.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)