Print Page | Close Window

Char and Int in Formula field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14506
Printed Date: 01 May 2024 at 6:42pm


Topic: Char and Int in Formula field
Posted By: venky_115
Subject: Char and Int in Formula field
Date Posted: 27 Sep 2011 at 1:42am
Hello All,
 
I want to divide two amounts in a formulae, for eg: amt1/amt2 but if amt2 is 0 then i should show "N/A" else should show the result.
 
in detail: amt1=100 and amt2=50 then the result should 2
               amt1=50 and amt2=100 then the result should 0.5
               amt1=100 and amt2=0  then the result should "N/A" .. etc
 
Back ground color highlighting also there, depends on the values greater than 7 and less than -7. if i convert this to totext then the hightlighting option is not working.
 
Can anybody suggest me the syntax for this formulae?
 
Thanks in advance


-------------
Regards


Replies:
Posted By: jorrebor
Date Posted: 27 Sep 2011 at 2:36am
didn't try it out but something like

if amt2 = 0 then "NA" else result = amt1 / amt2

hope it helps

on the second issue: you can always convert back to to int with the conversion operators

Posted By: DBlank
Date Posted: 27 Sep 2011 at 3:41am
you will have to convert your result to a string as you have to have a consistent output data type for the formula. Since you want 'n/a' as one output you have to convert the numebr to a string also
 
if table.amount2=0 then 'N/A' else totext((table.amount1 / table.amount2))

Posted By: venky_115
Date Posted: 27 Sep 2011 at 3:49am
yes dblank, i tried this previously but the background color logic is not working as this is string so not able to compare in the back ground formula

-------------
Regards

Posted By: DBlank
Date Posted: 27 Sep 2011 at 3:55am
what "background formula"?
Are you trying to summarize a summary?

Posted By: venky_115
Date Posted: 27 Sep 2011 at 4:31am
no like if the result is >7 background color should yellow else if >-7 then diff color like this, hope you got this

-------------
Regards

Posted By: DBlank
Date Posted: 27 Sep 2011 at 4:49am
you can still do that just not using the value in the formula.
 
if (
if table.amount2=0 then 0 else (table.amount1 / table.amount2)
   ) >7 then cryellow else crothercolor.

Posted By: DBlank
Date Posted: 27 Sep 2011 at 4:54am
or you can just do one calculation
if table.amount2=0 then 0 else (table.amount1 / table.amount2)
and then place this on repot canvas,
right click on it
select Common tab
find the "display string" and open the fomrula editor next to it
add in your formula here
if currentfieldvalue = 0 then 'N/A' else totext(currentfieldvalue)

Posted By: venky_115
Date Posted: 28 Sep 2011 at 9:47pm

ok, thanks for all your replies



-------------
Regards


Print Page | Close Window