Crystal Equivalent of MAX and NVL

[pnad]

Hello,

What would the crystal equivalent of the following be:

MAX(NVL(field, ' '))

I tried using isnull and field = " " and maximum but they did not work.

Thanks.

 

[lbass]

Do it in two steps. Create a formula:

//{@convert}:
if isnull({table.field} then
"" else
{table.field}

Then create a second formula:

maximum({@convert})

You also have the option of converting nulls to default values in file->report options, in which case the first formula becomes unnecessary.

-LB

 

[pnad]

Thank you lbass.

I tried both options but it didnt seem to work. Table.Field is a string field and I am using Crystal 10.0 - does this change anything?

 

[lbass]

In what sense did it not work? Please explain what you did. You should be creating the formulas in the field explorer->formula->new, and then placing the first formula in the detail section. Then you can either right click on the formula and insert a summary (maximum) or you can add the maximuum formula to the report.

-LB

 

[pnad]

Hello lbass,

I created a formula called 'Explanation' as follows:

If IsNull({BUSINESS_RULE_VIOLATION_VW.EXPLANATION}) then
" "
else
{BUSINESS_RULE_VIOLATION_VW.EXPLANATION}

And then I created anothe formula called 'Exp' as follows:

Maximum({@Explanation})

And I put @Exp in the detail section.

The number of rows returned decreased and populated the exp field with only one value for every record.

 

[lbass]

The field only HAS one value, since it is a maximum at the report level. If you want to see a maximum per some group, you have to add a group condition, as in:

Maximum({@Explanation},{table.groupfield})

If adding the formula changed the number of rows, it probably means you are using more than one table, and the formula was the first reference to the second table. To maintain the same number of rows, change your join to a left join FROM the first table TO BUSINESS_RULE_VIOLATION_VW.

-LB

 

[pnad]

I added the left join and the number of rows did not decrease so that was great !

And then I did this - I changed the @Exp formula to
Maximum ({@Explanation}, {EXPENSE_TYPE.DESCRIPTION}) and put it in the detail section. Now, there is nothing in the Explanation fields. It is blank.

 

[pnad]

Sorry about the earlier message - the number of rows actually increased this time.

And the @Exp field has duplicates.

 

[lbass]

And you're all set? If not, you have to clarify the current problem, and show sample data and another sample of your desired results for the same data.

-LB