Select Expert formula using "Maximum" 3

[urbanhim]

Hi, i'm hoping someone can help with a formula I want for Select Expert.

I am running a report on our Heat database, we have a field which shows order a call was resolved.

At the moment my report is returning multiple records, with the resolve order numbered from 1-10 depending on how many resolves there has been. I need to only show:

A) The latest, ie, highest resolve number, and
B) Where there is no number at all, ie, its not been resolved.

Hopefully you'll understand what I want from my formula attempt which clearly isnt written correctly...


isnull ({Asgnmnt.ResolveOrder}) and maximum ({Asgnmnt.ResolveOrder})


I have tried many variations of this, but cant get it to work.

Many thanks



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[urbanhim]

Come to think of it, I dont even know if this is possible using the type of formula I was thinking of...

Each call has its own CallID, and the maximum needs to be based around that, for instance:

Call ID ResolveOrder
001234 1
001234 2
001234 3

I want to only return line "3" and of course records that do not have a ResolveOrder.

I'm confusing myself the more I think about it!!!



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[CR85user]

Base your selection criteria on only stripping out the nulls.

NOT(ISNULL({table.resolveorder}))

Then within your report suppress the section based on it not being the maximum.

Might be easier if you cannot add a stored proc or command to get the db doing the work.

'J

 

[dgillz]

Conditionally suppress the details section:

if isnull({RESOLVE}) or {RESOLVE}=maximum({RESOLVE},{CALLID}) then false else true

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

http://www.gainfocus.biz

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

 

[urbanhim]

Thank you both for your quick responses, and I am trying your solutions as we speak.

Cheers



UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[lbass]

You could go to report->selection formula->GROUP and use:

isnull({table.RESOLVE}) or
{table.RESOLVE}=maximum({table.RESOLVE},{table.CALLID})

The advantage of this is that if you need to do any calculations across callID, you can insert a running total without having to specifically exclude the non-displayed records, while with suppression you must do that.

You could also handle this by using a command as a datasource, as mentioned earlier, where you limit the records returned to those meeting your criteria.

-LB

 

[urbanhim]

Awesome LB! Thanks

UrbanHim
Crystal XI Report Writer
London

[small]What's the best cure for a hangover?... Heavy drinking the night before!![/small]

 

[dgillz]

LB once again is the MAN!

I have actually thought from time to time that LB is a bot, planted by business objects.

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

http://www.gainfocus.biz

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