May 10, 2012 at 8:26 am
I get errors when I try to format percentage. I've tried just about everything not sure what I'm missing. I tried it two ways the first by going into the Expression and formatting. This is where it just says 'Error' Here is code:
=round(Fields!ContainerDiff2010vs2011Fct.Value/Fields!LastYearQtyActualContainers.Value * 100,2)& "%"
The other is when I am using this code and I right click on the textbox, go to Properties and format code and select Percentage and it says 'NaN':
=Fields!ContainerDiff2010vs2011Fct.Value/Fields!LastYearQtyActualContainers.Value
Which one should I use for formatting percentage and what is the correct code to get it to work?
May 10, 2012 at 11:45 am
Anyone with any ideas on this?
May 10, 2012 at 12:36 pm
What's the datatype in that field? What does it look like? .0895? 1234? Let me know.
May 10, 2012 at 12:39 pm
I definitely think the datatype could be preventing you from just setting the text box properties to percentage.
May 10, 2012 at 12:44 pm
Also, division by zero could give you an NAN. Try doing a conditional statement using something like this:
=IIF(Fields!YourData.Value > 0,Fields!YourData.Value/Fields!YourOtherData.Value),0)
You might need two statements if both of your fields can be 0.
May 10, 2012 at 12:44 pm
themangoagent (5/10/2012)
What's the datatype in that field? What does it look like? .0895? 1234? Let me know.
There really isn't a datatype. I'm dividing the results of two fields which are whole numbers like -2,313 divided by 33,000 and should get a percentage like 6.36% OR -18.02%.
May 10, 2012 at 3:07 pm
Hmmm, ok. What about leaving the text box to the default and just doing the math with your fields? Does that error or give you a result (other than NAN of course)?
May 10, 2012 at 6:29 pm
It is most likely a divide by zero error.
Add custom code to rdl (report properties --> code pane):
Public Function DivideBy(ByVal Numerator, ByVal Denominator)
If Denominator = 0 Then
DivideBy = 0
Else : DivideBy = Numerator / Denominator
End If
End Function
Add to expression as:
=Code.DivideBy(Fields![Numerator].Value, Fields![Denominator].Value)
Where you replace [Numerator] and [Denominator] with the field values you want to divide
The Format shouldn't be a problem, in the Format Property, use P or p
May 11, 2012 at 7:14 am
It is likely a divide by zero or a divide by something that is not actually a number.
I would suggest the same solution as burninator, except I would use this as my embeded code instead because it tests first for the whether or not the input is a number.
Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Microsoft.VisualBasic.IsNumeric(Exp1) And Microsoft.VisualBasic.IsNumeric(Exp2) Then
If Exp2 = 0 Then
DivideBy = 0
Else: DivideBy = Exp1/Exp2
End If
Else
DivideBy = "N/A"
End If
End Function
One other thing I would suggest is to drop your formatting until you get the math working properly.
May 11, 2012 at 7:57 am
Hi All,
I tried both Daniel and burninator's code and it seems to be better but not calculating correctly for the totals. This is probably something I'm doing wrong but for example on one line it divides -2400/2400 and it shows 100% which is correct. For another line it divides -2313/36614 and it says '#Error' and on another line it divides -672/5406 and it shows -112.33% which I know isn't correct.
This is the expression that I'm using for the field:
=Code.DivideBy(Fields!ContainerDiff2010vs2011Fct.Value, Fields!LastYearQtyActualContainers.Value)
and when I drag the field from Dataset to the textbox in the report and right click the textbox and go to Expression it says:
=Sum(Fields!ContainerDiffPercentage2010vs2011fct.Value)
Could that be the problem, with the Sum?
May 11, 2012 at 12:14 pm
Are you doing this calculation in the header?
May 11, 2012 at 12:22 pm
I'm actually doing it in the textbox. I right click on the text box where I want to show the value and put and go to Expression and put in this code: =Code.DivideBy(Fields!ContainerDiff2010vs2011Fct.Value, Fields!LastYearQtyActualContainers.Value). I also put in the code that you provided in the Report Properties as well. One other thing, when I add the =Code.DivideBy... there is a red squiggly line under 'DivideBy', and it says unrecognized identifier when hovering over it.
May 11, 2012 at 6:03 pm
except I would use this as my embeded code instead because it tests first for the whether or not the input is a number.
Cool! Thanks!
May 11, 2012 at 6:08 pm
mldardy (5/11/2012)
One other thing, when I add the =Code.DivideBy... there is a red squiggly line under 'DivideBy', and it says unrecognized identifier when hovering over it.
It'll do that - just ignore - it won't err when you run the report. It probably has something to do with not knowing the code exists until runtime or something technical-like.
May 14, 2012 at 1:08 pm
mldardy (5/11/2012)
Hi All,I tried both Daniel and burninator's code and it seems to be better but not calculating correctly for the totals. This is probably something I'm doing wrong but for example on one line it divides -2400/2400 and it shows 100% which is correct. For another line it divides -2313/36614 and it says '#Error' and on another line it divides -672/5406 and it shows -112.33% which I know isn't correct.
This is the expression that I'm using for the field:
=Code.DivideBy(Fields!ContainerDiff2010vs2011Fct.Value, Fields!LastYearQtyActualContainers.Value)
and when I drag the field from Dataset to the textbox in the report and right click the textbox and go to Expression it says:
=Sum(Fields!ContainerDiffPercentage2010vs2011fct.Value)
Could that be the problem, with the Sum?
Did you try =IIF(Fields!YourData.Value > 0,Fields!YourData.Value/Fields!YourOtherData.Value),0)
?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply