Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Selection Formatting Question

Posted on 2005-04-27
13
Medium Priority
?
490 Views
Last Modified: 2008-02-01
Hey all....another question

I am using selection formatting to evaluate a field value and then apply color based upon the field value.  My formual is listed below....

Select CurrentFieldValue
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .50      :crLime
    case is <= .75      :crYellow
    default  

I have a new value 'NB' which is text....How can I incorporate this into my code above...

I have tried

Select CurrentFieldValue
    case = 'NB'      :crRed
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .50      :crLime
    case is <= .75      :crYellow
    default


That didnt work...I am pretty sure its because its text and not a number....Any push in the right direction would be helpful.

Thanks,
bob  
0
Comment
Question by:vmrfadmin
  • 6
  • 5
  • 2
13 Comments
 
LVL 28

Expert Comment

by:bdreed35
ID: 13881080
What is the data type of this field?  It must be text, but then that will not explain how the formula worked before since what you have had to have been numeric data.
0
 

Author Comment

by:vmrfadmin
ID: 13881215
The date type in this field was all numberic.....now it can be numeric or text.......

0
 
LVL 28

Expert Comment

by:bdreed35
ID: 13881237
There can only be one data type, so it must be text now.
Try changing it to this:

if CurrentFieldValue = "NB" then
    crRed
else
Select val(CurrentFieldValue)
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .75      :crYellow
    case is <= .50      :crLime
    default                 :crNoColor
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:vmrfadmin
ID: 13881300
I tried it out......Crystal gives an error message that a

"A number is required here"..........it referes to the "NB"
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 13881320
If it gives you that error than the field must be numeric and it can't contain the value "NB"
Try highlighting the field in the Fields list, right click it and choose Browse Data.
This will give you the data type and the first 500 unique values in the field.
Post the data type and what you found out about the data.
0
 

Author Comment

by:vmrfadmin
ID: 13881357
The field is a formula, and this is the formula....


If  {Budget Amount Computer Services}= 0 and {Computer Services Expenses} = 0 then
    '0'
else
(
  IF {Budget Amount Computer Services}= 0 then
     'NB'
  else
     cstr(({Computer Services Expenses}/{Budget Amount Computer Services}) * 100)
)

The formula will return a numeric value or a text value.......

Does that make sense
0
 
LVL 7

Expert Comment

by:ladylinet
ID: 13881412
Well, problem looks like lays in this part:

  case = 'NB'      :crRed
  case is >= 1    :crRed

 First case is string, second is number.  Change second one to string to:

 case = 'NB'      :crRed
 case is >= CInt('1')    :crRed

Lady Linet
0
 
LVL 28

Expert Comment

by:bdreed35
ID: 13881423
It makes sense but it will actually only return text data like "NB", "0", or the calculation converted to text using the CStr function.

I am not sure why the last code I gave you is not working.  Perhaps we should simplify it for the time being to try and get it working.

Try this for now:

if CurrentFieldValue = "NB" then
    crRed
else
    crNoColor

Let me know if that works
0
 

Author Comment

by:vmrfadmin
ID: 13881565
I  tried the simplified version....

if CurrentFieldValue = "NB" then
    crRed
else
    crNoColor

And it still complains about "A number is required here" .....referring to the NB.

The field that I am applying this formual to can either be a number or string field........

In the example above...I dont understand why Crystal cares if the field value is a number or string.....just evaluate based upon my formula......

bob
0
 

Author Comment

by:vmrfadmin
ID: 13881572
ladylinet.....

I tried your code and have the same problem........It doesnt like that "NB" is a string........instead of a number.

Keep getting the "A number is required here"

bob
0
 
LVL 28

Accepted Solution

by:
bdreed35 earned 2000 total points
ID: 13881596
Bob:
The data may look like it is either numeric or text but the formula can only return one data type.  Because you put quotes around the 0 and used the cstr function, you have converted the numeric data to text.

Are you sure you have this in the font color for the formula and not in the font color for another field?
If you use the formula name instead of CurrentFieldValue do you get the same error:

if {@formula_name} = "NB" then
    crRed
else
Select val({@formula_name})
    case is >= 1    :crRed
    case is > .75       :Color (244,134,11)
    case 0              :crNoColor
    case is <= .75      :crYellow
    case is <= .50      :crLime
    default                 :crNoColor
0
 
LVL 7

Expert Comment

by:ladylinet
ID: 13881601
Hi, Crystal cares if field is string or number, very much so. How can Crystal knows how to compare values like NB and 0? It can if they are both strings.

 It can't be both, so you should choose one, string will be better because you can use both 'NB' and '0'.

 Covert all cases into strings:

    case = 'NB'      :crRed
    case is >=  '1'    :crRed
    case is > '0.75'       :Color (244,134,11)
    case '0'              :crNoColor
    case is <= '.75'      :crYellow
    case is <= '.50'      :crLime
    default                 :crNoColor

Lady Linet
0
 

Author Comment

by:vmrfadmin
ID: 13881714
bdreed35.....You got it.  

I had selected all the formulas at once and inserted the code CurrentFieldValue..........

When I went thru and specified the specific formula and removed the currentfieldvalue it works like a charm.  The downside...I have to setup about 40 of these that specify the formula instead of setting it up once for all of them.

But, considering I have already spent way too much time on this....Its time well spent.

Thanks everyone for helping me.  I really appreciate it!!!!  Some days are harder than others......

Today was a hard one  :)

Thanks.
Bob
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Loops Section Overview
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question