Minimum & Maximum Values in Crystal Reports 2008 Column

Question

Say I have this column returned in a command for Crystal:

deposit_no
123
130
125
124
126
127
128
129

and I need to have this in the report title:

Includes deposits between 123 - 130

I've tried a running formula for minimum and maximum and they aren't returning the correct values no matter how I manipulate them. I've tried evaluate for every record, on change of the deposit_no field, etc. I have no grouping on this report.

Edited to add: While I preferred to handle this on the CR side of things, I changed my command to include what mson wrote below. So technically, mson had the correct answer.

Solution 1

create a stored procedure or view that has the information you want. access the stored procedure or view through crystal reports.

crystal reports is a hindrance to properly coding anything.

the unexpected result you are getting may be because the column is not numeric. often, number values are stored as varchar/nvarchar. this is done especially for fields like zipcode/phone number/etc. where the value may be numeric, but you would never do math on them.

in any event, here are the snippets you can use to build in sql server (and then call from crystal)

select min(coalesce(cast(deposit_no as int),0)) as min_deposit from tableA

select max(coalesce(cast(deposit_no as int),0)) as max_deposit from tableA

Solution 2

Came across this while searching for the same thing, and would like to add to SqlACID's answer which does work.

You can do this in your formula editor.

'XX'+totext(Minimum ({YY.Num}), 0, '') + '-XX'+totext(Maximum ({YY.Num}), 0, '')

Comments

[GregD]

I agree with you on CR. I build all of my queries in SQL and use them as commands in CR. I will try your sql on my command on Monday and let you know how it goes. Thanks.

[Mr.J]

This should have been the answer, because it is the easiest way on how to get the minimum value from a field without creating any other objects as well.