Grouping/Selecting A Maximum Value

[jakecolman]

I thought I knew how to do this but clearly I don't.

My table looks as follows:

DP_DEALID, DP_TYPE, DP_DATE, DP_POSITION

I need to select the DP_POSITION for all DP_DEALID and DP_TYPES with the max date <= a specified date. So for a given DP_DEALID and DP_TYPE I will get just one record - the one with most recent date on or before the specified date.

My report has groups for DP_DEALID and DP_TYPE. My group selection criteria is:

maximum({DP_DATE},{DP_TYPE}) <= CDATE('05-JAN-2006')

The report is still including all record, not just the one with the max date. What did I miss?

Thanks!

...Jake

 

[synapsevampire]

You did it correctly, Crystal will display those that you've selected, but the other rows are still there and will couint in summaries, etc. You can display in the group header or footer using the same criteria in a formula, such as:

maximum({DP_DATE},{DP_TYPE})

Perhaps you'll consider posting your environment and requirements instead:

Crystal version
Database/connectivity
Example data
Expected output

This is Crystal's way of accomplishing this, but I dislike it and never use it.

You're better served to only return the rows required to the report by properly coding on the database itself.

-k

 

[jakecolman]

Crystal Report XI
Oracle 9

I really don't want to use database-side coding with a command object. Crystal has a bug with how it handles parameters. if you specify parameters in both a command-object and in crystal, and the crystal parameters are dynamic, you will get prompted with two different sets of prompt windows. To avoid this, I have removed all my command-object parameters, moved them to crystal, and use crystal-side selection and sorting.

If crystal will always include all records in the summaries, then what does a group selection criteria accomplish?

Is there any way I can filter out and only actually 'see' the single record with the MAX dp_date?

 

[jakecolman]

Guess what? My colleague found a tip elsewhere in this forum that solves the problem. For the record, in that other posting it stated that Crystal is pretty dumb about this and that I should use server-side queries. Having said that, the solution is to spcify the group selection criteria as follows:

{DP_DATE} = maximum({DP_DATE},{DP_TYPE})

This evaluates a boolean condition checking whether the current record's DP_DATE is equal to the max date for the group.

This works!

Thanks for your help!

...Jake

 

[synapsevampire]

Yep, I've posted that solution manyh times, note that you SPECIFIED a solution wherein it MUST be <=, add to that, that the solution you've used STILL returns all rows to the report, test by using a summary function.

-k