You'll need to create a sql-expression field to get the most-recent effective date, then use this field in the record-selection formula.
// {%MAX_EFFECTIVE_DATE}
// most-likely you'll need to alias the table in the main report for this to work
(
SELECT Max(effective_date)
FROM price_history
WHERE product_id = price_history_alias.product_id
)
Record-selection formula:
{price_history_alias.effective_date}={%MAX_EFFECTIVE_DATE}
If you only want to see the most recent record, per userID, based on the flightdate, there are a few options. The most efficient way is to use a custom SQL command, but Crystal can do it by retrieving all of the records, and then suppressing the records you don't want to see.
1.Group the report by USERID (Report > Group Expert > Add the USERID field to the group section)
2. Sort the records by FlightDate ( Report > Sort Expert > Add FlightDate and select Descending NOTE: if FlightDate is not a date, we will need to create a formula to convert it into a date first, otherwise Crystal will treat it as a string and 12/2015 will be higher than 1/2016 (for example)
3.Create a running total to count the number of records within each group (This is how we will tell Crystal to suppress the records we don't want)
--Right click "running Total" on the right hand side, select New. Name it whatever you want; under Field To Summarize add FlightDate, change summary to COUNT
Leave Evaluate as "For Each Record"
Under Reset, select On Change of Group and select the group for USERID
The report should look like this
- In the Section Expert, select Details and click the formula button next to "Suppress"
Enter the formula
{#RecountCount} > 1
(Or whatever name your running total has)
Now you should only see one record for each user
Best Answer
I don't have CR in front of me, but there is formatting on the group that is causing this. Something like show by week instead of the actual date. I've had this bite me numerous times. I can be more specific when I get to work tomorrow.
Edited to add: On the insert group dialog, you need to change:
"The section will be printed: for each week" to "for each day".