Using formulas in cross-tabs in crystal reports 8.5 1

[envirodata]

Using Crystal Reports for BlackBaud, Version 8.5.
I am attempting to write a formula for a cross-tab and insert it as a row. The first step I took was to open the formatting box and hit New Formula. I used this formula (which had no errors):

If {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Activist" Then " Individual Donor " Else {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} ;
If {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = " Prospect " Then " Individual Donor " Else {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong};
If {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Member" Then "Individual Donor" Else {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong};
If {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = " Staff " Then " Individual Donor " Else {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong};
If {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = " Advocacy Contact " Then " Individual Donor " Else {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong}

Finally, I inserted the formula as a row. However, after doing so, the text Member, Staff, etc. was not replaced by Individual Donor but remained as Member, Staff, etc. Did I put this formula in the wrong place? Any suggestions?

Thank you!

 

[lbass]

What formatting box? You should be creating this in the field explorer or in the crosstab expert->new formula. The syntax should be:

If trim({GfCnCnstncy_1.GfCnCnstncy_1_CodeLong}) in ["Activist", "Prospect", "Member","Staff","Advocacy Contact"] Then
"Individual Donor" Else
{GfCnCnstncy_1.GfCnCnstncy_1_CodeLong}

I removed the extra spaces using trim(). Not sure whether the spaces are actually in the field or whether you were displaying the code that way because it was more visually appealing, but the values wouldn't have matched the field if the field didn't also contain the spaces. You could also have written this formula like the following, but it is unnecessarily long:

If {GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Activist" or
{GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Prospect" or
{GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Member" or
{GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Staff" or
{GfCnCnstncy_1.GfCnCnstncy_1_CodeLong} = "Advocacy Contact" then
"Individual Donor" else
{GfCnstncy_1.GfCnCnstncy_1_CodeLong}

-LB