CODEWITHSUNDEEP
crystal-reports
user3616771
user3616771

Reputation: 3

Crystal Reports Filter by Most Recent Date of Field

I have a report I am creating through an ODBC connection. The report includes several invoices, where each invoice has several products. There is also a table which contains all the historical price changes for each product (field: unit-price). Currently there are duplicate product records being pulled, one for each time there was a price change. Therefore, I need to filter my data so that only the most recent unit-price is shown (date field: effective-date). How can I do this via the "Select Expert?"

In short, show the product's unit-price for the most recent effective-date.

Thank you!

Upvotes: 0

Views: 9335

Answers (2)

craig
craig

Reputation: 26262

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}

Upvotes: 0

Siva
Siva

Reputation: 9101

Instead of doing it in select expert. group by effective date and set the ordering as Descending.

Upvotes: -1

Related Questions