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!
asked May 8, 2014 at 14:06 user3616771 user3616771 3 2 2 silver badges 4 4 bronze badgesUnless you need all that data to be retrieved from the database, your report will generate more quickly if you can pull the most recent effective date from database only. It will likely not matter much if you have a small set to work with, but your approach does not scale well.
Commented May 8, 2014 at 15:18 Is pulling only the most recent effective date from the database something I can do in Crystal then? Commented May 8, 2014 at 15:59 @user3616771: Why don't you create a stored procedure which brings you the correct results?? Commented May 8, 2014 at 16:28I do not have access to the database. I figured out the solution. In select expert you can choose to filter by "group." In here I used "effective-date = maximum(effective-date, product)" and it solved the problem.