Need help formatting fields with percentage

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

  • Anyone with any ideas on this?

  • What's the datatype in that field? What does it look like? .0895? 1234? Let me know.

  • I definitely think the datatype could be preventing you from just setting the text box properties to percentage.

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

  • 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%.

  • 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)?

  • 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

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • 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.

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

  • Are you doing this calculation in the header?

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

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

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • 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.

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • 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