Solved

Formula issue?

Posted on 2011-03-06
14
389 Views
Last Modified: 2012-08-14
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
Comment
Question by:mcse2007
  • 7
  • 6
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
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

by:GJParker
GJParker earned 20 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

by:mcse2007
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 100

Expert Comment

by:mlmcc
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

by:mcse2007
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 100

Accepted Solution

by:
mlmcc earned 280 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

by:mcse2007
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
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

by:mcse2007
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 100

Expert Comment

by:mlmcc
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

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

Author Comment

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

Expert Comment

by:mlmcc
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

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IF THEN Statement not working 3 39
Crystal suppressing if sub report empty 8 60
New and Previous Value in Crystal Report 8 53
Crystal Reports 13 5 44
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now