Crystal Reports Formula

[whensley]

I suspect I am making this much more difficult than it is but here is what I am tying to do:

I have a database that that has an employee information table in it. In that table there is a field called department number. I need to to write a formula that will say if the department number is is between 100 and 199 then display the text "A", if it is between 200 and 299 then display the text "B", if it is between 300 and 399 then display the text "C".

I am having a heck of a time getting this to work out.

[jade1977]

select {department.number}
case 100 to 199:"A"
case 200 to 299:"B"
case 300 to 399:"C"

Hope that helps

[whensley]

I keep getting a message that says "A string is required here" with that formula. However, I think the formula is correct. not sure what the problem is....

[jggtz]

If the field is String type then

select {department.number}
case "100" to "199":"A"
case "200" to "299":"B"
case "300" to "399":"C"

[Yudi Purwanto]

Hi wensley

You could try the alternative formula (i'm using basic syntax) :

if ToNumber(department.number) between 100 and 199 then
Formula = "A"
else if ToNumber(department.number) between 200 and 299 then
Formula = "B"
else if ToNumber(department.number) between 300 and 399 then
Formula = "C"
end if

it's seems to be longer, but hope it work 4 you

[jsham]

If the field is String type then

select {department.number}
case "100" to "199":"A"
case "200" to "299":"B"
case "300" to "399":"C"

if ToNumber(department.number) between 100 and 199 then
Formula = "A"
else if ToNumber(department.number) between 200 and 299 then
Formula = "B"
else if ToNumber(department.number) between 300 and 399 then
Formula = "C"
end if

Between the two, the Select Case structure is more elegant, and should include the Default case:

Select {department.number}
  Case "100" to "199":
    "A"
  Case "200" to "299":
    "B"
  Case "300" to "399":
    "C"
  Default:
    ""

[whensley]

Thanks, guys! that worked perfectly! I do have one more question though

I would love to insert a parameter field into this report that lets the user choose either "A" "B"or "C". for instance:

It would be great if the user could input "A" and only pull up those records that are associated with "A". The Select, Case worked perfectly to separate the records, but is it possible to add a parameter that will let the user only choose to view "A" on the report?

[jade1977]

That is easy enough. Create your parameter and then in the select expert

{?parameter} = {department.number}

[whensley]

that sounds easy enough. The department.number is a range of numbers which is why I needed that select, case statement to separate them out.

So I created my parameter, but how do I make that work in the select expert if the department numbers are just a long list of numbers? So for instance, thanks to the select statement I have labeled them as "A" B" or "C" (the formula is @plant_ID and the parameter I just created is called {?plant}, what I need is for the user to choose Either A, B, or C and the report only display the results of the select statement. I hope this is not too confusing.....

[whensley]

Hey never mind! I just worked it out! Thanks so much to everyone who replied!

[jggtz]

Post your solution, to close the thread

[whensley]

Basically what I did was not just add the parameter formula in the select expert but changed the current parameter. Without bogging you down in the details (it is a rather complex report) I already had a necessary date range parameter set up so I just appended a new line to it to filter but department as well as date. Pretty easy stuff...I just wasn't thinking when I initially looked at it.