Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Formula issue?

Posted on 2011-03-06
Medium Priority
396 Views
Hi CR guru

The below formula produces 0.00 if has not value and I cannot find a feature in CR to supress if Zero value. If you amend the formula to produce a BLANK outcome rather than 0.00, what would the formula be? I think the way the formula field formatted is not number hence the feature 'supress value if zero' isn't available?

if Val( {@sum_condition_value} ) <= 0 then
""
else

Local StringVar outstring := {@sum_condition_value};
If Left(outstring ,1) = '-' then
""
Else
CStr(Val(outstring ) * .1, 2)

Thanks heaps
0
Question by:mcse2007
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 6

LVL 101

Expert Comment

ID: 35053691
How large are the numbers?

Can any of the values be NULL?

What is this formula?
{@sum_condition_value}

mlmcc
0

LVL 19

Assisted Solution

GJParker earned 80 total points
ID: 35055254
Try this formula

If {@sum_condition_value} >  0 Then
{@sum_condition_value}/10
Else
0

Thsi shoukld return you the same results but as a number which you can then format and suppress zero values

HTH
0

LVL 7

Author Comment

ID: 35062712
hi mlmcc,  here it is...it is just summing the condition value by bill doc number.

CStr(sum ({Sheet1_.Condition value}, {Sheet1_.Bill#Doc#}),2,'')

------------------------------------------------

hi GJPParket, after ELSE, there is still condition. CR won't allow the formula to be saved because it say that Local String etc...isn't part of the formula but obviously it is.

If {@sum_condition_value} >  0 Then
{@sum_condition_value}/10
Else
0

Local StringVar outstring := {@sum_condition_value};
If Left(outstring ,1) = '-' then
""
Else
CStr(Val(outstring ) * .1, 2)
0

LVL 101

Expert Comment

ID: 35063903
Is that 2 formulas or 1?

In Crystal syntax if that is a single formula it is not part of the formula because the formula ends at the 0.  To continue the formula you need a ; after the 0

This is the only formula you need as stated above by GJParker
If {@sum_condition_value} >  0 Then
{@sum_condition_value}/10
Else
0

You can then format it as a number and suppres 0s in the CUSTOM format.

If you want to do it with strings then
If {@sum_condition_value} >  0 Then
CStr({@sum_condition_value}/10,2)
Else
""

mlmcc

0

LVL 7

Author Comment

ID: 35063977
hi mlmcc, when I use both of the above formula i get "A string is required here"...it is highlighting the Zero (0) after the greater symbol (>).
0

LVL 101

Accepted Solution

mlmcc earned 1120 total points
ID: 35064077
Why are you converting the summary to a string?
Try it this way

If sum ({Sheet1_.Condition value}, {Sheet1_.Bill#Doc#})  >  0 Then
sum ({Sheet1_.Condition value}, {Sheet1_.Bill#Doc#})   / 10
Else
0

Or if you really want to use strings
If {@sum_condition_value} =  "0.00"  Then
""
Else If Left({@sum_condition_value},1) =  "-"  Then
""
Else
CStr(Val({@sum_condition_value})/10,2)

mlmcc
0

LVL 7

Author Comment

ID: 35065952
Hello mlmcc,

What this formula means?

Else If Left({@sum_condition_value},1) =  "-"  Then
""
Else
CStr(Val({@sum_condition_value})/10,2)

Could you break it down please for a novice guy like me.

Thanks
0

LVL 101

Expert Comment

ID: 35065995
There is only 1 formula

If {@sum_condition_value} =  "0.00"  Then
""
Else If Left({@sum_condition_value},1) =  "-"  Then
""
Else
CStr(Val({@sum_condition_value})/10,2)

If the string is 0.00 then display nothing
else test if it starts with a - if so then display nothin
otherwise disply the value

mlmcc
0

LVL 7

Author Comment

ID: 35066036
Ah...what is the below which still part of the formula

Val

CStr

/10,2

Sorry for asking these very basic and annolying questions which I find very intriguing. (which You probably thinking I'm clueless...which you are probably right)
0

LVL 101

Expert Comment

ID: 35066055
CStr(Val({@sum_condition_value})/10,2)

CStr - Convert what is inthe ( ) to a string

Val - Convert the string to a numeric value

/10 - divide the numeric value by 10

2 - convert the string to have 2 decimal places

mlmcc
0

LVL 7

Author Comment

ID: 35066065
What's the logic behind dividing the numeric value by 10?
0

LVL 7

Author Comment

ID: 35066076
I forgot to thank you from your last post...learned again from you. so thank again.
0

LVL 101

Expert Comment

ID: 35066133
I dont know, you were doing it in the original formula.

It is the same as multiplying by 0.1

mlmcc
0

LVL 7

Author Closing Comment

ID: 35066170
thanks mlmcc.........you're a legend !
0

## Featured Post

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
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 â€¦
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a qâ€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
###### Suggested Courses
Course of the Month9 days, 2 hours left to enroll