Sort but push zeros or blanks to bottom

[slimshades26]

CR 8.5

I have a formula that turns the string to number so I can choose the formula for the sort. Well this worked to put the numbers in order but it puts zeros in front when we need to see priority 1 first and zeros are not assigned so they go to the bottom.

{@ISPriority} form is: val({TASKS.LOOKUP2})

I made a few other formulas and tried a few things like if it equals zero add 100 but it is not doing anything. Thanks for your help. ss26
ss26

 

[dgillz]

Create a formula field as follows:

If {@ISPriority} = 0 then "ZZZZZ" else {TASKS.LOOKUP2}

Then sort on this formula field. Supress this field if you do not want it displayed. You may want to pick a secondary sort field since everything that evalautes to zero will be at the bottom. Picking a secondary sort field will help control the porder that these items are displayed in at teh bottom.

Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[slimshades26]

I changed the ZZZZs to 0. I had blanks also that were not affected by ZZZZs or the 0. I had to go into the database and make blanks 0 and it works now. It all sounds so simple but what a pain.....Thanks for your quick response. ss26

 

[Naith]

Slim,

If you got stuff in your db which is null, and you switched them to 0, isn't there a possibility that nulls can be fed into the database at a later juncture, and be ignored by the report, when you'd really want them reported on?

To catch the nulls, chuck an extra line on top of dgillz's comment:

If isnull({@ISPriority}) then "ZZZZZ" else
If {@ISPriority} = 0 then "ZZZZZ" else {TASKS.LOOKUP2}

Naith

 

[slimshades26]

That's it! Thanks to you both. Works just right.... ss26