Selecting data

[inexperienced1]

I need some help with a report

Beloew is an idea of what I am trying to achieve

example Table design

key occurance date
100 1 01/01/02
100 2 01/02/02
100 3 01/03/02
100 4 01/04/02
101 1 01/01/02
101 2 01/02/02
101 3 01/04/02

Parameter field Dateto = 01/03/02

I need to return the max occurance row for each distinct key where the date <= the parameter field.

ie
100 3 01/03/02
101 2 01/02/02

I then need to work out where the Date is before the parameter field

ie
Before After
100 3 01/03/02 0 1
101 2 01/02/02 1 0

And then sum the results

Before After
100 3 01/03/02 0 1
101 2 01/02/02 1 0
1 1

I have tried to group the data and sum the grouped amounts, but this does not work.

I am a &quot;first time&quot; user of crystal so I am sure that an answer is out there.

 

[dgillz]

I did not understand the second part of your question:

I then need to work out where the Date is before the parameter field

ie
Before After
100 3 01/03/02 0 1
101 2 01/02/02 1 0


You have 2 columns after date, Before and After. What do they represent? Please elaborate.
Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[inexperienced1]

I have tried to simplify things. The idea is that there will actually be four columns after the date. Each row being valid in one and only one of the four columns.

I have been able to populate these columns but get stuck when trying to sum the columns.

 

[Naith]

If you cannot right click on the column and insert a summary. Use the 3 formula approach - of which there are several faqs.

//Place in the details section

WhilePrintingRecords;
NumberVar Column1 := Column1 + {Column1Field};
NumberVar Column2 := Column2 + {Column2Field};
NumberVar Column3 := Column3 + {Column3Field};
NumberVar Column4 := Column4 + {Column4Field};

To display the total at the end;

//Place where you want the total to be displayed

WhilePrintingRecords;
NumberVar Column1;

(Repeat the formula for each variable)

To reset the total if you want it for different group levels;

//Place in the group or page header

WhilePrintingRecords;
NumberVar Column1 := 0;
NumberVar Column2 := 0;
NumberVar Column3 := 0;
NumberVar Column4 := 0;

Naith

 

[kenhamady]

I would group by key and sort by occurance in DESCENDING order. Then move all the fields you want to display into the Group header. Suppress the details and group footer.

Now you can sum your column using automatic Running Total Fields set to:
&quot;Sum&quot;
&quot;Evaluate For Each Group&quot;
&quot;Reset Never&quot; Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[inexperienced1]

Thanks Naith. Still need to make some final tests but your solution seems to have worked.