Solved

Formula issue?

Posted on 2011-03-06
14
392 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
[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
  • Learn & ask questions
  • 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
[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

 
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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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