Is there a restriction on the IF ELSE ladder in Crystal Reports

Question

I have a requirement where for one of the formula fields I need to validate around 600 codes and display an appropriate description for the same. Hence forth there is currently an IF ELSE ladder construct. A sample is shown below:

If {Command.FINL_STR_SCR_CMTY_CD_1} = "N00" Then    "Control age or date entered in D&B files indicates higher risk"
Else If {Command.FINL_STR_SCR_CMTY_CD_1} = "N01"    Then    "Control age or date entered in D&B files indicates lower risk"
Else If {Command.FINL_STR_SCR_CMTY_CD_1} = "N02"    Then    "Business does not own facilities"
....
....

But upon adding further rows, I am unable to make the report work. It is throwing the below error:

Failed to open report Error message: Failed to receive reply from server "xxxxx". Error returned from Windows Sockets API recv(replySize) : 10054.

Reports are configured on Crystal Report 2008. We use ASP pages (Running on IIS) to call the reports.

Need to know if there are any restrictions on the size of the IF ELSE ladder that can be added to the formula fields in Crystal Reports. The report works fine until 567 lines of IF ELSE ladder (approx). But adding further ELSE IF statements causes it to break with the above message.

Thanks, Chandan

Answer 1

You would be MUCH better served to create a table to hold the key and its description, then link this REFERENCE_TABLE.KEY field to the TABLE.FINL_STR_SCR_CMTY_CD_1 field.

Answer 2

While I'm not aware of any limitations as to how many "if" statements you can nest, it is not advisable to nest 600 statements. Not only is it a LOT of work, the margin for error is quite high. And the maintenance factor on a statement like that would be quite cumbersome. Your best bet would be to create a stored procedure, or like @craig suggested, a separate table. The Stored Procedure is a little more flexible than creating a new table. The table has the advantage that you could store all the information in it, like your description.