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
14
Medium Priority
?
396 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 101

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

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
LVL 101

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 101

Accepted Solution

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

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 101

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 101

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 101

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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

604 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