Grouping formula 1

[Scroller52]

Hi, working with CRXI release 2 on a SQL database.

i have a report that is grouped by 'investor name'

the problem is that in my report i need to consolidate the report for some investors. but their names are different so i created a formula to group by:

if {Main.Combined Investor Checkbox}= "Yes"
then {Main.Combined Investor}
else
{Main.Investor_Name}

I had to put Yes in quotes since Yes by itself was another formula? whats happening is that the report is only picking up for investors that have "Yes" in the Combined Investor Checkbox field. whats going on with the else part of my formula?

 

[lbass]

Try this instead:

if isnull({Main.Combined Investor Checkbox}) then
{Main.Investor_Name} else
{Main.Combined Investor}

-LB

 

[Scroller52]

lbass,

just before you posted that i tried that exact same formula. thanks!

any reason why the 1st formula wouldnt work?

 

[lbass]

Once a field is evaluated, it no longer recognizes nulls, i.e., it assumes that if nulls are not first tested, then they don't exist in the field. Nulls must always be evaluated first.

-LB

 

[Scroller52]

thank lbass!

 

[Scroller52]

I'm using this formula in another report. the layout of the data is the same as the previous report, but the formula doesnt seem to be working correctly. i even simplified the formula to be this:

if isnull({LE.Combined Investor Checkbox})
then {LE.Investor}
else {LE.Combined Investor}

but it doesnt seem to be reading the values in the 'combined investor checkbox'. it is only grouping on investors that have a value for the 'combined investor' field. any ideas as to why it would work correctly in one report and not another?

 

[Scroller52]

also, i see that crystal is reading all of the records correctly '183' investors which is what i'm expecting. however it only shows the 13 that have this 'combined investor' field populated with something.

 

[Scroller52]

i'v figured out that CR isn't reading any value in the 'combined investor checkbox' field.

this was my original formula and it would bring back all of the investors that had a 'combined investor' value.

if isnull({Main.Combined Investor Checkbox})
then {Main.Investor_Name}
else if {Main.Combined Investor Checkbox}="No"
then {Main.Investor_Name}
else {Main.Combined Investor}

if i changed to formula to this:
if isnull({Main.Combined Investor Checkbox})
then {Main.Investor_Name}
else if {Main.Combined Investor Checkbox}="Yes"
then {Main.Combined Investor}
else {Main.Investor_Name}

then it would just sort everyone by 'investor' any reason as to why CR isn't taking into account this 'combined investor checkbox' field?

 

[Scroller52]

i changed the formula to this:

if {Main.Combined Investor Checkbox}="Yes"
then {Main.Combined Investor}
else {Main.Investor_Name}

and this worked for what i wanted. so now my question is why the other formulas did not work?

 

[lbass]

Do you by any chance have "convert nulls to default values" checked in file->report options?

-LB

 

[Scroller52]

wow, lbass that was exactly the problem! even though the new formula produced the same results, i unchecked those boxes and applied the old formula in there. thanks once again!