Formula for showing a specific value 3

[junction123]

Hi

I am using CE 9.0 and Sybase 12.x. I am using the following formula to get the value "SCBX" when certain columns data values are NULL. The result is showing TRUE if the condition is not satisfied. How do I get to show the actual value?

IF {CR_allocfair.xshares}=0
and {CR_allocfair.mincost}=0
and {CR_allocfair.maxcost}=0
THEN {CR_allocfair.tgx}= "SCBX"
ELSE
{CR_allocfair.tgx} = ({CR_allocfair.tgx})


is showing:

Symbol Buy/Sell TradeGroup
WYE SELL True

when it should show

Symbol Buy/Sell TradeGroup
WYE SELL MM

Thanks!
junction123

 

[lbass]

IF {CR_allocfair.xshares}=0
and {CR_allocfair.mincost}=0
and {CR_allocfair.maxcost}=0
THEN "SCBX"
ELSE
{CR_allocfair.tgx}

Please note that if the fields = 0, they are not null--unless you have set "convert null fields to default values" in the report options.

-LB

 

[rosemaryl]

Dear Junction 123,

You stated the fields were null, do you have the Report Options Convert Null Field to default values checked?

Otherwise, 0 and null are two different things:

To correct your actual formula do this:

IF 
({CR_allocfair.xshares}=0
and {CR_allocfair.mincost}=0
and {CR_allocfair.maxcost}=0
)
THEN "SCBX"
ELSE
Totext({CR_allocfair.tgx})

You cannot actually change the value in a table's field, you create a formula to display instead of that field...

The datatype of the Then and Else must agree ... since you are displaying a string for the then, if the CR_Allocfair.tgx field is an integer then you must convert it to text first, otherwise just remove that totext() portion.

If the field needs to be evaluated for nulls and/or 0's you would change it to:

If 
(
    (Isnull({CR_allocfair.xshares}) 
    or 
    {CR_allocfair.xshares} = 0) 
and
    (isnull({CR_allocfair.mincost}) 
    or 
    {CR_allocfair.mincost}=0)
and 
    (Isnull({CR_allocfair.maxcost})
    or
    {CR_allocfair.maxcost}=0)
)
THEN "SCBX"
ELSE
Totext({CR_allocfair.tgx})

Hope that helps.

Regards,
ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[junction123]

lbass,

Thanks for your response. Actually the fields are NULL and not 0. But Crystal does not like it if I give the foll:

IF {CR_allocfair.xshares}=NULL
and {CR_allocfair.mincost}=NULL
and {CR_allocfair.maxcost}=NULL
THEN "SCBX"
ELSE
{CR_allocfair.tgx}

Thanks!
junction123

 

[rosemaryl]

Dear Junction123,

The correct function (see my formula above your post) is:

Isnull({Table.Field}) so with that updated info your formula should be:

If
(
Isnull({CR_allocfair.xshares})
and
isnull({CR_allocfair.mincost})
and
Isnull({CR_allocfair.maxcost})
)
THEN "SCBX"
ELSE
CR_allocfair.tgx}

Keep in mind, as stated earlier if CR_AllocFair.TGX is a numeric field then you will need to convert it to totext using the ToText() funciton.

Regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[junction123]

Rosemary

Thank you so much for your response! Actually I Already tried the foll formula before reading your response!

Now it is getting a little more complicated. The stored proc does return the right output but for some reason crystal does not.

I am using this formula:

IF ((isNULL({CR_allocfair.xshares})
and isNULL({CR_allocfair.mincost})
and isNULL({CR_allocfair.maxcost})
and ({CR_allocfair.symbol}=Previous({CR_allocfair.symbol}))
and {CR_allocfair.true}=Previous({CR_allocfair.symbol}))
and {CR_allocfair.tgx}<> "OOH")
THEN "SCBX"
ELSE
{CR_allocfair.tgx}


Now my report does not show values (xhares, mincost and maxcost) for trade_group = "SCB" and it also does not show
the value "SCBX".

I think my logic is wrong here. This is how the original report looks:

Symbol Buy/Sell TradeGroup xhares mincost maxcost
A SELL SCB 400 23.44 23.45
A SELL SCB


AAPL BUY MM 4,400 65.33 69.11
OOH 500 64.23 68.22
SCB


ABS SELL OOH
SCB 300 29.34 29.35
SCB



and it should be:

Symbol Buy/Sell TradeGroup xhares mincost maxcost
A SELL SCB 400 23.44 23.45
A SELL SCBX


AAPL BUY MM 4,400 65.33 69.11
OOH 500 64.23 68.22
SCBX


