How to Subtotal amounts for a Group taking the most recent records??

[maxi]

Hello,
A monthly report needs to display CompanyName, Actual,Budgeted and Forecasted Amounts per record for all the States. For each CompanyName,Amounts having only the latest Date_Stamp should appear.There may be more than one District per State but a CompanyName can show only once on the report.The report has a Start_Date and an End_Date and is grouped in the following order :
G1= {State}
G2= {District}
G3= {CompanyName}
all the three fields from the same table.The Date_Stamp and other Amounts are contained in another table and there might be multiple enteries against a CompanyName in a given month.
I can use the Maximum({Date_Stamp},{CompanyName}) in the Detail section's supress property to show only the latest records.Problem comes when i try to do a Subtotal of Amount fields by G2{District}.It calculates a total of all the Amounts for a District,irrespective of the Date_Stamp.
Is there any way to create a formula in the Record Selection to filter records at the first stage.
I would be greatful if anyone can help me with this report, since Iam very new to CR8.

 

[kenhamady]

You can't filter them out, but you can create totals that only create the first record of each group. Here are the steps:

1)Group on Company
2) Sort on datestamp in Descending order so the newest is first.
3) Place all of the printing fields on the group header and hide the details.
4) Now add running totals and use the 'evaluate' setting to tell it to only use the first record in each "company" group. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

IF performance is an issue, AND if you are using a client server database (or ODBC) to get at your data, THEN you could also modify the link between the two tables so that you only get the latest amounts for a company.
ELSE go with Ken's solution.

There, the fun of posting with IF THEN ELSE statements...
(Yes, I need more fun in my life) ;-) Malcolm

 

[kenhamady]

Malcolm,

I am not familiar with that technique, myself. How would he do that using the link.

Ken Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[maxi]

Ken,
Thanks a lot for responding.But my problem is to create subtotals for the "{District}" group.
Do you mean that i have to use the evaluate function for every total on the report?? Not all can be a Running Total,since some fields are a variance.

Thanks again for the help.

 

[kenhamady]

>>Do you mean that i have to use the evaluate >>function for every total on the report??

Yep, unless you can eliminate the duplicates some other way they will be in the result set, and will part of any of Crystal's default totals. Crystal doesn't know what the maximum is until it includes all the records in the results, hence the problem.

I am anxious to see how Malcolms suggestion works myself. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Guest_imported]

How about creating a SQL Query source that checks that
each date stamp in the detail table is equal to the MAX date stamp
for that State, District, and Comnay Name?

This way Crystal would only receive the correct records to work on.

hth,
- Ido