Print Page | Close Window

Using ROUND and ToTEXT

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=19649
Printed Date: 06 May 2024 at 10:56pm


Topic: Using ROUND and ToTEXT
Posted By: bal1
Subject: Using ROUND and ToTEXT
Date Posted: 13 Jun 2013 at 2:27am
Hi,

I have the following formula:

totext(Round(({@TotalFailed}/{@TotalSampleByComponent})*100,2),"0.00") + "%"

I would expect this to yield 3.63 based on the values used (11/303).
Instead I'm getting 4.00%

Not sure why - or if it has to do with the use of ToText along with the Round.

Thanks!


Replies:
Posted By: DBlank
Date Posted: 13 Jun 2013 at 3:32am
totext(Round(( mailto:%7b@TotalFailed%7d/%7b@TotalSampleByComponent%7d%29*100,2%29,0.00,2 - {@TotalFailed}/{@TotalSampleByComponent})*100,2),"0.00",2 ) + "%"
or you can simplify as
totext(( mailto:%7B@TotalFailed%7D/%7B@TotalSampleByComponent%7D%29*100,2%29,0.00,2 - mailto:%7b@TotalFailed%7d%%29,0.00,2 - {@TotalFailed} %{@TotalSampleByComponent}),"0.00",2) +'%'

Posted By: bal1
Date Posted: 13 Jun 2013 at 3:50am
Love the simplified version.

Can you help me understand the use of '%' and how we eliminated the use of 'Round'

Thanks so much

Posted By: DBlank
Date Posted: 13 Jun 2013 at 3:58am

x%y is the same as ((x/y)*100) (e.g gets the percentage value of x to y)

Totext also will round. If you do not specify where to round it will round to a whole number even if you format it like you did "0.00". You would think that the format would indicate where to round but it does not. The ",2" at the end ot the totext indicates how many decimal points to use and when it applies that rule it uses standard rounding rules not truncating.

Posted By: DBlank
Date Posted: 13 Jun 2013 at 3:59am
so basically your first formula was doing what you wanted but the ToText was rounding up to 4 because you did not indiacate that in that function to use 2 decimal spaces.

Posted By: bal1
Date Posted: 13 Jun 2013 at 4:00am
Brilliant...thank you!

Posted By: hilfy
Date Posted: 14 Jun 2013 at 7:42am
Actually, you can simplify this even further by using the format of the field to include the '%' instead of converting to text.  So, here's your formula:
 
If not IsNull({@TotalSampleByComponent}) and {@TotalSampleByComponent}) > 0 then
  {@TotalFailed}%{@TotalSampleByComponent}
else 0
 
NOTE:  I added the "If" statement to prevent division by zero errors that will cause the report to fail.
 
Once you add this to the report, right click on it and select "Format Field...".  On the Number tab, click on the "Customize" button.  On the new Number tab, select the correct decimals and rounding.  Then go to the "Currency Symbol" tab.  Enable the currency symbol and set its position to be on the right.  Then set the actual symbol to "%".  This will give you the format you're looking for without having to convert the number to text.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: bal1
Date Posted: 14 Jun 2013 at 8:37am
Thanks Dell!


Print Page | Close Window