Crystal Reports Selection Formula

Question

Does anyone know how/if you can use a MIN() in selection formula for a Crystal Report?

Im wanting to do this:

    Dim sql As String = ""
    Dim startDate As Date = CDate(_startDate.ToString(_dateFormat))
    Dim endDate As Date = CDate(_endDate.ToString(_dateFormat))

    sql = "min({" & tableName & "." & dateFieldName & "}) <= " & startDate & " AND {" & tableName & "." & dateFieldName & "} <= " & endDate & ""

    Return sql

Answer 1

Can you use the minimum() function? It's referenced in Crystal's help file.

Answer 2

Are you trying to aggregate data in your selection formula, and then filter data based on that aggregation? I doubt that would work (could be wrong, haven't tried). I'd suggest either:
1. feeding Crystal pre-filtered data
OR
2. including your selection formula as a second dataset and link the 2 tables together once they are both in Crystal.

Answer 3

Yes, you can use Crystal's aggregation functions (minimum, maximum, etc.) in a GROUP selection formula. But, it's probably a bad idea on a very large dataset. The reason is (and this goes for record selection as well) that using Crystal functions in selection formulas will most likely not carry over to the actual SQL query that Crystal generates and sends to the database. This means that the selection will not take place on the database server and the ENTIRE dataset will have to get sent back to Crystal and then the local machine has to chug through it to filter out the rows/groups that don't meet the selection criteria.

Instead, use a SQL Expression. You can use the expression to return the minimum date from a subset of records and use that in the selection formula instead. These will get included in the SQL query that Crystal sends to the database.