equivalent to select max(field)... in crystal 2

[beltmanjr]

I'm running into a problem where I need the values from a row in my table where the date field has it's max value.

Usually I would use sql to get this from a database like:
select max(dateField), otherField, otherField2 from table where otherField = '1'

But using a command in crystal causes this simple query to run for hours without result (on the database direct it will take less then 1 minute).

So, can I emulate this behaviour in a clever manner without using a command?

Cheers

 

[dgillz]

Have you never just written a crystal report without a SQL command? Just drag the database objects you want to work with on the report canvas. Insert your maximum, and your record selection formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

Go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date})

If you want the maximum at some group level, use:

{table.date} = maximum({table.date},{table.groupfield})

You need to then use running totals if you are calculating any summaries across groups, as non-group selected records will contribute to the more usual inserted summaries.

Are you literally looking for the most recent date in your entire database? I don't see why a command would take that long. If you need to use a command though, it's better to use it as your entire datasource, rather than linking it, since linking will occur locally and slow the report.

-LB

 

[beltmanjr]

dgillz,
I dont get how inserting a maximum in the canvas of the report will help to select the row required based on an selection of the maximum value for a field over all rows??

lbass!! Great stuff. I never knew about this option! This is different from the 'select expert' and 'sql expression fields' isn't it?

I'll try it in the morning when I'm back at work!!!

Cheers

 

[beltmanjr]

PS the command does go to a rather large database table which must be why it becomes so darn slow. And crystal does some stupid stuff to the sql at times which results in more records to be returned.

I could try it as 1 big command, which means some mean left outer joins with subqueries.....

This solution of a subreport seems to be a good solution for the time being!

 

[lbass]

PS. You would also go to report->selection formuala->RECORD and enter:

{table.otherfield} = "1"

...assuming you want the most recent row where otherfield = "1".

-LB

 

[beltmanjr]

It worked like a marvel

 

[Telemachus]

Gurus....I was running into a same kind of situation..Thanks for the help ..mine worked too