ABS SELL OOH
SCB 300 29.34 29.35
SCBX


Mine is:


Symbol Buy/Sell TradeGroup xhares mincost maxcost
A SELL SCB


AAPL BUY MM 4,400 65.33 69.11
OOH 500 64.23 68.22
SCB


ABS SELL OOH
SCB


Mine is not showing any daat for TradeGroup = "SCB" and it also does not display the TradeGroup as "SCBX" for the rows that belong to this TradeGroup.

Is this confusing enough?

Thanks in advance!
junction123

 

[rosemaryl]

Dear Junction123,

Yep.

Actually that makes it clearer ...

Let me get the logic clear ... Is it if any of those fields are null that you need to show the SCBX or only if all of the fields are null?

It really depends upon what is being returned by the SP, because a value we didn't check for was '' (blank).

Now, are those fields numeric or varchar?

You have indeed complicated by adding this:

and ({CR_allocfair.symbol}=Previous({CR_allocfair.symbol}))
and {CR_allocfair.true}=Previous({CR_allocfair.symbol}))
and {CR_allocfair.tgx}<> "OOH")

Why don't you explain exactly what you are trying to do ... do a simple formula for each of those fields to test for a '' so we know what we are dealing with and then we can build up the formula ...

If isNULL({CR_allocfair.xshares}
then 'XSHARES IS NULL'
else
if {CR_allocfair.xshares} = ''
then 'XSHARES IS BLANK'
else
{CR_allocfair.xshares}

We need to see what you get ... try that and let me know what you find ...

ro









Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[junction123]

Rosemary,

Thanks again for your time!

All three fields must be NULL:

xhares, mincost and maxcost. Also, these fields are float.

I am using this formula and am able to get TradeGroup SCBX. But I am missing some values now. I will try again.

IF (isNULL({CR_allocfair.xshares})
and {CR_allocfair.tgx} <> "OOH"
or {CR_allocfair.tgx} <> "SCB"
and isNULL({CR_allocfair.mincost})
and isNULL({CR_allocfair.maxcost})
and {CR_allocfair.tgx} <> Previous({CR_allocfair.tgx})
and {CR_allocfair.true}=Previous({CR_allocfair.true}))

THEN "SCBX"
ELSE
{CR_allocfair.tgx}


I am trying to display the TradeGroup as "SCBX" for those records where it is supposed to be "SCBX" instead of "SCB". The proc returns the data correctly based on the where clause and the conversion. The original datasource has the column width for TradeGroup as char(3). But I am putting this in a temp table which has a width of char(4) for TradeGroup. Will pasting the stored proc help? In the meantime, let me try various combinations based on teh proc logic.


Thanks for your help!
junction123

 

[rosemaryl]

Dear Junction123,

Crystal is very flaky on nulls ... try this:

IF
(
(isNULL({CR_allocfair.xshares})
and isNULL({CR_allocfair.mincost})
and isNULL({CR_allocfair.maxcost})
)
and {CR_allocfair.tgx} in ("OOH","SCB")
and {CR_allocfair.tgx} <> Previous({CR_allocfair.tgx})
and {CR_allocfair.true}=Previous({CR_allocfair.true})
)
THEN "SCBX"
ELSE
{CR_allocfair.tgx}

Does that work?

Regards,
ro

P.S. If your sp isn't too big, go ahead and post, it might help... ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[JackSparrrow]

Junction123

Im not exactly sure exacly what you're trying but the reason why you got it to show TRUE...and not SCBX is because you are not doing a proper comparison. it should be:

IF {CR_allocfair.xshares}:=0
and {CR_allocfair.mincost}:=0
and {CR_allocfair.maxcost}:=0
THEN {CR_allocfair.tgx}= "SCBX"
ELSE
{CR_allocfair.tgx} = ({CR_allocfair.tgx})


Notice the := instead of just the = .
If you do that it should show SCBX!
Hope thats what you're looking for!

JackSparrrow

 

[junction123]

Thank you all for your wonderful help! Rosemary, I had to tweak your script a little, Thanks!

Here is what I finally came up with:

IF
(
(isNULL({CR_allocfair.xshares})
and isNULL({CR_allocfair.mincost})
and isNULL({CR_allocfair.maxcost})
)
and {CR_allocfair.tgx} <> "OOH"
and {CR_allocfair.tgx} LIKE ("SCB")
and {CR_allocfair.true}=Previous({CR_allocfair.true})
)
THEN "SCBX"
ELSE
{CR_allocfair.tgx}

Thanks again!
junction123